CODEWITHSUNDEEP
excelconnection-stringadovba
Grimbox
Grimbox

Reputation: 223

Getting errors while trying to open a Excel file with ADO

I'm trying to get some values from an Excel file (.xls) using ADO with SQL queries. However I'm getting error while trying to open the connection. This is what I have currently:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strFile = IIf(strPath <> "", strPath, Range("G13").Value)
If strFile = "" Then Exit Sub

strCon = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";'Extended Properties='Excel 8.0;HDR=No;IMEX=1';"

strCon2 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";'Extended Properties='Excel 12.0 Xml;HDR=YES';"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon2

I have tested using both my connection strings (strCon, strCon2) with no success. I either get an error with no text only an "x" symbol. Or a "System Error &H80004005 (-2147467259)"

Edit:

Changed it so I open recordset instead, however it still gives me the same error.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strFile = IIf(strPath <> "", strPath, Range("G13").Value)
If strFile = "" Then Exit Sub

strCon = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & _
          strFile & "';'Extended Properties='Excel 8.0;HDR=No;IMEX=1';"

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & strFile & ";" & _
        "Extended Properties=Excel 12.0;"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

'cn.Open strCon

strSQL = "SELECT * FROM [Sheet11$C5]"

rs.Open strSQL, strConn

Debug.Print rs.GetString

Upvotes: 0

Views: 1203

Answers (1)

Juan Tapiador
Juan Tapiador

Reputation: 21

It's too late to answer this question, but for future readers.

I had a similar problem. I tried to connect Excel with MySQL database, but when the recordset open, it cracks and shows me the &H80004005 error.

Finally, the problem was my office is 32 bits but my ODBC was 64 bits and this did not like it.

I changed the ODBC connection to 32 bits and the VBA function worked.

Upvotes: 1

Related Questions