我有一个函数,使用ADO从工作表的内容中获取ADODB记录集,如下所示:
Function WorksheetRecordset(workbookPath As String, sheetName As String) As adodb.Recordset
Dim objconnection As New adodb.Connection
Dim objrecordset As New adodb.Recordset
On Error GoTo errHandler
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
objconnection.CommandTimeout = 99999999
objconnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & workbookPath & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
objrecordset.Open "Select * FROM [" & sheetName & "$]", _
objconnection, adOpenStatic, adLockOptimistic, adCmdText
If objrecordset.EOF Then
Set WorksheetRecordset = Nothing
Exit Function
End If
objrecordset.MoveLast
objrecordset.MoveFirst
Set WorksheetRecordset = objrecordset
Exit Function
errHandler:
Set WorksheetRecordset = Nothing
End Function
我在导入数字数据时遇到问题,其中数字格式为小数点后1位,但它们实际上有小数点后2位。只有当数据类型在列中混合时才会发生这种情况。例如,这些值:
0.03
0.05
0.08
0.13
当我在这个表中将它们设置为小数点后1位时:
+-------+-----------+
| value | something |
+-------+-----------+
| 0.0 | a |
| 0.1 | a |
| 0.1 | sda |
| 0.1 | sdf |
+-------+-----------+
则记录集获得正确的小数点后2位值。但是当我把它们放在这个表格中:
+---------+-----------+
| value | something |
+---------+-----------+
| asdfasd | asdfas |
| 0.0 | a |
| 0.1 | a |
| 0.1 | sda |
| 0.1 | sdf |
+---------+-----------+
则记录集只获得1位小数点后的值,例如,它选择"0.0"而不是"0.03"。我认为这是因为第一行中的字符串导致ADO将列中的所有值视为字符串。
是否有一种方法,我仍然可以拿起文本字符串,但也得到正确的小数位数的数值?
编辑:刚刚注意到一些奇怪的事情。当我在工作簿打开时运行此操作时,记录集将获得正确的小数点。如果我在工作簿关闭时运行它,它只会得到显示的小数。
尝试以下为您的objRecordset功能和查询(在MS query with Excel中测试):
With objrecordset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.ActiveConnection = objconnection
.Open "Select format(`" & sheetName & "$`.value,'0.00') as [value], something FROM [" & sheetName & "$]"
End With
所以,这里JET SQL格式函数强制ADO的SQL解析器输出一个格式化为0.00
的字符串
另外,我已经将CursorTLocation
属性设置为adUseClient
,因此您不需要使用MoveLast
和MoveFirst
让我们知道你的进展
菲利普不幸的是,我以前遇到过一次同样的问题,原因是ACE驱动程序只查看列中的第一个值来决定整个列的数据类型。因此,您可以尝试将数值放在顶部对数据进行排序。
在Excel中创建连接表的"黄金标准"方法是使用vLookup。我建议这样做,尽管这看起来有点"业余"。"
而且,似乎将IMEX设置为1基本上强制ACE返回文本表示,以便数字列中的字母数字值不会作为空返回。