not importing properly from Excel

[Guest]

I have code that is supposed to import a spreadsheet from Excel. When I look
at the Excel Spreadsheet there are 3775 lines. When I import it it only
imports 2847 lines. Here is the code that does the import

DoCmd.OpenQuery "qry:WalStoreDel", acViewNormal, acEdit
stDocName = "G:\Accounting\AR\AR Database\walmart stores.xls"
DoCmd.TransferSpreadsheet acImport, , "WalmartStores", stDocName,
True, "Wal-Mart Stores!"

Help!!!!!!!

TIA

 

[Guest]

Check to see if there is a blank line after line 2847 in the spreadsheet.

 

[Guest]

No blank lines, but I should have mentioned that it seems to be randomly
missing lines. I do know that the 1st cell in each line does contain data.
I have thouroughly checked the Excel; sheet and the data seems consistant.

 

[Guest]

Oh, well, it was just a guess anyway. That is the only thing I have seen
cause that problem.

 

[Guest]

What's interesting is I am importing 4 other sheets from the same workbook
and all of them import completely.

 

[Guest]

Excel files can become corrupt. How about creating a new book, but don't
copy the sheet, select all the data in the sheet, do a copy, then do a paste
special values into the new book and try importing that.

 

[Guest]

Still the same issue. I am trying some other ideas. I started this in
another thread and have gone back to that one and asked the person who helped
me get the way to import sheet names THe thread name is Import Excel sheet
in which you actually tried helping. I put a message out to Robin, who
actually helped me. So I will see what comes of that.

THanks for all of your help. You have hyelped me many times in the past.

 

[MH]

In addition to the advice given by Klatuu, ensure that your data-types are
consistant in the columns, i.e. if your values in the rows 2-2780 are all
numeric and then you have a date this can cause problems during import.
Access scans the first <don't know the answer and don't really care> number
of rows and makes the decision of what data-type to use based on that, then
when it encounters a different data-type it throws a wobbly.

MH

 

[Guest]

Good luck, sorry I don't have an easy answer. I would be interested in
knowing what you find out, if you would be kind enough to post back to this
thread when you get it resolved.

 

[Guest]

Hi Klatuu,

When I checked the format on the Excel Spreadsheet, it was set to general.
Once I set it to Number, it imported all of the records. I tried setting my
field in Access to Text and leaving the field as general in Excel, and I was
still only able to import 2847 records.

Go figure. I just have to remember to set the row to numeric before saving
the downloaded spreadsheet.

Thanks again for your help.

 

[Guest]

That's okay, you can test for it with the IsWobbly() function

 

[Guest]

Never used that one before. I take it the syntax would be iswobbly(fieldname)?

 

[MH]

Good one! <g>

MH

That's okay, you can test for it with the IsWobbly() function

 

[MH]

It's not supported by most versions. <g>

MH

Never used that one before. I take it the syntax would be
iswobbly(fieldname)?
--
James Gaylord
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf