Transferspreadsheet not importing some data

[hedgracer]

I have set up the following vba code:

DoCmd.TransferS preadsheet acImport, acSpreadsheetTy peExcel9,
"Sheet1", _
"Z:\Accounting\ DataArchive\Mon thly_Database_D ata\Copy of
NIGHTFILLS JUL 19.06.xls", True, "A1:F200"


The data to be imported is as follows:

Account B/S QTY Contract Px Ticket#
83183979 B 2 GCQ 6333 32
12916393 B 2 GCQ 6305 400
12917937 S 2 GCQ 6330 20
63148781 S 3 SIN 10585 526
37532141 B 3 CLQ 7390 534
83083127 S 2 GCQ 6325 5
47847810 B 2 NGU 5750 440
83083128 B 20 SIU 10550 4
84947002 S 1 GCQ 6283 313
58990000 B 1 SIU 10535 528
20754623 S 10 SIU 10535 744
68041247 S 10 GCQ 6310 520
83388252 S 1 GCQ 6372 609
20754623 S 6 GCQ 6310 743
20754623 S 4 GCQ 6308 743
20555308 S 2 GCQ 6305 745
63148781 B 4 SIU 10540 516
74761005 S 2 SFU 8021 30
46105806 S 2 ESU 124475 443
93293205 S 1 SPU 124520 19
14160000 B 3 ESU 124500 384
14160000 B 5 ESU 124450 385
14160000 B 5 ESU 114475 11
86020177 B 1 CDU 8816 27
11911905 B 1 YMU 10867 33
46015880 S 1 ZCZ 2632 25
46060485 S 1 ZSQ 5880 402
46060485 S 2 ZSQ 5876 402
477OBK50 S 1 YMU 10862 31

The problem is that the 477OBK50 is not imported (the rest of the row
comes through fine). Now comes the puzzling part. If I move the data in
row 30 to row 26 the row comes through just fine. If the row with the
477OBK50 data in is placed anywhere after row 26 the 477OBK50 is
missing. BTW, the table that it is imported into is all text set at 255
character size for each field. When the data is imported in and the
477OBK50 is missing there is an import error table that reads this:

Error Field Row
Type Conversion Failure Account 30

Can anyone shed any light on what the problem might be? Any and all
help is appreciated. Thanks.

Dave Christman

[Ron2006]

Re: Transferspreads heet not importing some data

When doing the transferSpreads heet, Access will scan down a number of
records to determine how to characterize the field. If it looks at
(say) 29 rows and they are all numeric then it will define that column
as numeric. If it finds an alpha in the column in that number of
records it will say that the field is a text field.

I have had to go to the extent of adding a dummy row (row 2) in a
spreadsheet in order to help the system classify the column properly.

I believe that there is a registry setting that can be set to tell it
how many rows to read for the classification. I have been hesitent of
playing around in there, so I added the automation step of opening
excell from Access and adding the row.

Ron