docmd.transferspreadsheet expects a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
When you import data from a spreadsheet, you can specify a range.
--
Bridge Players know all the Tricks


NoSpamTakeSquareRootOfNumber" <"nlee144( said:
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?
Click to expand...
Click to expand...
 
Back
Top