Starting an Excel Macro from Within Access 4

[bxgti4x4]

I have command button on an access form which runs the following code:

Private Sub ExportToExcel_Click()
    
 Dim wbXL As Excel.Application
 Set wbXL = CreateObject("Excel.Application")
 
 DoCmd.OutputTo acOutputQuery, "QryVP", acFormatXLS, "VP_Access.xls", True
 wbXL.Application.Visible = True
 wbXL.Application.Workbooks.Open ("N:\Access\BackgroundVP.xls")
 wbXL.Application.Run ("N:\Access\BackgroundVP.xls!Auto_Open")
 
 End Sub

Originally, the last line of the code was not included. However, each time I ran the code, the spreadsheet would open but the auto macro would not run. If I opened the spreadsheet from within Excel, the macro would run automatically. I then tried inserting the last line of the code but, now, it opens the spreadsheet as before but tells me that it cannot find the macro.

Can anyone see where I am going wrong

Best Regards
John

 

[DoubleD]

wbXL.Application.Run "Autpen"

You don't need the full path to call the macro.

I am what I am based on the decisions I have made.

DoubleD

 

[VBslammer]

Try:

Private Sub ExportToExcel_Click()
 Dim strFile As String   
 Dim wbXL As Excel.Application

 strFile = "N:\Access\BackgroundVP.xls"
 Set wbXL = CreateObject("Excel.Application")
 
 DoCmd.OutputTo acOutputQuery, "QryVP", acFormatXLS, strFile, True
 wbXL.Visible = True
 wbXL.Workbooks.Open strFile
 wbXL.Run "Auto_Open"
 
 End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[PHV]

You may try this:
wbXL.Application.Workbooks.Open ("N:\Access\BackgroundVP.xls").RunAutoMacros xlAutoOpen

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

I gotta give you props for that one PHV - that's a good one

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[bxgti4x4]

Thanks Everyone, I'm sure I have enough now to sort it out.

Best Regards
John

 

[MikeCDPQ]

Hi all,

I've tried everything that is suggested here above to no avail. I either get error message "Macro ... cannot be found" or "Object does not support this property or method"

Here is what I am trying to do:

xl.Workbooks.Open("G:\Department Shared Data\Purchasing\Expediters\ExpeditersDatabaseMasterFile\VendorsReportFinal.xls")
xl.Visible = True
xl.Run "CreateVendorOutstandingPOs"

CreateVendorOutstandingPOs was created within the "ThisWorkbook"

Any suggestions ?

Thanks

 

[PHV]

The macro must be in a standard code module.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886