How do you begin a macro in Excel from Access?

[wbishopjr]

Access has great possibilities at a lot of things, but I need to analyze some data in Excel (using a macro) and then import it back into Access.

What I would like to do is set up one button to export the form data, start a macro in Excel, and import it back into Access.

Is there a way to do this without a lot of VBA code (I'm not fluent in programmin'...yet)? I can get it to export and save an Excel file as well as import the data back in, the part I'm having trouble with is automatically running the Excel Macro in between.

Thanks a million for any advice!!!
Waymond

 

[Guest_imported]

One way I can think of is to export the MS Access data to a dedicated, named Excel file. Set up an Autpen macro [ie name the macro Autpen() ] in that file that calls the macro you have written. This way you don't have to worry about writing any more code. The only problem you will encounter of course is that the Autpen macro (and hence your macro) will run every time the Excel file is opened. However, if the file is dedicated to just processing the exported data that shouldn't be a problem.

Another potential problem is one of timing; ie you may find that your processing macro starts to run before the data export to Excel is complete. There's not a lot you can easily do about that. If you have the knowledge then two suggestions are: write a loop that essentially pauses your code for a few seconds or use a 'Do While' type loop.

Hope this helps.

 

[Guest_imported]

One way I can think of is to export the MS Access data to a dedicated, named Excel file. Set up an Autpen macro [ie name the macro Autpen() ] in that file that calls the macro you have written. This way you don't have to worry about writing any more code. The only problem you will encounter of course is that the Autpen macro (and hence your macro) will run every time the Excel file is opened. However, if the file is dedicated to just processing the exported data that shouldn't be a problem.

Another potential problem is one of timing; ie you may find that your processing macro starts to run before the data export to Excel is complete. There's not a lot you can easily do about that. If you have the knowledge then two suggestions are: write a loop that essentially pauses your code for a few seconds or use a 'Do While' type loop.

Hope this helps.

 

[wbishopjr]

Thanks! I'll try that to see if it works. If you were a member, I'd give you a star...go ahead and join, this place has helped me tremendously!

Waymond