General discussion


Bridging access and excel macros?

By ·
I have a project in which I need to go back and forth between access and excel over and over (200+ times each time I run the project). Within each application, I run a macro, then go to the other and run a macro, then back to the former... and so on. This is the only way it can be done. How do I build/write a larger macro that can bridge both applications where I can combine both and just press the run button once?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by Maevinn In reply to Bridging access and excel ...

Have you tried linking the Excel spreadsheets into Access as tables and run everything from Access?

Collapse -

by In reply to Bridging access and excel ...

I don't think that will work. What I am doing is using an access macro to manage and sort through a bunch of data. Then export it it excel. In excel, I am using matrices, calculus, and other higher level math that access can't do via excel macros. I then sort through access using another access macro to pull different, similar data and export it to excel. I then use the results of the data manupulation and and math from the previous excel macro to do more equations to test the similarity of the results. I want to press one button so I dont have to do this over and over, is this possible? Thanks.

Collapse -

by Maevinn In reply to Bridging access and excel ...

Ah--the data is in Access, the crunching is in Excel. Linking only works if it's the other way around--the data in Excel, crunching (as such) done in Access.

So, alternate option...Build the query against the database in Excel. Data | Get External Data | Create New Database Query. Connect to your Access database, and either use an existing query in Access, or rebuild it (I'd probably use the existing one, particularly if it does any formatting or concantonating). Save the Query, and next time, you can just open up Excel and run it again.

If this is something you do all the time, add some parameters to the Access query allowing you to auto filter (ie, Between [Enter begin date] and [Enter end date] criteria). The Excel spreadsheet can then be opened, and populated.

Collapse -

object automation

by BryanReyn In reply to Bridging access and excel ...

This may be too late to help, but.
Have you tried automation?

In Access, you create an instance of Excel, and control it thru automation. You can create new sheets, pump values, get values, set formatting, etc. The excel object model is available to you from within Access.

Hope this helps.

Collapse -

re:object automation

by broncotedk In reply to object automation

Quick question, do you know any place that's a good resource for transferring Excel Macros to Access. I have code in Access to runs Excel Macros that format, but methinks its better if I write everything in Access.

Collapse -

access/excel macros

by plynch In reply to Bridging access and excel ...

not a very useful reply but i have a very similar query - need to write some code that can run functions in access and excell simultaneously - and if possible link parameter values in access to cells in excell.

Collapse -

by heifler In reply to Bridging access and excel ...

There is a tool to control Excel macros and data export from MS Access. See

Related Discussions

Related Forums