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. Anonymous
    2012-01-27T13:23:24+00:00

    I love this code.  It works perfect.  I do, however need help with one thing.  If the amount of data is less that the previous time this was run, I end up with leftover data, i.e., I run this and there are 25 records listed on my spreadsheet.  The next time I run it, there are only 20 records.  The spreadsheet is still going to show 25 records, because it only overwrote the first 20 and ignored everything else.  Is there a way of clearing the sheet first, then writing the data?  I realize I could manually clear the sheet, but that defies automation and relies on the several users to remember to clear data.  It also risks them clearing my conditional formatting instead of just data!

    tia,

    Bryan

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-01-27T16:18:37+00:00

    Try this after this line of code:

    Set xlWSh = xlWBk.Worksheets(strSheetName)

    put this:

    xlWSh.Cells.Clear

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-01-27T16:41:54+00:00

    Thanks Bob!  Worked like a charm.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-01-25T10:51:14+00:00

    Where / what code would you put in order to clear the sheet first?

    Thank you!

    Was this answer helpful?

    0 comments
  5. Anonymous
    2014-11-26T15:57:04+00:00

    I get error 3061 - To few perameters. Expected 1 when I try to use the below criteria on the query.

    [Forms]![Purchase Orders]![PurchaseOrderNumber]

    Any help is greatly apreciated,

    Thanks !

    Was this answer helpful?

    0 comments