Running Excel macro from Access module...

[Cele Balser]

Hello,
This can't be that hard! Could someone give me the code to run a macro in my Personal.xls from my Access module? I have created a query in Access, exported it to a network drive, I can open it, but now need to format it.

This is what I have, it works fine to open the Personal.xls but not run the Format_TerrList ing macro.

(Previous code here......)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String
Dim sMacros As String, tMacros As String
'Dim CurrentProject As String

' Create a new Excel instance
Set oXL = CreateObject("E xcel.Applicatio n")

' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0

' Full path of excel file to open

On Error GoTo ErrHandle

sPath = "P:\TerritoryLi stings\"
sFullPath = sPath & mFilename
sMacros = "V:\Documen ts and Settings\CBalse r\Application Data\MicroSoft\ Excel\XLSTART\P ERSONAL.XLS"
DoCmd.RunMacro "Format_TerrLis ting" ' this doesn't work

' Open it
With oXL
.Visible = True
.workbooks.Open (sFullPath)
.workbooks.Open (sMacros)
End With


Thanks for setting me straight with the correct syntax and code!!
CBalser

[Stewart Ross]

Hi. You can use the Run method of the Excel Application object to run your macro. However, the workbook in which the macro is contained must be open for the macro to be available to the automation server, so you will have to open it first, as shown in the sample code below:

Dim objExcel as Excel.Application
Set objExcel = New Excel.Application
objExcel.Workbooks.Open(sMacros)
objExcel.Visible = True ' use for testing to make sure all is working - no need for application to be visible if it is to be closed automatically
objExcel.Run ("personal.xls!Format_TerrListing")
objExcel.Quit ' assuming you don't need to save this workbook
Set objExcel = Nothing

-Stewart

[Cele Balser]

Hi. You can use the Run method of the Excel Application object to run your macro. However, the workbook in which the macro is contained must be open for the macro to be available to the automation server, so you will have to open it first, as shown in the sample code below:

Dim objExcel as Excel.Application
Set objExcel = New Excel.Application
objExcel.Workbooks.Open(sMacros)
objExcel.Visible = True ' use for testing to make sure all is working - no need for application to be visible if it is to be closed automatically
objExcel.Run ("personal.xls!Format_TerrListing")
objExcel.Quit ' assuming you don't need to save this workbook
Set objExcel = Nothing

-Stewart

Thanks a bunch, Stewart!! I'm dealing with other problems, but at least THAT works now! Best regards! cb