Help for running excel macro from access

[Glen]

I am having trouble running an Excel formatting macro from Access. I
have a command button in access which will automate the spreadsheet
creation. This spreadsheet must be formatted a specific way and the
macro for this is already written and saved in a file (BOM1.xls). I
want to have access create a new spreadsheet and automate the format
macro. My code errors out when trying to get this macro. I have tried
everything I could think of and find online. Below is a sample of the
current version of my code.

xlMac = "c:\Documents and Settings\gwelsh\desktop\Bill of
Material\BOM1.xls"
xlApp.UserControl = True
xlWs.Activate
xlApp.Run xlMac & "!" & "Get_Format"

So far, I have access create the new file and save it as BOM(x). Then
I try to get the macro to run on the new file. The error I keep
getting states that access cannot find the macro but I know it is at
this path. Any help would be greatly appreciated.

 

[Ron2005]

It sounds to me like, just that. The macro cannot be found.

Within Excel you have to tell it where to get the macro. When you
create a macro, it asks where to store it - in the open spreadsheet or
a macro spreadsheet.

Try this manually, and you can see the problem that Access is having.
1) Open excell and
2) then try to execute a macro via the menus. My bet is that you will
not be able to find that or for that matter any macro.

My bet is that you have to do the following:
1) Export your data to BOMx spreadsheet.
2) Start excell as you have.
3) Open the spreadsheet that has the macro in it but don't close it
(BOM1).
4) Open the BOMx spreadsheet.
5) Run the macro.
6) Save and close BOMx.
7) Closs BOM1.
8) Quit Excell

Let us know how it goes, please.

Ron

 

[Glen]

I really do appreciate the effort but this didn;t seem to work for me.
It is very possible and quite probable that I am doing something
stupid. I have tried many different ways again. The excel sheet that
has the macro in it will open just fine. 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 with
the macro, the error I get says that it can't find the macro. I have
saved the macro in BOM1.xls and have checked to see that it is
available for only BOM1 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. I
fyou have 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"

 

[Ron2005]

1) If you start excel from your desktop and do not open any other
spreadsheets and you go to macros do you see any macros?

What you can or cannot see is exactly what acces sees when it is trying
to run the macro.

Here are two other thoughts.

1) If you open BOM1 which is where you say you have the macros, and go
to macros, the macros are there and named "macroone" "macro2" etc.

2) Now open BOM2 without closeing BOM1. When you go to macros now
andlook at the names they are named
BOM1!macroone, BOM1!macro2, etc.

Modify you code appropriately.

From the code example above, it would seem that since the macros are in

BOM1 and not BOM2 that the like should be:

xlApp1.Run "BOM1!macroname"

 

[Glen]

Thanks Ron. I looked at the response I had placed yesterday and found
it to be a bit confusing. I resubmitted this morning to see if I could
get any suggestions. Let me ask you this, If I have both files open
and I have the new sheet active, can I run the macro on that active
sheet by simply coding xlApp1.Run "BOM1!macroname" or do I have to
indicate the full path for that macro? I do appreciate your patience
with me. This is just one of those things that I have never had to do
before and I am trying to learn. Thanks.

 

[Glen]

Oh Ron I am an idiot! All this time I have been trying to get Access
to run this macro from the complete path or some subset of it. That
last line of your suggestion set off a bell in my head. I feel like a
mental midget. Anyway. It works great. Thanks for your help.

 

[Ron2005]

Have been busy with projects. Glad it worked.

Ron