Replace worksheet when Exporting to Excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a macro that automatically exports the results of an Access query to
an Excel Spreadsheet. It does this on a daily basis.
Up until last week, the tab on the spreadsheet would be overlaid with new
data.
For example, the query name is "Replenishment". Each day the tab called
"Replenishment" within the Excel spreadsheet would be replaced with the
current day's data. Now instead of replacing the contents, a NEW tab is
created, "Replenishment1".
What causes this? How can I revert back so the tab will be replaced?
(This is a problem because a pivot table is based on the tab called
"Replenishment".)
 
This could be due to recent installation of an Office service pack or
update that was issued months ago, after a law suit over a patent on a
method of linking Access to Excel. The effect of these is to make it
impossible to update data in an Access sheet, only to read it or export
to a new or empty sheet. See e.g.
http://support.microsoft.com/kb/904953/


Here http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html
there is detailed information on how you can use the Range argument in
your macro to control how the export works: this may be some help.

Otherwise, the best approach may be to delete the "Replenishment" sheet
before exporting, so the new sheet is created with the correct name.
 
I have this happen to me occasionally as well. The only solution I have
found is to create a NEW spreadsheet without the problem tab, then export
your table to it. (It's no good to copy the XLS file, however you CAN Move
all the other sheets to your new workbook.) From that point forward, it
should work as before.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Back
Top