running an Excel macro from MS Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have code to export certain data from Access to Excel. The code open a
Save As Dialog box where the user specify the location and name for the newly
reated Excel file. I also have an Excel Macro (ain my Personal macro
Workbook) that i than run against the spreadsheet.

Since I know the name of the Excel File and the Name of the Excel Macro, is
there a way to execurte the Excel macro from within the MS Access code?

The File name is something like 'C:\Documents and
Settings\595930\Desktop\VCP_IT_Reports\VCP_IT_mmddyy.xls

The macro in Excel is named: PERSONAL.XLS!Format_VCP_IT.Format_VCP_IT


Thanks
 
Arvin.

I have used the following variation on your code and can open my Excel file
however, I cannot get the macro to run. Please note that my macro is saved
as a global macro in Personal.xls

Below is my code

Public Sub OpenXL2(strSaveFileName)
Dim objXL As Object, x
On Error Resume Next
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open strSaveFileName
x = .Run("Personal.xls!Format_VCP_STI.Format_VCP-STI")
End With
Set objXL = Nothing
End Sub
 
Back
Top