sql - 使用ADODB连接从封闭的工作簿中检索数据。一些数据被跳过了吗？

我目前正在编写一些代码，这些代码可以通过ADODB连接访问单独的工作簿。由于速度原因，我选择了此方法，而不是其他方法。这是我的代码如下:

    Sub GetWorksheetData(strSourceFile As String, strSQL As String, TargetCell As      range)
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer, r As Long
    If TargetCell Is Nothing Then Exit Sub
    Set cn = New ADODB.Connection
    On Error Resume Next
    cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;" & _
    "DBQ=" & strSourceFile & ";"
    ' DriverId=790: Excel 97/2000
    ' DriverId=22: Excel 5/95
    ' DriverId=278: Excel 4
    ' DriverId=534: Excel 3
    On Error GoTo 0
    If cn Is Nothing Then
    MsgBox "Can't find the file!", vbExclamation, ThisWorkbook.Name
    Exit Sub
    End If

    ' open a recordset
    Set rs = New ADODB.Recordset
    On Error Resume Next
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    ' rs.Open "SELECT * FROM [SheetName$]", _
    cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    ' rs.Open "SELECT * FROM [SheetName$]", _
    cn, adOpenStatic, adLockOptimistic, adCmdText
    ' rs.Open "SELECT * FROM [SheetName$] WHERE [Field Name] LIKE 'A%'", _
    cn, adOpenStatic, adLockOptimistic, adCmdText
    ' rs.Open "SELECT * FROM [SheetName$] WHERE [Field Name] LIKE 'A%' ORDER BY [Field                 Name]", _
    cn, adOpenStatic, adLockOptimistic, adCmdText

    ' optional ways of retrieving a recordset
    ' Set rs = cn.Execute("[A1:Z1000]") ' first worksheet
    ' Set rs = cn.Execute("[DefinedRangeName]") ' any worksheet

    On Error GoTo 0
    If rs Is Nothing Then
    MsgBox "Can't open the file!", vbExclamation, ThisWorkbook.Name
    cn.Close
    Set cn = Nothing
    Exit Sub
    End If

    'RS2WS rs, TargetCell
    TargetCell.CopyFromRecordset rs ' optional approach for Excel 2000 or later

    If rs.State = adStateOpen Then
    rs.Close
    End If
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    End Sub

现在，该代码大部分可用，但是当一行包含混合数据类型时，查询将跳过一些值。例如:

原始数据:

    3844774 12505604
    3844794 12505604
    4266113 3281271
    4295817 1307HX

返回的数据:

    3844774 12505604
    3844794 12505604
    4266113 3281271
    4295817

注意如何跳过数据的最后一位。对于多个条目，这是正确的，但只有那些包含字母(使其成为文本)的条目才是正确的。原始表也将所有内容都设置为文本。有什么建议可以避免不跳过这些行吗？

提前致谢!

最佳答案

那是因为您缺少IMEX :)

请参阅此链接(部分，将数据视为文本)

http://connectionstrings.com/excel-2007

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

对于XLS文件，请参见此链接

http://connectionstrings.com/excel

高温超导

关于sql - 使用ADODB连接从封闭的工作簿中检索数据。一些数据被跳过了吗？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/10506223/