Getting Access to run Excel macro in a new .xls file

[Glen]

It is very possible and quite probable that I am doing something
stupid. I have tried many different ways of doing this. The excel
sheet that has the macro in it will open just fine from Access. Then
the new spreadsheet is created and saved with no problems. I just
can't get the thing to run the macro. What's great is that after
access has opened the .xls that contains the macro, I get an error that
says it can't find the macro. I have saved the macro in BOM2.xls and
have checked to see that it is available for only BOM2 when I enter the
'Edit Macros' window. The block of code for running the macro is
included below. I have replaced the entire path name with the word
'path' to make it easier to read. If anyone has any other suggestions,
I welcome them with open arms. Thanks


Set xlApp1 = CreateObject("Excel.application")
xlApp1.Application.Visible = True
With xlApp1
.Workbooks.Add
.ActiveWorkbook.SaveAs "path\" & filename & ".xls"
End With
Set xlWb2 = xlApp1.Workbooks.Open("path\BOM2.xls")
Set xlWb1 = xlApp1.Workbooks.Open("path\" & filename & ".xls")
Set xlWs = xlWb2.Worksheets("Sheet1")
'xlMac = "path\BOM2"
xlApp1.UserControl = True
xlWs.Activate
xlApp1.Run "BOM2" & "!" & "Sheet1.Sheet_Format_1_1"

**NOTE**
'App1', 'Wb1', 'Wb2', 'Ws' are declared as objects. 'filename' is a
string value taken from the database for naming new spreadsheet. I have
tried "xlApp1.Run xlMac" and "xlApp1.Run "BOM2.xls"" also.

 

[Glen]

This has been corrected so don't waste your time with a reply. If I
have both excel files open, you can write the code exactly as it
appears in the macro window to call the macro. { i.e. xlApp1.Run
"BOM2.xls!Module2.Sheet_Format_1_1" } It was so easy I was trying to
make it harder. Thanks to Ron for helping me figure it out.