使用ADODB连接从封闭的工作簿中检索数据.一些数据被跳过了吗? [英] Retrieving data from closed workbook using ADODB connection. Some data is skipped?
问题描述
我当前正在编写一些代码，这些代码可以通过ADODB连接访问单独的工作簿.由于速度原因，我选择了此方法，而不是其他方法.这是我的代码如下:
I am currently writing some code that can access a separate workbook through an ADODB connection. I have chosen this instead of other methods because of speed. Here is my code below:
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
现在，此代码大部分可用，但是当一行包含混合数据类型时，查询将跳过一些值.例如:
Now, this code mostly works however when a row contains mixed data types, the query will skip some values. For example:
原始数据:
3844774 12505604
3844794 12505604
4266113 3281271
4295817 1307HX
返回的数据:
3844774 12505604
3844794 12505604
4266113 3281271
4295817
注意如何跳过最后一位数据.对于多个条目，这是正确的，但只有那些包含字母(使其成为文本)的条目才是正确的.原始表也将所有内容都设置为文本.有什么建议可以避免不跳过这些行吗?
Notice how the last bit of data is skipped. This is true for multiple entries, but only those that contain letters (making it text). The original table has everything set to text as well. Any suggestions so that it doesn't skip these lines?
提前谢谢！
推荐答案
那是因为您缺少IMEX:)
That is because you are missing IMEX :)
查看此链接(将数据视为文本的部分)
http://connectionstrings.com/excel-2007
引用该链接.
Quote from that link.
将数据作为文本处理
当您要将文件中的所有数据都视为文本时，请使用此选项，以覆盖Excel列类型常规"以猜测列中的数据类型.
Use this one when you want to treat all data in the file as text, overriding Excels column type "General" to guess what type of data is in the column.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";
如果您想将列标题读入结果集中(即使有标题也使用HDR = NO)并且列数据是数字，请使用IMEX = 1以避免崩溃.
If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash.
始终使用IMEX = 1是检索混合数据列数据的一种更安全的方法.考虑以下情况:一个Excel文件可能工作正常，原因是该文件的数据导致驱动程序猜测一种数据类型，而另一个包含其他数据的文件导致驱动程序猜测另一种数据类型.这可能会导致您的应用崩溃.
To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash.
有关XLS文件，请参见此链接
For XLS files see this link
http://connectionstrings.com/excel
HTH
这篇关于使用ADODB连接从封闭的工作簿中检索数据.一些数据被跳过了吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
