A quirk in Importing Excel Spreadsheets

[NICOLE2000]

When I run a macro that imports an Excel spreadsheet into an existing Access Table, it appends the data fine. The only problem is, the first couple of records are out of order. I tested it by putting a different date on each row in the spreadsheet. When I imported it into Access, the first couple of dates were out of order.

Any ideas would be very appreciated!!

[Mongo]

Could you provide a few more details?

[NICOLE2000]

Mongo,

Thanks for replying. I have a macro that I run from Access. It imports an Excel spreadsheet into an existing table in Access, therefore "appending" the data to the table. It appends the data fine, I can scroll down and see the new data. Each record has a date field. When the data appends to the Access table, I see that the dates are out of order. So each entry has it own date like 3/1/00, 3/2/00, 3/3/00, etc. But when I append the data to the Access table, the dates get switched around so it may say 3/2/00, 3/1/00, 3/3/00. Worse yet, this mix up only happens for the first couple of lines, then it straightens itself out. I know this is a tricky one, and again, any help would be greatly appreciated.

[Mongo]

Okie-dokie, that gives me a few more clues.
So, you're importing a specific range
of contiguous cells into a non-indexed
single field Access table...

Does the import part of your macro look something like this?
(ignore the brackets -- implying your actual data)

DoCmd.TransferSpreadsheet acImport, 8, _
"[AccessTableName]","[drive:\path\filename.xls]", _
[-1/True,0/False], "[cell_range, i.e., A1:A12]"

And last but not least, what version of Office are you
using? Have you upgraded, when?

[Edited by Mongo on 05-16-2000 at 02:50 PM]

[NICOLE2000]

Mongo,

I am using Access 2000. I did not actually code the Macro, I created in design mode. So, under the "Action" column, I selected "Transfer Spreadsheet". At the bottom of the Macro design mode screen is a list of "Action Arguments". I entered my info as follows:

Transfer Type: Import
Spreadsheet Type: Microsoft Excel 8-9
Table Name: Table 3
File Name: \\....(path to file)
Has field names: Yes
Range: I left this blank. The instructions say to "Leave this blank to import the entire spreadsheet".
It seems like I may need to re-sort the spreadsheet on that date field before appending it to the table. Even though the rows in the spreadsheet are in the proper order.
What do you think?

[Mongo]

Hmm, leading/trailing spaces in the import range of cells and/or extra characters, to include spaces, in other seemingly unrelated cells on the sheet can create other than expected results...

Does the datatype of [Table 3] import as datetime?

Just for yucks, build & run this from a module and let me know if the oddity still happens.


Sub ImportXL()
DoCmd.TransferSpreadsheet acImport, 8, "TblTest", "full path & filename of your file", _
True, "specific date cell range"
End Sub

(If you have more than one sheet in your file, be sure to use something like Sheet1!A1:A10 for the quoted specific date cell range value.)