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-01-18T21:21:35+00:00

    I've tried putting them in so far, and I am getting an error as follows:

    Compile error:      Expected: Identifier

    Help!!!

    Are you trying to call it from a macro or VBA code?  Also, post what you have currently.  If in VBA post the code you are using to call it.  If you are calling it from a function post what you have and how you have the arguments if using the RunCode macro action.

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

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-01-18T21:17:02+00:00

    I've tried putting them in so far, and I am getting an error as follows:

    Compile error:      Expected: Identifier

    Help!!!

    Was this answer helpful?

    0 comments
  3. 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
  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:37:55+00:00

    Could you give me an example of full code?

    Thanks.

    Was this answer helpful?

    0 comments