vba - 文本文件源上的 Excel QueryTable 因 Jet OLEDB 连接字符串而失败

Question

使用 VBA，我正在尝试创建一个 Excel 查询表以提供来自文本文件的数据子集。我想使用 Jet OLEDB 连接字符串作为查询表 Connection .为什么会失败？

这是程序。

Sub OledbTest1()  'FAILS.
'Create querytable with oledb connect string.
    Const strConn = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Users\RSJCB\Desktop\;" & _
        "Extended Properties=""text;HDR=Yes;FMT=Delimited"""
    Dim wsht As Worksheet
    Set wsht = ThisWorkbook.Worksheets.Add()
    With wsht
        'The next line errors with 1004: Application-defined of object-defined error
        .QueryTables.Add strConn, .Range("A1"), "SELECT TOP 10 * FROM [TestFile.csv]"
        .QueryTables(1).Refresh
    End With
    Set wsht = Nothing
End Sub

如果我使用它来创建 ADO 记录集，然后将该记录集用作查询表 Connection，则连接字符串可以工作。 .

Sub OledbTest2()  'SUCCEEDS.
'Create querytable with ado recordset opened using oledb connect string.
    Const strConn = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Users\RSJCB\Desktop\;" & _
        "Extended Properties=""text;HDR=Yes;FMT=Delimited"""
    Const strSql = "SELECT TOP 10 * FROM [TestFile.csv]"
    Dim wsht As Worksheet
    Dim rst As New ADODB.Recordset
    rst.Open strSql, strConn
    Set wsht = ThisWorkbook.Worksheets.Add()
    With wsht
        'The next line errors with 1004: Application-defined of object-defined error
        .QueryTables.Add rst, .Range("A1")
        .QueryTables(1).Refresh
    End With
    Set wsht = Nothing
    Set rst = Nothing
End Sub

如果我使用 Microsoft Text ODBC Driver 连接字符串作为查询表 Connection，它也可以工作。 .但是，这似乎工作得慢了一点，我在实际代码中读取了 700K 记录。此外，我从来没有能够与查询表建立 OLEDB 连接，并且想知道如何做到这一点。

Sub OdbcTestQtbl()  'SUCCEEDS.
'Create querytable with odbc connect string.
    Const strConn = _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=C:\Users\RSJCB\Desktop\;" & _
        "Extensions=asc,csv,tab,txt;"
    Dim wsht As Worksheet
    Set wsht = ThisWorkbook.Worksheets.Add()
    With wsht
        .QueryTables.Add "ODBC;" & strConn, .Range("A1"), "SELECT TOP 10 * FROM [TestFile.csv]"
        .QueryTables(1).Refresh
    End With
    Set wsht = Nothing
End Sub

非常感谢您对此的任何帮助。

Answer 1

您需要让 Excel 知道该驱动程序是 OLEDB 驱动程序。只需将(显然未记录的)提供者说明符附加到连接字符串的开头:

.QueryTables.Add "OLEDB;" & strConn, .Range("A1"), "SELECT TOP 10 * FROM [TestFile.csv]"