Importing Excel Spreadsheets

[MartinF]

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 [sig][/sig]

 

[kathryn]

Martin,

Do you get any error messages? Can you post the code that you are using?

Kathryn [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[MartinF]

Hi Kathryn,

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 = &quot;s:\private\access\Database Front End source code\CD tools\CD contents\data\dataattached.xls&quot;

DoCmd.RunSQL &quot;DELETE DataAttached.* FROM DataAttached&quot;
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, &quot;DataAttached&quot;, OpenFile, True
DoCmd.SetWarnings True

Exit Function

RetrieveError:
MsgBox Error$
End Function


Martin F
[sig][/sig]

 

[kathryn]

What happens when you try to do it manually? Are all the records returned? [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[MartinF]

Hi Kathryn,

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 [sig][/sig]

 

[kathryn]

Is there a primary key in the table into which you are appending data? (grasping at straws here!) [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[MartinF]

Kathryn,

There was a primary key, which I removed and tried again, but still same results.

MartinF [sig][/sig]

 

[kathryn]

OK, what if you try to import it into a new table, not an existing table? Does the same thing happen?

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. [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[MartinF]

Kathryn,

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 [sig][/sig]