我使用的是Microsoft.Jet.OLEDB.4.0提供了一些VBA code的基本连接，和code工作无处不在，除了在Windows上运行64 7 64位操作系统比特安装的Microsoft Office Excel 2010。

I'm using the "Microsoft.Jet.OLEDB.4.0" provider for a basic connection in some VBA code, and the code works everywhere except on windows 7 64-bit operating systems running a 64-bit installation of Microsoft Office Excel 2010.

XP 32或64，Vista的32或64，和7 32，从字面上所有其他组合与Excel 2003，2007，或2010安装具有运行此code没有问题，但上述系统上，它的结果在关于失踪提供商的错误，我不能创建连接字符串。

Literally every other combination of XP 32 or 64, Vista 32 or 64, and 7 32, with Excel 2003, 2007, or 2010 installations has no problem running this code, but on the above described system, it results in an error about a "Missing Provider" and I can't create the connection string.

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提供的（没有64位版本存在）和Excel应该有将其用于连接没有问题。有任何想法吗？

I've done a ton of research, but from what I can tell, the operating system is supposed to come with a full set of providers, including the 32-bit version of the Jet Provider (no 64 bit version exists), and Excel should have no problem using it for the connection. Any ideas?

推荐答案

我不知道这是否会有助于人们超出了我具体的例子。我用的是提供程序执行上一个CSV的查询。例如：

I don't know if this will be helpful to people outside my specific example. I was using the provider to perform a query on a CSV. For instance:

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

下面，源文件已被修改，以便所有被命名的C n通过在头列：

Here, the source file has been modified so that all of the columns are named Cn by their headers:

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

因此​​，在我的情况，我有一个文件，该文件是这样的：

So in my case, I have a file that looks like this:

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提供，上述查询字符串可以用于读取文件的内容导入细胞：

Normally, using the Jet OLEDB provider, the above query string can be used to read the contents of the file into a cell:

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

但在上述codeProviderError将被触发在64位机器，因为没有Jet提供可用。我的解决方法是如下。其实，我将文件加载到Excel中，并解析查询字符串自己。我中断查询串由逗号分隔符，以使查询串的各部分变为公式一个新小区。要创建公式，我只是prePEND的 = 符号和参考源列替换字符串道道通。这样一来，复杂的查询，如（C2 + C4 * 10000000）仍然得到评估。然后我复制下来公式根据源数据的长度，然后覆盖以硬codeD值的公式。最终的结果是相同的操作的复杂的喷射OLEDB查询，尽管稍微慢一些。 code是如下。

But in the above code "ProviderError" will get triggered on 64 bit machines because there is no Jet provider available. My workaround was as follows. I actually load the file into excel, and parse the query string myself. I break the query string by comma delimiter so that each section of the query string becomes the formula for a new cell. To create the formula, I simply prepend an = sign and replace the string "Cn" with a reference to the source column. This way, complex queries like (C2+C4*10000000) still get evaluated. I then copy down the formula according to the length of the source data, and then overwrite the formulas with hard coded values. The end result is identical to doing a complex Jet OLEDB query, albeit slightly slower. Code is below.

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列的查询，但可以很容易地进行调整，以采取任何查询，可使用拆分因为有获得尽可能多的栏目，并动态REDIM POS（）和COL（）阵列。

The above solution assumes a query with 3 columns, but could easily be adjusted to take a any query, use split to get as many columns as there are, and dynamically redim pos() and col() arrays.