ADO connection to Excel

[AlexanderBB]

Yikes ! I've just found my ADO connection in Access is not getting all the data. Some fields are blank. After a bit of googling it looks like I might need a Schema.ini File.

Has anyone any knowledge of this? Or had a similar issue and fixed it. I have made a mockup demo of just the problem. It's the same in Access 97 and 2003.

I have added dummy text to Excels first 8 rows to 'lock' the datatype. The problen doesn't occur until line 58 when a column contains just a dash. Maybe a clue ?

Thanks, ABB

 

[AlexanderBB]

Perhaps I better keep quiet... sorry... confusing TypeGuessRows with MaxScansRows.

 

[Kreszch68]

MAXSCANROWS is completely ignored when using ADO to query Excel files.

http://www.xtremevbtalk.com/showpost.php?p=1156340&postcount=12

I'll bet you a million dollars it does affect the way the JET engine reads excel files. The article you're reffering to, says that passing the regkey in the extended properties string will not work. But setting the reg key in the registry will absolutly change the way the data is interpreted.

 

[Colin Legg]

I'll bet you a million dollars it does affect the way the JET engine reads excel files. The article you're reffering to, says that passing the regkey in the extended properties string will not work. But setting the reg key in the registry will absolutly change the way the data is interpreted.

Cool... if you have the time would you do some tests and let us know?

 

[Kreszch68]

****, now I misread my own post, and made the same mistake as ABB.
It should be TypeGuessRows.

So give me you're accountnumber and I'll transfer the money as soon as possible .

 

[Colin Legg]

lol... buy me a pint when you're next in London and we'll call it quits.

 

[RoryA]

Assuming that the import is the same each time, I would create and save an import specification manually for each of your CSV files, then specify the name of the saved specification as the second argument of the TransferText method.

 

[AlexanderBB]

Rorya, that's something I'll have to swot up on, thanks for the tip but also very intrigued with the transferSpreadsheet method Colin mentioned.

So many ways ...... the best may well be another eureka moment, like when I stumbled onto .CopyFromRecordset. It was great to bin a pile of poor code and have amost 1000% speed increase.