importing data from Access

[Guest]

I have two mdb's set with macros to import text files, massage the data, and
then export the resulting tables to several worksheets inside two existing
Excel files--e.g., the data from Access table "Medicaid Data" gets exported
to worksheet "Medicaid_Data" on Radiology.xls using TransferSpreadsheet. I
have another worksheet in the Excel file ("Data") that is filled with
index/match formulas which point to "Medicaid_Data" so that "Data" serves as
the graph datasource and updating "Medicaid_Data" succeeds in updating my
graph without my having to rewrite the formulas each month and also keeps the
source data with the final report for reference. This worked great last
month and in testing....

But now, while one of the Access macros is still behaving as I expected, the
other one is now creating new worksheets (e.g., "Medicaid_Data1") rather than
replacing the data on "Medicaid_Data".

Why am I now getting worksheets added with new names (xxx1) rather than
updating the existing worksheet with new data? (And why does it work in one
Access/Excel combo but not the other??)

With TransferSpreadsheet I have specified:
Transfer Type: Export
Spreadsheet Type: MS Excel 97

In both cases, I am exporting the contents of Access tables created via Make
Table queries.

Thanks for any insight!

 

[tony h]

A look at the code would be useful to get an answer. Need the bit where
you open the spreadsheet and send the data.
Also do you have the spreadsheet used as a linbked table source in the
Access database?

regards

 

[Guest]

Eventually I may get around to writing some VBA to do the work, but what I
have now is a macro. I haven't done much in VBA and what I did do was a long
time ago, so sitting down and writing code (even for something relatively
straightforward) will take a block of time I can't come up with at the moment.

The macro imports data from a text file, runs queries, then runs
TransferSpreadsheet four times in succession, transferring the contents of
four tables in Access to the same Excel spreadsheet, creating four worksheets
with the names of the tables.

Transfer Type: Export
Spreadsheet Type: Microsoft Excel 97 (altho when I print the macro from
Documentor is says on the printed page "Lotus 1-2-3 (WK1)" for some reason)
Table Name: MK Rad Costs
File Name: Y:\...\...\...\Radiology\Radiology.xls
Has Field Names: Yes
Range: (left blank)

Same set up for the one that still works and the one that doesn't.

In both cases I am exporting to an existing Excel spreadsheet and the
spreadsheet will already contain worksheets with the same name (generated in
the previous quarter). My goal is to refresh/replace the data worksheets
each quarter with worksheets with the same name so that my formulas and graph
sources, etc. don't have to be revised each time the report is run.