IMEX=0 的 Excel ADODB 查询不返回第一行的数据

Question

我在Excel VBA中使用ADODB来查询/更新另一个(封闭的(Excel电子表格中的命名范围。使用 ADODB 对电子表格进行 SELECT 查询很简单;但是，弄清楚 UPDATE 查询是熊。经过很多很多...经过大量的研究和调整，我终于让更新工作了。

但是，我发现 SELECT 查询不包括命名区域中的第一行数据，即记录集包含 19 行，而命名区域包含 20 行，记录集的第一行包含命名范围第二行的数据。命名区域没有标题行...自始至终都是可靠的数据。

我知道你在说什么，"哦，你需要将HDR属性设置为"否"。 不，我试过了。见下文。

我唯一的附加线索是，当 IMEX 设置为 1 或 2 时，SELECT 查询将返回所有行。当 IMEX 设置为 0 时，则缺少第一行。但是，当 IMEX 设置为 1 或 2 时，记录集是只读的，因此无法更新。据我所知，使更新工作的唯一方法是将IMEX设置为0。

为了清洁，下面的代码是更大函数的摘录。

strSourcePath 是目标工作簿的完整路径 strSourceRange 是要更新的范围的名称 avNewValues(( 是要写入的新值的二维数组

我使用的是 32 位 Office 2016 和 64 位 Windows 10。

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strconnect As String
Dim strSQL As String
Dim iRow As Integer, iCol As Integer
strconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strSourcePath & ";" & _
"Extended Properties=""Excel 12.0;HDR=No;IMEX=0"";"
strSQL = "SELECT * FROM " & strSourceRange & ";"
Set conn = CreateObject("adodb.connection")
conn.Open strconnect
Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, adOpenStatic, adLockOptimistic
'Update the values in the recordset
rs.MoveFirst
For iRow = 1 To UBound(avNewValues, 1)
For iCol = 1 To UBound(avNewValues, 2)
rs.Fields(iCol - 1).Value = avNewValues(iRow, iCol)
Next
rs.MoveNext
Next
rs.Update

Answer 1

请尝试以下 XLSX 文件的连接字符串(请注意下面字符串中的关键字Xml(：

strconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strSourcePath & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=0"";"

或者这个用于 XLSM 文件(请注意下面字符串中的关键字宏(：

strconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strSourcePath & ";" & _
"Extended Properties=""Excel 12.0 Macro;HDR=No;IMEX=0"";"

另外，请尝试设置IMEX=1。我遇到了类似的问题，这种参数组合解决了它。

Answer 2

这是我的发现。如果根本不指定 IMEX 属性，则它可以工作，即返回所有行并且记录集是可读/写的。这令人困惑，因为我能找到的所有文档都说连接对象假定默认 IMEX 值为零。如果为 true，则显式指定默认值会更改连接对象的行为。

在我发现这一点之前，我已经决定只适应不良行为并向数据范围添加一个标题行。但是现在我将完全跳过IMEX属性，除了默认值。这无疑会引入数据类型不匹配的一些风险，但对于当前项目来说风险很低。