G
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.
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.