Error Running Excel Macro From Access Using VBA
I am using Access 2010 to transfer data to Excel. I have an excel macro that does a number of formatting routines. I am trying to use VB (code at bottom of post) to have Access open the spreadsheet and run the macro InitialFormat.
The macro is in the located in the ThisWorkbook.
When the code runs, I get the error:
Run-time error '1004':
Cannot run the macro 'Open Orders.xlsm!ThisWorkbook.I
The macro may not be available in this workboor or all macros may be disabled.
The error occurs at the line:
xlApp.Run "Open Orders.xlsm!ThisWorkbook.I
If I open the workbook and run the macro, it works just fine.
Any Ideas? Thanks in advance.
Sub FormatCOP()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
'Open the workbook
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("W:\S
xlApp.Visible = True
'run the macro
xlApp.Run "Open Orders.xlsm!ThisWorkbook.I
xlApp.DisplayAlerts = False
xlApp.ActiveWorkbook.Save
xlApp.Quit
xlApp.DisplayAlerts = True
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
The macro is in the located in the ThisWorkbook.
When the code runs, I get the error:
Run-time error '1004':
Cannot run the macro 'Open Orders.xlsm!ThisWorkbook.I
The macro may not be available in this workboor or all macros may be disabled.
The error occurs at the line:
xlApp.Run "Open Orders.xlsm!ThisWorkbook.I
If I open the workbook and run the macro, it works just fine.
Any Ideas? Thanks in advance.
Sub FormatCOP()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
'Open the workbook
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("W:\S
xlApp.Visible = True
'run the macro
xlApp.Run "Open Orders.xlsm!ThisWorkbook.I
xlApp.DisplayAlerts = False
xlApp.ActiveWorkbook.Save
xlApp.Quit
xlApp.DisplayAlerts = True
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
oleggold
Excel has a security setting to run only active macros . You can add a form and run the macro from form or You can run Your macro from access .
ASKER
I am not sure what you mean. I am trying to run the excel macro from access.
I have used similar code to start an excel macro from access and did not receive this kind of error.
I have used similar code to start an excel macro from access and did not receive this kind of error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cactus_Data
Thank you.
I made a slight change to:
For Each varMacro In avarMacros()
If Not Len(varMacro) = 0 Then
Debug.Print "xl run", Time, varMacro
booSuccess = xlApp.Run(varMacro)
End If
Next varMacro
and replaced with:
booSuccess = xlApp.Run("ThisWorkbook.In
to run one specific macro.
WORKS GREAT!
Thank you.
I made a slight change to:
For Each varMacro In avarMacros()
If Not Len(varMacro) = 0 Then
Debug.Print "xl run", Time, varMacro
booSuccess = xlApp.Run(varMacro)
End If
Next varMacro
and replaced with:
booSuccess = xlApp.Run("ThisWorkbook.In
to run one specific macro.
WORKS GREAT!
ASKER
Thank you for the help.
You are welcome!
/gustav
/gustav