Claiming that table does not exist

[Magnatolia]

Hi all,

I know nothing about VBA for Access but I created a drag-and-drop macro for exporting which didn't do what I wanted so I converted it to VBA.

What I want to achieve is have a list of queries that all export into one existing spreadsheet with the query name as the sheet name. So each query will have it's own sheet. I believe TransferSpreadsheet is the appropriate command.

I have '

Call DoCmd.TransferSpreadsheet (TransferType:=acExport, TableName[I]:="QueryName[/I]", FileName[I]:="Spreadsheet location and name[/I]")
[I]Call DoCmd.TransferSpreadsheet (TransferType:=acExport, TableName[I]:="QueryName2[/I]", FileName[I]:="Spreadsheet location and name[/I]")
Call DoCmd.TransferSpreadsheet (TransferType:=acExport, TableName[I]:="QueryName3[/I]", FileName[I]:="Spreadsheet location and name[/I]")
[/I][I][/[/I]CODE]
 
The first two queries work correctly, however everything after these two claim that the object doesn't exist, but I have checked and triple-checked the spelling, and even pasted the exact query name but no luck.
 
Am I doing something wrong?
 
Thanks!

 

[JohnLee]

Hi,

This may be just semantics but your error states that the table does not exist not your query, so perhaps a query is missing the appropriate table, queries can exist and can have a table as it's origin, but if that table has no data in it, then the query can not run, hence table does not exist.

This is just a suggestion and may not the the cause of the problem, but one I think is worth checking out.

Regards

John

 

[JHB]

Call DoCmd.TransferSpreadsheet (TransferType:=acExport, TableName[I]:="QueryName[/I]", FileName[I]:="Spreadsheet location and name[/I]")
[I]Call DoCmd.TransferSpreadsheet (TransferType:=acExport, TableName[I]:="QueryName2[/I]", FileName[I]:="Spreadsheet location and name[/I]")
Call DoCmd.TransferSpreadsheet (TransferType:=acExport, TableName[I]:="QueryName3[/I]", FileName[I]:="Spreadsheet location and name[/I]")
[/I][I][/[/I]CODE]
 [/QUOTE]
If could be the filename, here is the note from Help-file:
 [B]FileName: A string expression that's the file name and path of the spreadsheet you want to import from, export to, or link to.[/B]
So something like the below:
[QUOTE]"F:\Demo\Employees.xls"[/QUOTE]

 

[DonkeyKong]

I agree with JohnLee, make sure your query works as a standalone. Seems likely there is an error with the SQL in Query3.

 

[JohnLee]

Hi I would suggest that you write some code that checks to see if there is any data in the source tables and if there isn't then don't execute the rest of the code, have it pop up a message to let you know that which ever table it is that has no data in it and that this is where you need to check for reasons as to why it has no data.

This is something that I recommend you always do.#

Regards

John