Access transfer to Excel

When trying to transfer data from a Database on one drive to an Exce
Spreadsheet on a different drive in our system, the transfer i
creating a new tab in the destination spreadsheet. We want th
information to overwrite the info on the old worksheet every time th
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

--
Deb Pinge
-----------------------------------------------------------------------
Deb Pingel's Profile: http://www.excelforum.com/member.php...fo&userid=2011
View this thread: http://www.excelforum.com/showthread.php?threadid=55660

Sounds like you're exporting from Access to Excel.

Try putting your data into a table in Access with either an Append, Update,
or Make-Table query, then in Excel use Data-> Get External Data->New Database
Query to link to the data table your database. When you need to refresh the
spreadsheet, click the Refesh Data button on the External Data toolbox (or
Refresh All if you've set up multiple queries in this fashion).

Obviously, you'll have to run the Access query first to get fresh data into
the table, and the Access database must be closed before you try to refresh
the spreadsheet, or you'll get some strange error messages.

Alternatively, you can set up your Excel spreadhseet as a linked table in
your Access database, so when Access updates or appends data, it will also be
refreshed in Excel. This is a bit trickier.

HTH

"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.
>
>
> --
> Deb Pingel
> ------------------------------------------------------------------------
> Deb Pingel's Profile: http://www.excelforum.com/member.php...o&userid=20119
> View this thread: http://www.excelforum.com/showthread...hreadid=556602
>
>

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)