docmd.transferspreadsheet expects a table

[Guest]

I have an Excel file that was exported from my database using the
DoCmd.SendObject method, which pulls from a query (the query cleans out
some junk).
i.e.
DoCmd.SendObject acSendQuery, "qrySupplierParts", acFormatXLS,
rsQuery![email address]...

I want to be able to import that data file as well. I am using the
DoCmd.TransferSpreadsheet acImport method, but when I try to import the
Excel file, it gives an error. I am certain this is because the Excel
file was generated by a query, because I tried exporting from the same
table using SendObject and using TransferSpreadsheet to import successfully.

Any idea how I can force Access to import the data which was created by
the query export into my table?

 

[Guest]

I see now that the problem is that the TransferSpreadsheet method is
looking for a worksheet with the same name as the destination table.
Manually chaning the worksheet name fixes it. Any way to make it bypass
that? I don't really want to have to go through opening the file in VBA
just to change the name of that thing.

Nathan

 

[Guest]

When you import data from a spreadsheet, you can specify a range.
--
Bridge Players know all the Tricks

I see now that the problem is that the TransferSpreadsheet method is
looking for a worksheet with the same name as the destination table.
Manually chaning the worksheet name fixes it. Any way to make it bypass
that? I don't really want to have to go through opening the file in VBA
just to change the name of that thing.

Nathan

I have an Excel file that was exported from my database using the
DoCmd.SendObject method, which pulls from a query (the query cleans out
some junk).
i.e.
DoCmd.SendObject acSendQuery, "qrySupplierParts", acFormatXLS,
rsQuery![email address]...

I want to be able to import that data file as well. I am using the
DoCmd.TransferSpreadsheet acImport method, but when I try to import the
Excel file, it gives an error. I am certain this is because the Excel
file was generated by a query, because I tried exporting from the same
table using SendObject and using TransferSpreadsheet to import
successfully.

Any idea how I can force Access to import the data which was created by
the query export into my table?