I currently have a macro in Access that generates and emails an Excel file from one of my stored queries. I have it set to edit the email, then I open the file and perform a formatting macro I wrote in excel to clean up the output into a more readable format. I then save and send, then the Access macro continues.

My question is, can I just have the Access macro run the code that the Excel macro has on the fly without opening the file for edit? I'm trying to limit user inervention at every turn :)

I'm guessing the answer lies somewhere in converting my Access macro to VBA, dropping my Excel macro code into it, and adding some code to open the file and whatnot, run the formatting, then close the file and continue. Thoughts?

bump bump bump

Yes it would be something like:


path = "C:\xl_file_name.xls"

excel.application.workbooks.open(path)

excel.application.run("macro name as string")

Yes it would be something like:


path = "C:\xl_file_name.xls"

excel.application.workbooks.open(path)

excel.application.run("macro name as string")


excellent! i'll give that a shot. anyone know of a way without actually opening the file on screen?

It shouldn't actually open the file on screen unless you put in a line saying


application.visible = true

It shouldn't actually open the file on screen unless you put in a line saying


application.visible = true

excellent, i should be good to go. one more question, as opposed to running the macro with a call, could i just paste the vba code from the macro in at that point in the code? would make edits to the code easier to keep it all in one place. thoughts?

Doesn't really make much difference. Your going to have to open the workbook either way. So you can translate your code so it can run from access or just call the code in the excel workbook. Personally I would leave it in excel as it avoids having to rewrite parts of the code.

Doesn't really make much difference. Your going to have to open the workbook either way. So you can translate your code so it can run from access or just call the code in the excel workbook. Personally I would leave it in excel as it avoids having to rewrite parts of the code.

Cool, thanks for the advice!