Excel + ADO + Installable ISAM error 1

[nealvenables]

This is driving me nuts!

I'm trying to open an ADO connection to an Excel file, so that I can load the contents of the Excel file into an ADO recordset.

The code looks like this...

adoCNN_XL.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPath & strFile & ";" & _
"Extended Properties=Excel 8.0;HDR=Yes;"

adoCNN_XL.Mode = adModeRead

adoCNN_XL.Open


adoRST_XL.Open "SELECT * FROM [SHEET1];", adoCNN_XL, adOpenStatic

.. and is failing at the line

 adoCNN_XL.Open

with everyone's favourite "Could not find installable ISAM" error.

I've followed Microsoft suggestions (checked the MSEXCL40.DLL is registered correctly, reinstalled Office, upgraded MDAC to the latest version) and trawled Tek-Tips and nothing is fixing the issue.

Can anyone shed any light before I take desparate measures!?

btw: system config is NTS 4.0 + SP6, Office 2000 SR1 + SP4, MDAC 7.1, JET 4.0 SP3

 

[dougcranston]

nealv,

Can't answer your issue about the error you are seeing, but maybe the following can be used to circumvent the problem altogether.

Have you evaluated or tried using a DSN connection as opposed to the full connect string as you were doing? Along those lines, here are a couple of pages that may help you, if getting connectivity now is the requirement as opposed to ferreting out the source of your problems.

ADO to Query Data from a Closed Excel Workbook

http://www.beyondtechnology.com/geeks023.shtml

ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks

http://support.microsoft.com/default.aspx?scid=KB;en-us;q278973

How to set up a System DSN

http://www.webwizguide.com/asp/tutorials/setting_up_dsn.asp

Sample on how to use it...All be it Access, but same works for excel

http://www.webwizguide.com/asp/tutorials/connecting_to_a_database_pt2.asp

Hope this helps.
DougCranston

 

[nealvenables]

Thanks Doug.

Excellent suggestion and excellent resources too.

 

[dougcranston]

nealv,

No problem. Hope it gets you past this hurdle.

DougCranston