Software

Ask something
Discussion Locked

Bridging access and excel macros?

0 helpful
7 replies

by davidson.dj

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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?

7 replies

by maevinn

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

by davidson.dj

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.

by maevinn

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.

by bryanreyn

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.
b

by broncotedk

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.

by plynch

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.

This discussion is closed to new replies.