Excel行转独立XML文件VBA报错：Runtime Error 91

错误原因分析 #

• 标签名称不匹配：模板XML里的节点是<AXCustNum/>，但代码中使用getElementsByTagName("AX.CustNum")查找节点，多余的点号导致无法找到对应元素，触发「Object Variable or With block variable not set」错误。
• 潜在触发点：如果工作表名称"SAL Checked File Names 1.9"与实际不符，Sheets对象会返回Nothing，同样触发该错误；若Folder列的路径为空或无效，保存操作也会失败。

修正后的代码 #

Option Explicit

Sub ExportRowsToXML()
    Dim sTemplateXML As String
    Dim doc As Object
    Dim ws As Worksheet
    Dim lLastRow As Long
    Dim lRow As Long
    Dim sname As String, sExtension As String
    Dim saxcustnum As String, scustomername As String
    Dim stitlewithextension As String, sTitle As String
    Dim sFolder As String
    
    ' 定义XML模板
    sTemplateXML = _
            "<data>" & vbNewLine & _
            "   <Name/>" & vbNewLine & _
            "   <Extension/>" & vbNewLine & _
            "   <AXCustNum/>" & vbNewLine & _
            "   <CustomerName/>" & vbNewLine & _
            "   <TitlewithExtension/>" & vbNewLine & _
            "   <Title/>" & vbNewLine & _
            "   <Folder/>" & vbNewLine & _
            "</data>" & vbNewLine

    ' 创建XML文档对象
    Set doc = CreateObject("MSXML2.DOMDocument")
    doc.async = False
    doc.validateOnParse = False
    doc.resolveExternals = False
    
    ' 检查工作表是否存在
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("SAL Checked File Names 1.9")
    On Error GoTo 0
    If ws Is Nothing Then
        MsgBox "指定工作表不存在，请检查名称！", vbCritical
        Exit Sub
    End If
    
    With ws
        lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row ' 更可靠的获取最后一行方式
        
        For lRow = 2 To lLastRow
            ' 读取单元格值
            sname = .Cells(lRow, 1).Value
            sExtension = .Cells(lRow, 2).Value
            saxcustnum = .Cells(lRow, 3).Value
            scustomername = .Cells(lRow, 4).Value
            stitlewithextension = .Cells(lRow, 5).Value
            sTitle = .Cells(lRow, 6).Value
            sFolder = .Cells(lRow, 7).Value
            
            ' 跳过空路径行
            If Trim(sFolder) = "" Then
                MsgBox "第" & lRow & "行路径为空，跳过导出！", vbExclamation
                GoTo NextRow
            End If
            
            ' 加载模板并填充数据
            doc.LoadXML sTemplateXML
            doc.getElementsByTagName("Name")(0).appendChild doc.createTextNode(sname)
            doc.getElementsByTagName("Extension")(0).appendChild doc.createTextNode(sExtension)
            doc.getElementsByTagName("AXCustNum")(0).appendChild doc.createTextNode(saxcustnum) ' 修正标签名
            doc.getElementsByTagName("CustomerName")(0).appendChild doc.createTextNode(scustomername)
            doc.getElementsByTagName("TitlewithExtension")(0).appendChild doc.createTextNode(stitlewithextension)
            doc.getElementsByTagName("Title")(0).appendChild doc.createTextNode(sTitle)
            doc.getElementsByTagName("Folder")(0).appendChild doc.createTextNode(sFolder)
            
            ' 保存XML文件，建议添加.xml扩展名
            If Right(sFolder, 4) <> ".xml" Then sFolder = sFolder & ".xml"
            doc.Save sFolder
            
NextRow:
        Next lRow
    End With
    
    MsgBox "XML导出完成！", vbInformation
End Sub

额外注意事项 #

• 替换工作表名称时，确保与实际工作表名称完全一致，或改用工作表索引（如Sheets(1)）避免名称拼写问题。
• Folder列的路径需为有效路径，建议包含文件名（如C:\Files\data.xml），避免仅指定文件夹导致保存失败。
• 添加Option Explicit强制变量声明，可提前发现变量拼写错误，减少调试难度。

内容的提问来源于stack exchange，提问作者Sallie Francis