尝试使用ADO打开Excel文件时出现错误 [英] Getting errors while trying to open a Excel file with ADO

问题描述

我正在尝试使用ADO和SQL查询从Excel文件(.xls)中获取一些值.但是，尝试打开连接时出现错误.这是我目前所拥有的:

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

我已经使用两个连接字符串(strCon，strCon2)进行了测试，但均未成功.我或者得到一个错误，没有文本，只有一个"x"符号.或系统错误& H80004005(-2147467259)"

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)"

更改了它，所以我改为打开记录集，但是它仍然给我同样的错误.

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

推荐答案

回答这个问题为时已晚，但对于将来的读者来说.

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

我有一个类似的问题.我试图将Excel与MySQL数据库连接，但是当记录集打开时，它会破裂并向我显示& H80004005错误.

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.

最后，问题是我的办公室是32位，但是我的ODBC是64位，这不喜欢它.

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

我将ODBC连接更改为32位，并且VBA功能正常工作.

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

这篇关于尝试使用ADO打开Excel文件时出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！