Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Export and Run Excel macro from Access Module

Status
Not open for further replies.

ossse

Programmer
Jun 26, 2007
49
US
I am sending a Query to excel, then I would like to run a macro in excel from the Access module. The macro is not already in excel. I can send it fine, I just don't know the commands to convert the macro. (Note: this is similar to Thread707-1337949). Any help or point in the right direction will be appreciated. Also if you have a better way to write the excel macro let me know.


Code:
Sub openExcel()

    Dim objXL As Object, objWB As Object
    DoCmd.TransferSpreadsheet acExport, 8, "Total", "Totaltemp.xls", True
    DoEvents
    Set objXL = CreateObject("Excel.Application")

    With objXL
        .Visible = True
        Set objWB = .Workbooks.Open("Totaltemp.xls") 'opens file

With .Range(.Range("B2"), .Range("B2").End(-4121))
.FormatConditions.Add Type:=2, Formula1:="=IF(A2=A1,B1+1,1)"

End With

        'Excel Macro to run
        'Formula into B2= "=IF(A2=A1,B1+1,1)" then autofill to end of file
        Range("B2").Select
        ActiveCell.FormulaR1C1 = "=IF(RC1=R[-1]C1,R[-1]C+1,1)"
        Range("B2").Select
        Selection.AutoFill Destination:=Range("B2:B822")
        Range("B2:B822").Select
        Range("B2").Select
        'End of excel macro

        objWB.Saved = True 'saves file
        objWB.Close False 'closes file
        .Quit 'Exits excel
        
    End With
    Set objWB = Nothing
    Set objXL = Nothing
End Sub
 
Sorry, Ignore the following lines


With .Range(.Range("B2"), .Range("B2").End(-4121))
.FormatConditions.Add Type:=2, Formula1:="=IF(A2=A1,B1+1,1)"

End With
 
Something like this ?
With objXL
.Visible = True
Set objWB = .Workbooks.Open("Totaltemp.xls") 'opens file
.Range("B2").FormulaR1C1 = "=IF(RC1=R[-1]C1,R[-1]C+1,1)"
.Range("B2").AutoFill Destination:=.Range("B2:B822")
objWB.Close True 'save and close file
.Quit 'Exits excel
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
It doesn't work... the excel opens and closes, but the formula did not get entered into the cells.

here is the code again (maybe I did something wrong). I just discovered that the following excel macro code will do what I want (if this could be used instead)

Range("B2", Range("B2").End(xlDown)).FormulaR1C1 = "=IF(RC1=R[-1]C1,R[-1]C+1,1)"

Code:
Sub openExcel()

Dim objXL As Object, objWB As Object
DoCmd.TransferSpreadsheet acExport, 8, "Total", "Totaltemp2.xls", True
DoEvents
Set objXL = CreateObject("Excel.Application")

With objXL
.Visible = True
Set objWB = .Workbooks.Open("Totaltemp2.xls") 'opens file

.Range("B2").FormulaR1C1 = "=IF(RC1=R[-1]C1,R[-1]C+1,1)"
.Range("B2").AutoFill Destination:=.Range("B2:B822")

objWB.Close False 'closes file
.Quit 'Exits excel

End With
Set objWB = Nothing
Set objXL = Nothing
End Sub
 
Why did you write this (pease, reread my previous post)?
objWB.Close [!]False[/!] 'closes file

Anyway:
With objXL
.Visible = True
Set objWB = .Workbooks.Open("Totaltemp.xls") 'opens file
.Range("B2", .Range("B2").End(-4121)).FormulaR1C1 = "=IF(RC1=R[-1]C1,R[-1]C+1,1)"
objWB.Close True 'save and close file
.Quit 'Exits excel
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Hi,

you calling the range of the application object.

Try the following code:

Code:
Sub openExcel()

    Dim objXL As Object, objWB As Object
    DoCmd.TransferSpreadsheet acExport, 8, "Total", "Totaltemp2.xls", True
    DoEvents
    Set objXL = CreateObject("Excel.Application")

    With objXL
        .Visible = True
        Set objWB = .Workbooks.Open("Totaltemp2.xls") 'opens file
        With objWB.Sheets(1)
                .Range("B2").FormulaR1C1 = "=IF(RC1=R[-1]C1,R[-1]C+1,1)"
                .Range("B2").AutoFill Destination:=.Range("B2:B822")
        End With
        objWB.Close False 'closes file
        .Quit 'Exits excel
        
    End With
    Set objWB = Nothing
    Set objXL = Nothing
End Sub

Note that now it the range is of the workSHEET object.

Cheers,

Roel
 
Roel, the workbook wasn't saved, that's all !
 
Ah, we cross-posted. I just glimpsed over it and noticed the range reflected back to the app object. Didn't look further.

Good catch on your part [wink]

Cheers,

Roel
 
Thank you... both of you. I havn't programmed in 5 years, but have been working on this program all week now and I'm getting fried, and I'm having trouble thinking.

I know I've come across this earlier but can't seem to find it...How do I delete an excel file from a Access module?
 
Have a look at the Kill instruction:
Kill "\path\to\file.xls"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top