无法在EXCEL VBA中更新ADO记录集

Question

我正在使用Excel范围作为数据源创建记录集。代码如下：

    Sub Hello()

    Dim xlXML             As Object
    Dim adoRecordset      As Object
    Dim rng               As Range

    Set rng = Range("A1:C6")
    Set adoRecordset = CreateObject("ADODB.Recordset")
    Set xlXML = CreateObject("MSXML2.DOMDocument")
    xlXML.LoadXML rng.Value(xlRangeValueMSPersistXML)
    adoRecordset.CursorLocation = 3
    adoRecordset.Open xlXML, CursorType:=2, LockType:=3

    adoRecordset.Movefirst

    adoRecordset.Fields(1) = 1000 'this is the error line
    adoRecordset.Update

    Set adoRecordset = Nothing
    Set xlXML = Nothing

    End Sub

我无法更新数据集，出现错误“多步操作生成错误。检查每个值（-2147217887）”。我不知道哪里出错了。我正在使用Excel 2007。

- Sumair Sayani

---

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - CodeJockey

你有实现这个功能吗？ - John Bustos

Answer 1

我认为在向记录集中添加新行或编辑现有行之前，您必须执行以下操作：

adoRecordset.Edit
' OR
adoRecordset.AddNew

- Franksta

Answer 2

根本原因是从Range派生的RecordSet默认情况下是不可变的，正如Andrew在这里的帖子中所指出的。

假设源范围具有以下内容（以消除“数据类型”问题）

	A	B	C
1	$A$1	$B$1	$C$1
2	$A$2	$B$2	$C$2
3	$A$3	$B$3	$C$3
4	$A$4	$B$4	$C$4
5	$A$5	$B$5	$C$5
6	$A$6	$B$6	$C$6

如果要使 OP 的代码正常工作，则应该这样写：

Sub Hello()

    Dim xlXML             As Object
    Dim ElementNode       As Object
    Dim AttributeNodes    As Object
    Dim Child             As Object
    Dim adoRecordset      As Object
    Dim rng               As Range

    Set rng = Range("A1:C6")
    Set adoRecordset = CreateObject("ADODB.Recordset")
    Set xlXML = CreateObject("MSXML2.DOMDocument")
    
    xlXML.LoadXML rng.Value(xlRangeValueMSPersistXML)
    xlXML.SetProperty "SelectionNamespaces", "xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'"
    
    Set ElementNode = xlXML.SelectSingleNode("xml/x:PivotCache/s:Schema/s:ElementType")
    ElementNode.setAttribute "rs:updatable", "true"
    
    Set AttributeNodes = xlXML.SelectNodes("xml/x:PivotCache/s:Schema/s:AttributeType")
    For Each Child In AttributeNodes
        Child.setAttribute "rs:write", "true"
    Next Child
        
    adoRecordset.CursorLocation = 3
    adoRecordset.Open xlXML, CursorType:=2, LockType:=3

    adoRecordset.Movefirst

    adoRecordset.Fields(1) = 1000
    adoRecordset.Update

    Range("A1").CopyFromRecordset adoRecordset    'added to reflect the update on the worksheet
    Set adoRecordset = Nothing
    Set xlXML = Nothing

End Sub

- Spectral Instance