docmd TransferSpreadsheet

[Ronald_yoh]

hey again there...

i'm having a problem with importing excel sheet into a table in access docmd.TransferSpreadsheet.

Suppose in column A in excel sheet you have a date field (e.g. 12/01/2008). When i'm importing the sheet using "docmd.TransferSpreadsheet" in VBA, it converts the date to string so i end up with "12108". but bizarely, when I'm importing the excel sheet normally (using import wizard), it's does not convert it.

What the heck is going on with this stupid access??

any thoughts anyone?

Cheers
R

[CreganTur]

Suppose in column A in excel sheet you have a date field (e.g. 12/01/2008). When i'm importing the sheet using "docmd.TransferSpreadsheet" in VBA, it converts the date to string so i end up with "12108".
Is your receiving field setup with the Date data type? Are the cells in your spreadsheet for your date set to Date format?

[Ronald_yoh]

yep... the excel contains date format.. if i change to text it will convert to "dmmyy"..

[stanl]

yep... the excel contains date format.. if i change to text it will convert to "dmmyy"..

You need to either use schema.ini or if doing things manually click on the advanced button and save your settings.

[Ronald_yoh]

i won't be doing it manually.. this is an automation system so i have to do it using VBA... and file format will never be the same..

btw, there is no "advanced" button while importing excel..

not sure about schema.ini... any better solutions?

[stanl]

i won't be doing it manually.. this is an automation system so i have to do it using VBA... and file format will never be the same..


If that is the case, don't even mess with docmd - do everything directly with Jet 4.0 and ADO.

I mentioned the Advanced button, thinking you might choose to import your Excel data as .csv and you get a lot more control over your field specs and can save them.

and is it the file format will never be the same, or the field format... or both?