Importing data using TransferSpreads heet

[Nathan Bloom]

Hi,

I have a procedure that transfer data from an Excel spreadsheet to an Access
2000 table. There is a start date and an end date in the range specified.
One of the date fields transfers correctly and the other one transfers as a
text field containing a number like 37917. In the spreadsheet both date
cells are formatted as a date. The data in both fields are entered as dates
(i.e. 10/27/03).

The TransferSpreads heet Command creates a temporary table because it does
not exist at runtime. the previous table is deleted before the new transfer
is performed.

DoCmd.TransferS preadsheet acImport, 8, "tempQualit y", stFileName, True,
"Quality!A3:J10 00"

If anyone can tell me where I might have gone astray or what is causing one
to transfer correctly and the other one not too, I would be greatly
appreciative of your help.
Nathan Bloom

[Marc]

Nathan,

When Access has to create a new table this way it has to estimate
datattype and format. The data probably are just not in the correct
format. You can get a litle more control over the way data are
imported if you import it in a existing table (made empty in stead of
deleted) with the right data-formats for the columns. Other
suggestions:
- don't use range-expressions but use a rangename defining the size of
the table.
- when the referred sheet is always on the same location make a link
using the file menu from the table view in the database window.
Eventually you could switch to other sheets using the tools/database
utilities menu to change the link to another file.

Marc.

"Nathan Bloom" <na*******@eart hlink.net> wrote in message news:<fn******* *********@newsr ead3.news.pas.e arthlink.net>.. .

Hi,

I have a procedure that transfer data from an Excel spreadsheet to an Access
2000 table. There is a start date and an end date in the range specified.
One of the date fields transfers correctly and the other one transfers as a
text field containing a number like 37917. In the spreadsheet both date
cells are formatted as a date. The data in both fields are entered as dates
(i.e. 10/27/03).

The TransferSpreads heet Command creates a temporary table because it does
not exist at runtime. the previous table is deleted before the new transfer
is performed.

DoCmd.TransferS preadsheet acImport, 8, "tempQualit y", stFileName, True,
"Quality!A3:J10 00"

If anyone can tell me where I might have gone astray or what is causing one
to transfer correctly and the other one not too, I would be greatly
appreciative of your help.
Nathan Bloom