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