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: Oldest
Most helpful Newest Oldest
  1. HansV 462.7K Reputation points MVP Volunteer Moderator
    2011-01-18T20:35:20+00:00

    Try specifying the name of the sheet followed by $ (e.g. Sheet1$) in the Range argument of TransferSpreadsheet.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-01-18T20:36:19+00:00

    You can try my code here:

    http://www.btabdevelopment.com/ts/tq2xlspecwspath

    But if the data size can be different (rows and/or columns) then you will want to clear the sheet first.

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

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-01-18T20:37:55+00:00

    Could you give me an example of full code?

    Thanks.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2011-01-18T20:47:53+00:00

    Thanks Bob.

    Sorry if this is a stupid question, but where the code states "strTQName" etc, do I litterally replace it with the name of my query, ie "Jack Powell Completions"?

    Thanks again.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2011-01-18T20:55:09+00:00

    No, you would copy the entire code (UNCHANGED) into a standard module (not form or report module) and name the module something like basExcelExport (just needs to be different from the procedure name).

    Then you would call it in your code or macro using

    SendTQ2XLWbSheet "Jack Powell Completions", "NameOfTheWorksheetHere", "PathAndNameOfTheWorkBookHere"

    so if the name of the worksheet was MySheet and the path and file was C:\Temp\JackPComp.xlsx" you would use

    SendTQ2XLWbSheet "Jack Powell Completions", "MySheet", "C:\TempJackPComp.xlsx"

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

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments