Error: DoCmd.TransferSpreadsheet

[SyafiqIzzat]

Greetings,

Can someone justify what's wrong with my code below.

DoCmd.TransferSpreadsheet acImport, 8, "Info", "C:\Test1.xls", True, "A1:E60"

It said Microsoft Access db engine could not find the object "C:\Test1.xls"
I already added reference to Microsoft Excel Object Library but still it didn't work. Anything I missed out?

TIA

 

[John Big Booty]

Is your file Test1.xls actually in the root directory of your C:\ drive or is it in a sub directory? Or in another directory say something like; C:\Documents and Settings\Your Name\Desktop for example?

 

[SyafiqIzzat]

I'm sure it is located in the root directory, as you said I might place it at subdirectory, I copy and paste the file there and change the path of the VBA code. Still, the result is still the same.

 

[John Big Booty]

The only other thing I can think of is that you have specified the incorrect spreadsheet type.

 

[SyafiqIzzat]

oh really? is it possible to change the type

 

[John Big Booty]

In the past I have successfully used;

DoCmd.TransferSpreadsheet acImport, , "TBL_Import", filename, False

Where filename is a String variable that holds the name and location of the Excel file to be imported.

 

[SyafiqIzzat]

So I have to replace the filename to my excel's path directory?

 

[John Big Booty]

Try;

DoCmd.TransferSpreadsheet acImport, , "Info", "C:\Test1.xls", False

 

[John Big Booty]

Have a look at the sample I posted here

 

[SyafiqIzzat]

It still doesn't work. Anyway, I already opened the link yesterday when I search for ways to import data from Excel. What it does actually? I added new data in the Import file then I moved to Access and click the button "Import New Arrival Report" but why the data new data didn't appear?

 

[John Big Booty]

I use that DB on a daily basis so there is some date criteria in the query QRY_VesselSrch on the field ArvDate that excludes arrival dates more than 14 days old, remove that criteria from the query and all the new data, and old, should appear.

If you look in the table TBL_Voyages and compare it's contents before and after you run the import procedure you will see the new data that has been imported.

 

[gemma-the-husky]

do you still have the excel app open. you cannot import from an open spreadsheet.

that might be the problem.

 

[gemma-the-husky]

oh really? is it possible to change the type

thats is probably the "8" argument. Why 8 - you select the xl type from the drop-down at that point, dont you?