Printable View
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!!
Could you provide a few more details?
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.
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]
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?
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.
(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.)Code:Sub ImportXL()
DoCmd.TransferSpreadsheet acImport, 8, "TblTest", "full path & filename of your file", _
True, "specific date cell range"
End Sub