ACCESS: Macro to export query results to specific tab in Excel spreadsheet, whilst overwriting existing contents.

If you post your code and where it went wrong we can try to help.

It was this thread:

http://social.answers.microsoft.com/Forums/en-US/addbuz/thread/023fca07-997f-4d0d-8ccc-96c238a7b4ba

Bob Larson, Former Access MVP (2008-2010) http://www.btabdevelopment.com (free Access tools, tutorials, and samples)

Hi Bob,

Using the code you gave me, I can export one query to one excel spreadsheet.

I need to do this on several different queries/spreadsheets, but all independantly.

I have tried just copying the code again, and then setting up a new call, but this does not work.

Any ideas on how I can do this?

Many thanks.

For each Query/Spreadsheet combination you need to execute a new call statement with the varying data. As Bob said, the function is a generic one that you pass parameters to. So each time you call the function (which you only need one of) you pass it a different set of parameters so it works with a different query.

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

You can't do this in a macro. You need to use VBA coding with Office Automation to open your Excel sheet and place the data into it. If you post your code and where it went wrong we can try to help.

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Hi,

I need to write a macro for access, that will export the contents of a query to a specific tab on an excel spreadsheet, whilst overwriting the exiting contents. I will need the code to work on various queries/spreadsheets independantly.

Bob Larson responded to me on a previous thread, and gave me advice, but when I try to replicate the code for another query/spreadsheet, the macro fails.

I wondered if anyone can help me?

Thank you.

Hi Bob,

Using the code you gave me, I can export one query to one excel spreadsheet.

I need to do this on several different queries/spreadsheets, but all independantly.

I have tried just copying the code again, and then setting up a new call, but this does not work.

Any ideas on how I can do this?

Many thanks.