Open Excel - Paste Query - Run Macro

[kip]

Hello,

We have a database that runs a query. The displayed table from that query is then copied and pasted into an excel file and an excel macro is run that creates charts making the information more useful.

My question is can we do this (Run Query/Open Excel/Paste query results/Run Excel Macro) in Access code.

Thanks

 

[theDBguy]

Maybe, but I would say just open Excel and have it do everything else, i.e. connect to Access, run the query, get the result, and produce the chart. Just a thought...

 

[kip]

To be clear. I'm not trying to get Access to do everything without Excel. I'm trying to get Access to open Excel and paste the table and then tell Excel to do its thing.

 

[theDBguy]

To be clear. I'm not trying to get Access to do everything without Excel. I'm trying to get Access to open Excel and paste the table and then tell Excel to do its thing.

Hi. No offense intended. I was just saying both of those approaches are possible. It's just a matter of preference or situation. You can make Access do all those things to Excel or have Excel do all those things to Access. It's your choice. Either way, you may need to use some Automation code. But, from your original description, it sounded like it would be less of a hassle and code if you approach it from the Excel side.

 

[kip]

All the code in Access is written including a Form where you can select a date range and have it run a query along with a few other unrelated things. The code in Excel is also already written where it creates a couple of pivot tables and a few charts. All I am looking for is a way to connect the dots and make it seamless probably starting in Access since that is where we’re starting from now.

So what I’m trying to eliminate is the part after the user enters the date range and selects the query to run both from the Access Form and they have to do the following:
1. Copy the result of the query just ran
2. Open the Excel file (it’s blank but does have a macro that will run a module)
3. Paste the data from the query into the Excel file
4. Run the macro in the excel file

Just those 4 things everything else is done.

P.S. No offense taken. You’ll have to try a lot harder than that to offend me.

 

[theDBguy]

All the code in Access is written including a Form where you can select a date range and have it run a query along with a few other unrelated things. The code in Excel is also already written where it creates a couple of pivot tables and a few charts. All I am looking for is a way to connect the dots and make it seamless probably starting in Access since that is where we’re starting from now.

So what I’m trying to eliminate is the part after the user enters the date range and selects the query to run both from the Access Form and they have to do the following:
1. Copy the result of the query just ran
2. Open the Excel file (it’s blank but does have a macro that will run a module)
3. Paste the data from the query into the Excel file
4. Run the macro in the excel file

Just those 4 things everything else is done.

P.S. No offense taken. You’ll have to try a lot harder than that to offend me.

Hi. Glad to hear we're okay. So, take a look at this website article for starters. It shows how to automate Excel from Access. Once you can do that, we can try to tackle the next steps. Cheers!

 

[kip]

Thanks, I will try that.