run an Excel macro from Access?

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

Guest

I'm trying to make life as easy as possible for my users. I have a button
for them to click and export the results of several queries to an Excel
file--that works.

Then they have to open the new spreadsheet and the Excel file that has the
macros and run a macro to format the new spreadsheet.

Is there an Access coding solution that will run the Excel macro on the
Excel spreadsheet (by clicking a button on a form in Access)?

I would appreciate a response telling me if this is possible or not (and
help on how to do it if it is).

Thank you,
Judy
 
I recently did something like that, here is what I did:

-While exporting the files to an specific folder, I add to the name of the
file an identifier to know what excel macro is going to be applied for
formatting,
-After exporting the files, in the same vb code, I open the excel file
containing the macros (using shell), this excel file has an equivalent of an
autoexec, please check excel for sure but I think the macro has to be named
autoopen,
-This automatic macro will open one by one the exported files and depending
on the identifier in the name file you can direct the formatting
-At the end of formatting the files, have the macro copy the files to
another folder and delete the files from this "export only" folder,
 
I am looking forward to trying this when I am back at work tomorrow. But I
am not familiar with using a shell in Access (I am learning as I go). Can
you give me an example of code that would open the excel file containing the
macros?

Thank you for responding,
Judy
 
Thank you for pointing me to this article. I could use more advice. I don't
want to deliver an Excel file with macros to my users. Right now the data is
in one Excel file and the macros in another. I need to figure out how to run
the macro on the file. Or, I can export the data to the file with the macro
and run the macro. I know how to save a copy of the file in the macro. Can
I save a copy of the file without the macro?

Thank you,
Judy
 
Sorry, that (running a Macro from one Excel file in another Excel file) is
ways too deep in Excel for my limited Excel knowledge.

You may want to re-post this question in one of the Excel newsgroups.

--
HTH
Van T. Dinh
MVP (Access)
 
Judy Ward said:
I'm trying to make life as easy as possible for my users. I have a button
for them to click and export the results of several queries to an Excel
file--that works.

Then they have to open the new spreadsheet and the Excel file that has the
macros and run a macro to format the new spreadsheet.

Is there an Access coding solution that will run the Excel macro on the
Excel spreadsheet (by clicking a button on a form in Access)?

I would appreciate a response telling me if this is possible or not (and
help on how to do it if it is).

Thank you,
Judy
Click to expand...
 
Judy,

I can write code in Access that will format the new spreadsheet when you
click the button to export to Excel. You won't need to run the Excel macro;
the Access code will do the same thing. After your data is exported, the new
spreadsheet will be automatically formatted - no user action will be needed.
Send me an email to (e-mail address removed).
 
Back
Top