470,819 Members | 1,593 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,819 developers. It's quick & easy.

Importing data using TransferSpreadsheet

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 TransferSpreadsheet Command creates a temporary table because it does
not exist at runtime. the previous table is deleted before the new transfer
is performed.

DoCmd.TransferSpreadsheet acImport, 8, "tempQuality", stFileName, True,
"Quality!A3:J1000"

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
Nov 12 '05 #1
1 7123
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*******@earthlink.net> wrote in message news:<fn****************@newsread3.news.pas.earthl ink.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 TransferSpreadsheet Command creates a temporary table because it does
not exist at runtime. the previous table is deleted before the new transfer
is performed.

DoCmd.TransferSpreadsheet acImport, 8, "tempQuality", stFileName, True,
"Quality!A3:J1000"

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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Bill Agee | last post: by
4 posts views Thread by Janelle.Dunlap | last post: by
3 posts views Thread by D.Stone | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.