Deb Pingel wrote:
When trying to transfer data from a Database on one drive to an Excel
Spreadsheet on a different drive in our system, the transfer is
creating a new tab in the destination spreadsheet. We want the
information to overwrite the info on the old worksheet every time the
macro is run. Since the worksheet (tab) does not have the correct name,
our formulas won't work. What is causing this and how do we correct it?
This works when set up within the same drive.

Sounds like you have something wrong in the Path of the ODBC connection
that's linking Access to Excel.

An alternative: create a QueryTable object (Excel - Data Menu -
Import External Data - Import Data ) and create an ODBC connection to
the Access database. In the QueryTable Object, set the RefreshStyle
property to xlOverwriteCells.

Another alternative: Open a RecordSet of your data in Access. From
Access, create an "Excel.Application" object, and open the workbook
that you are writing the data to. Then use the Range.CopyFromRecordset
method to copy the data into the Excel sheet (remember do do a
Range().ClearContents first to get rid of old data)