By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,743 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

Importing data using TransferSpreadsheet

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.