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

Anonymous
2011-01-18T20:33:05+00:00

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. Is this possible?

I have tried various ways, but it always wants to overwrite the excel file altogether!

Thank you.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

47 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2011-11-01T17:16:19+00:00

    I have tried to use this in my application.  The issue I have found is the code doesn't move the focus to the required sheet name when trying to copy the data from Access to Excel.  If I open the excel workbook and move the focus the the required sheet name, save, exit then execute the code in Access it works.   Looking at the code you posted it should select the sheet name provided in the variable, but it doesn't.  Can you help I need to figure out how to make this work as I have 9 different queries that need to be exported to the same workbook to nine different sheets.  If I can make this work it will save me a ton of time.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-03-22T17:12:09+00:00

    I need to create a button on a form to export the query results to excel. 

    Where do I go in access to create this module and have it linked to the button?

    I've been using a macro with "export with formatting", but I don't want it to overwrite the whole file.

    Thanks for this very helpful information.  I appreciate your patience with someone green behind the ears!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-01-19T22:58:03+00:00

    No, still no luck.

    May I can run one query for all year, export the query, then filter by months in excel.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2011-01-19T22:28:04+00:00

    I have a tab for each month and I have checked that they contain no spaces.

    Maybe I should try the Jan & Feb combined call and see if that works?

    Was this answer helpful?

    0 comments
  5. Anonymous
    2011-01-19T22:22:41+00:00

    Does the tab for Feb actually exist in that worksheet?  If not, it needs to be.  If so, you have to have the right name (no spaces in the worksheet name which is sometimes hard to tell without checking closely.

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

    Was this answer helpful?

    0 comments