TransferSpreadsheet not updating Excel file

[Astello]

I have a TransferSpreadsheet macro that's supposed to overwrite an
excel file with new data each time it is run (the access table is
updated each time in a query). However, the data isn't being rewritten.
I've checked the table and file names, and the query is correct. Any
ideas?

 

[Ken Snell \(MVP\)]

What are the arguments that you're using for the TransferSpreadsheet action?

What is the name of the query?

What is the name of the worksheet in the EXCEL file?

What happens when you run the macro -- no data are put into the EXCEL file,
even on a new worksheet? Or something else?

 

[Astello]

Query is called "LTL Prepaid Table Query"
It prompts the user for input, the makes table "LTLPrepaidData" - this
part works fine

Macro in Access is called "LTL Prepaid Data to Excel"
Arguments:
TransferSpreadsheet: Export, Microsoft Excel 8-10, LTLPrepaidData,
C:\CostModel\LTLPrepaid\LTLPrepaidData.xls, No
End Macro

When I run the Macro in Access, there are no error messages. Yet when
I open the Excel file, it is full of old data, not the new data
generated. Table "LTLPrepaidData" gets transferred nowhere.

There is a macro stored in Excel that is supposed to run on the new
data. Could this be the problem, that Access can't update the data
when a macro is attached? It worked before (I think), but now it
doesn't.

 

[Ken Snell \(MVP\)]

How/when do you run the query "LTL Prepaid Table Query"? Is that an action
step in your macro? If yes, it may be a timing problem with the table not
being in place before your TransferSpreadsheet action runs.

Is the "LTL Prepaid Table Query" query a make-table query? or an update
query? or an append query?

What is the name of the worksheet in the EXCEL file to which you want the
data to be written? Are there any named Ranges in that workbook file?

 

[Astello]

Query "LTL Prepaid Table Query" is a make-table query. I have a user
form with command buttons that execute first the make-table query, then
there is a button to export the table to an excel file. The excel file
I am trying to write to is called "LTLPrepaidData". No named ranges.
There is a macro in this excel file that does things to the data. Is
that the reason, that you cannot put new data into an excel file with a
stored macro?
(I have multiple excel files opening with macros in the project I'm
working on. I don't want the user to ever have to touch the data, so I
need them all to auto-open, which is why they are stored in their own
worksheets).

 

[Ken Snell \(MVP\)]

Ok, that's good that the make-table query is run separately from the export
process.

Is the EXCEL macro set to run when the EXCEL file is opened? If yes, it's
possible that TransferSpreadsheet is unable to write to the EXCEL file
because of the EXCEL macro.

Let's try an experiment. Change your TransferSpreadsheet macro to export to
a new EXCEL file (name it whatever you want). Let's identify if the export
process is working ok before we dig deeper into your EXCEL file.

 

[Astello]

Yes, if I change the name of the Excel file that Access is exporting to
(a brand new file) the data is exported without a hitch. So the
problem must be with the Excel macro running as auto_open?

 

[Ken Snell \(MVP\)]

That is a good likelihood. Try commenting out the macro in the EXCEL file
and see if the data export correctly -- if yes, that is the cause.

 

[Astello]

I figured out the problem. A few days ago I changed a bunch of file
names so that they were more self-explanatory, since the model I'm
working on will be handed off to someone else in the near future. When
I changed the file names, the data from Access was in fact being sent
to the right Excel file, just on a different worksheet. When I opened
the Excel file, the Auto_Open macro was working fine, just with the
data that was under the original file name. Once I deleted the
worksheet with the original file name and changed the file name that
the macro points to, it worked fine.
Thanks for pointing me to the excel macro! Just saved me a bundle of
work.