Jet OLEDB Provider 可在除 Windows 7 64 位上的 Excel 2010 之外的任何地方工作

Question

我在某些 VBA 代码中使用“Microsoft.Jet.OLEDB.4.0”提供程序进行基本连接，并且该代码在除运行 64 位安装的 Microsoft Office Excel 2010 的 Windows 7 64 位操作系统之外的任何地方都可以工作。

从字面上看，XP 32 或 64、Vista 32 或 64 以及 7 32 的所有其他组合，以及 Excel 2003、2007 或 2010 安装运行此代码没有问题，但在上述系统上，它会导致有关“缺少提供程序”的错误，并且我无法创建连接字符串。

With Conn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Mode = adModeRead
    .ConnectionString = "Data Source=" & path & ";Extended Properties='text;HDR=YES;FMT=Delimited'"
    .Open
End With

我已经进行了大量研究，但据我所知，操作系统应该附带一整套提供程序，包括 32 位版本的 Jet Provider（不存在 64 位版本）和 Excel使用它进行连接应该没有问题。有什么想法吗？

Answer 1

我不知道这对我的具体示例之外的人是否有帮助。我正在使用提供程序对 CSV 执行查询。例如：

SELECT C7, 0.0001, (C2+C4*10000000) FROM (filename)

这里，源文件已被修改，以便所有列都通过其标题命名为 Cn：

csvColumns = UBound(Split(lineIn, Delimiter)) + 1
For icol = 1 To csvColumns: columnLine = columnLine & "C" & icol & Delimiter: Next icol

因此，在我的例子中，我有一个如下所示的文件：

C1       C2       C3       C4       C5       C6       C7
1234     654332   23.214   5432     12345    123      60918234.234
2345     876332   43.223   6534     23456    234      34958732.432
3456     987332   54.243   7654     34567    345      92645378.564

通常，使用 Jet OLEDB 提供程序，上面的查询字符串可用于将文件内容读入单元格：

On Error GoTo PoviderError
With Conn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Mode = adModeRead
    .ConnectionString = "Data Source=" & path & ";Extended Properties='text;HDR=YES;FMT=Delimited'"
    .Open
End With
Rst.Open "SELECT " & selectText & " FROM [" & file & "];", Conn, adOpenKeyset, adLockOptimistic, adCmdText
If Not Rst.EOF Then Destination.CopyFromRecordset Rst

但在上面的代码中，“ProviderError”将在 64 位计算机上触发，因为没有可用的 Jet 提供程序。我的解决方法如下。我实际上将文件加载到 Excel 中，并自己解析查询字符串。我用逗号分隔符分隔查询字符串，以便查询字符串的每个部分都成为新单元格的公式。为了创建公式，我只需在前面添加一个 = 符号，并将字符串“Cn”替换为对源列的引用。这样，像 (C2+C4*10000000) 这样的复杂查询仍然可以得到评估。然后，我根据源数据的长度复制公式，然后用硬编码值覆盖公式。最终结果与执行复杂的 Jet OLEDB 查询相同，尽管速度稍慢。代码如下。

PoviderError:
    Resume FailOver 'Resets the error state so that further errors can be thrown
FailOver:
    FileReadFailover fixedFile, Destination, selectText, Delimiter
    ...

Private Sub FileReadFailover(ByVal fName$, ByRef Dest As Range, ByVal inputs$, ByVal delim$)
    Dim newBook As Workbook
    Dim pos(0 To 2) As Integer, col(0 To 2) As String
    Dim referenceText As String, i As Integer

    'Parse the query string 'inputs'
    pos(0) = 0: pos(1) = InStr(pos(0) + 1, inputs, ","): pos(2) = InStr(pos(1) + 1, inputs, ",")
    col(0) = Trim(Mid(inputs, pos(0) + 1, pos(1) - pos(0) - 1))
    col(1) = Trim(Mid(inputs, pos(1) + 1, pos(2) - pos(1) - 1))
    col(2) = Trim(Mid(inputs, pos(2) + 1))

    Application.StatusBar = Application.StatusBar & " Missing Jet Provider, waiting for Excel to open file..."
    Application.ScreenUpdating = True 'Allow excel to display the status bar showing the file loading
    Application.Workbooks.OpenText Filename:=fName, DataType:=xlDelimited, Other:=True, OtherChar:=delim
    Set newBook = Application.ActiveWorkbook
    Application.ScreenUpdating = False
    If newBook Is Nothing Then Err.Raise 1, , "User Cancelled Load"

    'Create a formula that will pull the values from the external file just opened.
    referenceText = Replace(newBook.Sheets(1).Cells(1, 1).Address(, , , True), "$A$1", "R[" & 2 - Dest.row & "]C")
    For i = 0 To 2
        If InStr(1, col(i), "C") Then col(i) = "=" & Replace(col(i), "C", referenceText)
        Dest.Offset(0, i).FormulaR1C1 = col(i)
    Next i
    'Copy the formulae down the based on the length of the input file
    Dest.Worksheet.Range(Dest, Dest.Offset(0, 2)).Copy _
        Dest.Worksheet.Range(Dest.Offset(1), Dest.Offset(newBook.Sheets(1).UsedRange.Rows.Count - 2, 2))
    'Make sure the worksheet recalculates to pull the values
    If Application.Calculation = xlCalculationManual Then Dest.Worksheet.Calculate
    'Now overwrite the formulas that pull the values with the values themselves
    Dest.Worksheet.Range(Dest, Dest.Offset(0, 2).End(xlDown)).Copy
    Dest.Worksheet.Range(Dest, Dest.Offset(0, 2).End(xlDown)).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.StatusBar = "File Import Successful"

    newBook.Close (False)
End Sub

上述解决方案假设查询有 3 列，但可以轻松调整以接受任何查询，使用 split 获取尽可能多的列，并动态重新调整 pos() 和 col() 数组。