Automate Access Macro to run Access Query and Export to Excel

[weasyb]

Dear MVP's:

I would really appreciate help with this...

I have an access database that contains a macro which runs a query. I would like to know how to automate this macro so that it runs this query and copies query results from access to excel, refreshes the pivot tables and saves a copy of the report to a folder. The source file is also copied over. I do not know VBA. Is there a simple way to do this?


Any thoughts or suggestions would be greatly appreciated.


Thank you,

Lisa

 

[Norie]

Can't you just import the query into Excel?

 

[weasyb]

There is a macro that runs several queries in my access database.

The macro is a macro and not a procedure in a module.

This macro (not a module) runs several queries.

Once query is run, the user is supposed to take the previous weeks excel report and do a "save as" and rename it to the current week and copy each query output to its corresponding data tab in excel.

Then refresh all pivot tables in this excel workbook and save the spreadsheet to a particular folder path.



Any suggestions to point me in the right direction would be greatly appreciated.



Thank you,



Lisa

 

[Micron]

Take a look at the TransferSpreadsheet function to see if you could work with it.
https://msdn.microsoft.com/en-us/library/office/ff844793.aspx
Opening the workbook afterwards would automatically refresh the pivot tables as long as the underlying data structure for the pt is the same. If you are willing to do a bit of work, there are lots of 'net sources with examples on how to copy files. Or you could simply open the workbook and continue to do your Save As. Sorry, I don't have any ready to use code for file copying (have only done this with batch files or created new files using VBA).

 

[alansidman]

crosspost: Macro runs an access query, how do I automate it so that it copies query results to excel

 

[weasyb]

Thank you, I will take a look. Thank you for your time.

 

[Joe4]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).

Thanks

 

[weasyb]

Thank you, this thread is closed.