Importing Excel Spreadsheets
Importing Excel Spreadsheets
(OP)
Hi,
I am currently experiencing trouble importing an Excel 97 spreadsheet into an already existing empty Access 97 table.
The spreadsheet has over 7600 rows and 118 columns and was originally exported from Access. I am using the 'TransferSpreadsheet' method as it will need to be automated. The problem is is that it only imports just over half of the records. Any ideas?
MartinF
I am currently experiencing trouble importing an Excel 97 spreadsheet into an already existing empty Access 97 table.
The spreadsheet has over 7600 rows and 118 columns and was originally exported from Access. I am using the 'TransferSpreadsheet' method as it will need to be automated. The problem is is that it only imports just over half of the records. Any ideas?
MartinF
RE: Importing Excel Spreadsheets
Do you get any error messages? Can you post the code that you are using?
Kathryn
Kathryn
RE: Importing Excel Spreadsheets
No I don't get any error messages at all. It takes a while to run as expected, but then returns just over half of the records
Here is the code I am using:
Function Import()
Dim OpenFile As String
On Error GoTo RetrieveError
DoCmd.SetWarnings False
OpenFile = "s:\private\access\Database Front End source code\CD tools\CD contents\data\dataattached.xls"
DoCmd.RunSQL "DELETE DataAttached.* FROM DataAttached"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "DataAttached", OpenFile, True
DoCmd.SetWarnings True
Exit Function
RetrieveError:
MsgBox Error$
End Function
Martin F
RE: Importing Excel Spreadsheets
Kathryn
RE: Importing Excel Spreadsheets
I've just tried it manualy and it comes up with a message box stating the Access was unable to append all of the data. It then goes on to say that 0 record(s) were deleted and 0 records(s) were lost due to key violation errors. When I check the data only half of it is there! So it has the same results, but just brings up that message box saying that not all the data has been appended, but nothing has been lost!!
A puzzled MartinF
RE: Importing Excel Spreadsheets
Kathryn
RE: Importing Excel Spreadsheets
There was a primary key, which I removed and tried again, but still same results.
MartinF
RE: Importing Excel Spreadsheets
Other thoughts:
I wouldn't worry about the future automation at this point; first we have to get the process working. Try naming the spreadsheet as 3 or four named ranges, each having about 2000 records and see if they will import. If each imports, then you know that there is no trouble with the data. This brings up a thought: does the data always import up to the same row??
If there is a key field(s), import the data as two tables, again using named ranges, both of which have the key field(s) in them. Then you will have two tables which are in 1-1 relationship.
Kathryn
RE: Importing Excel Spreadsheets
I've just tried spliting the data in half across two worksheets and tried importing each worksheet in to the table, and again this time only half of the records off each worksheet inmported. I then tried the same thing but imported the first worksheet into a new table, and then the second worksheet into the same table and all the records imported ok. Now I have tried putting all the records back together on one worksheet and then tried importing them all into a new table in one go, and again they have all now imported ok. I have compared the new table structure with the existing table and the only obvious difference is is that one field that was an auto-number is now just a number field, however i did not think this would affect it because i have imported data in to auto-number fields before without problems. The other thing that is puzzling me though is that the existing table that the records will not import into is exactly the same table that they were exported from.
When attempting to import the data into the existing table, it is always the same number of records that get imported.
MartinF