Export and Run Excel macro from Access Module

[ossse]

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.

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

 

[ossse]

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

 

[PHV]

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

 

[ossse]

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

 

[PHV]

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

 

[Rofeu]

Hi,

you calling the range of the application object.

Try the following 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

 

[ossse]

GENIUS!!!

 

[PHV]

Roel, the workbook wasn't saved, that's all !

 

[Rofeu]

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

Cheers,

Roel

 

[ossse]

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?

 

[PHV]

Have a look at the Kill instruction:
Kill "\path\to\file.xls"

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

 

[ossse]

It's simple and it works, thanks!