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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-01-18T21:56:02+00:00

    I'm sorry, I misundestood you. It now works, however is there anyway of stopping it coming up with this error at the end of the process?

    3021 : No current record

    Thank you.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-01-18T21:41:55+00:00

    Okay, first off.  I TOLD you NOT TO MODIFY THE FUNCTION you got from my website AT ALL. Do NOT CHANGE ANYTHING.  Just copy and paste it into a standard module.

    You then CALL the function like this from a DIFFERENT EVENT or macro:

    Call SendTQ2XLWbSheet("Jack Powell Completions", "Completions", "O:\Public\Public Users Folders\Jack Powell\Jack Powell Target Sheet 2011.xlsm")

    Again DO NOT MESS WITH THE FUNCTION AT ALL.  It is a generic function written to handle anything you throw at it, but you must CALL the function and PASS the parameters, you do not modify the function.

    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-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
  4. 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
  5. 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