transferspreadsheet

[Guest]

I have a macro that opens a query, then opens Excel, then uses
transferspreadsheet to export the data to Excel. I'm having trouble when
defining the "table name". When the macro runs all is well up to the export.
I keep receiving a message that tells me the table name is invalid. The doc
says I can use a query name, am I doing something wrong?

thanks

 

[George Nicholson]

Just a guess:
Your macro opens the query? That may be your problem. You can give
TransferSpreadsheet the name of a query for it's table name argument, but it
doesn't need to be open. TransferSpreadsheet will run the query and export
the results. If you already have the query open, that might be preventing
TransferSpreadsheet from accessing the same query (i.e, "Invalid name" =
"couldn't be opened" rather than "doesn't exist").

HTH,

 

[Don Newger via AccessMonster.com]

Howdy folks,
This is SOMEWHAT of a problem I am also having.
I need to export multiple queries to the same work book on different tabs.
Example...
Query A ---> Book1.xls/sheet1
Query B ---> Book1.xls/sheet2
Query C ---> Book1.xls/sheet3

I have been playing with this for 2 days... All I can get it to do is Write
to the Same tab and remove all the others. Any ideas?

 

[George Nicholson]

I'm not sure you can do what you are trying. (If you ever find out
differently, please let me know...)

One work around I've used is to let TransferSpreadhseet export data to the
single-sheet workbooks that it does by default and then use Automation to
have Excel "move" those sheets into a single workbook. However, this was in
VBA, I don't know enough about Access macros to say Automation isn't
possible when using them, but I don't think so.

However, if the exported filenames are always the same and if running a
macro from Excel is acceptable, maybe you could use macro recorder in Excel
to create the code that moves all your exported sheets into a single book.
(The code would be very similar to what you might use in Automation from
Access, but getting Excel's macro recorder to write it for you might get you
there quicker if you aren't a coder.)

HTH,

 

[Ken Snell [MVP]]

If you export queries with different names to the same workbook, ACCESS will
write the data onto new sheets in that workbook. The trick is that the
queries' names must be different from each other.