运行时错误 '13' VBA 宏 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13621067/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run-time error '13' VBA Macro Excel
提问by Maggie11
I created this macro and I get a Run-time error '13' at the line
我创建了这个宏，但在该行出现了运行时错误“13”
Set objDomAttribute = objDomElement3.Attributes.setNamedItem(objDomDoc.createAttribute("Name4"))
My Excel file has 2727 rows. I tested it for fewer rows and it works perfectly, but for 2727 it doesn't work. What should I do for solving that?
我的 Excel 文件有 2727 行。我测试了更少的行，它运行得很好，但对于 2727 它不起作用。我该怎么做才能解决这个问题？
Option Explicit
Public Sub MakeXMLWithDom()
Dim Q As String
Q = Chr$(34)
Dim objDomDoc As New MSXML2.DOMDocument60
objDomDoc.LoadXML ("<?xml version=" & Q & "1.0" & Q & "encoding=" & Q & "utf-8" & Q & "?>")
Dim objDomElement As MSXML2.IXMLDOMElement
Dim objDomAttribute As MSXML2.IXMLDOMAttribute
Dim nodeSet As MSXML2.IXMLDOMNodeList
Set objDomElement = objDomDoc.createElement("Containers")
Set objDomElement = objDomDoc.appendChild(objDomElement)
'finding the amount of rows in the excel
Dim numofrows As Long
numofrows = Worksheets("EXAMPLE").Range("E1").Offset(Worksheets("EXAMPLE").Rows.Count - 1, 0).End(xlUp).Row
Dim iRow As Integer
Dim Value11 As Variant
Dim Value12 As Variant
Dim Value13 As Variant
Dim objDomElement1 As Variant
Dim objDomElement2 As Variant
Dim objDomElement3 As Variant
For iRow = 10 To (numofrows + 1)
If Worksheets("EXAMPLE").Cells(iRow - 1, 5) = Worksheets("EXAMPLE").Cells(iRow, 5) Then
Value11 = Worksheets("EXAMPLE").Cells(iRow - 1, 11).Value & ", " & Worksheets("EXAMPLE").Cells(iRow, 11).Value
Value12 = Worksheets("EXAMPLE").Cells(iRow - 1, 12).Value & ", " & Worksheets("EXAMPLE").Cells(iRow, 12).Value
Value13 = Worksheets("EXAMPLE").Cells(iRow - 1, 13).Value & ", " & Worksheets("EXAMPLE").Cells(iRow, 13).Value
Else
Value11 = Worksheets("EXAMPLE").Cells(iRow, 11).Value
Value12 = Worksheets("EXAMPLE").Cells(iRow, 12).Value
Value13 = Worksheets("EXAMPLE").Cells(iRow, 13).Value
End If
If Worksheets("EXAMPLE").Cells(iRow, 5) <> Worksheets("EXAMPLE").Cells(iRow + 1, 5) And Worksheets("EXAMPLE").Cells(iRow, 3) <> "" And Worksheets("EXAMPLE").Cells(iRow, 5) <> "" Then
Set objDomElement1 = objDomElement.appendChild(objDomDoc.createElement("Data"))
Set objDomAttribute = objDomElement1.Attributes.setNamedItem(objDomDoc.createAttribute("Relevant"))
objDomElement1.Attributes.getNamedItem("Relevant").Text = "True"
Set objDomElement2 = objDomElement1.appendChild(objDomDoc.createElement("Info"))
Set objDomElement3 = objDomElement2.appendChild(objDomDoc.createElement("Part"))
Set objDomAttribute = objDomElement3.Attributes.setNamedItem(objDomDoc.createAttribute("Name1"))
Set objDomAttribute = objDomElement3.Attributes.setNamedItem(objDomDoc.createAttribute("Name2"))
Set objDomAttribute = objDomElement3.Attributes.setNamedItem(objDomDoc.createAttribute("Name3"))
Set objDomAttribute = objDomElement3.Attributes.setNamedItem(objDomDoc.createAttribute("Name4"))
objDomElement3.Attributes.getNamedItem("Name1").Text = Trim$(Worksheets("EXAMPLE").Cells(iRow, 5).Value)
objDomElement3.Attributes.getNamedItem("Name2").Text = Value11
objDomElement3.Attributes.getNamedItem("Name3").Text = Value12
objDomElement3.Attributes.getNamedItem("Name4").Text = Value13
End If
Next iRow
objDomDoc.Save ("c:\Example.xml")
End Sub
回答by Jean-Fran?ois Corbett
You say that Value13in the Locals window shows as Value: Error 2015and Type: Variant/Error. Well, okay, keep on investigating... Where does Value13come from? Look in your Excel sheet on that line. I bet you'll find a #VALUE!error there.
您说Value13在 Locals 窗口中显示为Value: Error 2015和Type: Variant/Error。好吧，好吧，继续调查……Value13从哪里来？查看该行的 Excel 工作表。我打赌你会在#VALUE!那里发现错误。
So it's a problem in your input data, not your code. Fix that and your problem will go away.
所以这是您输入数据的问题，而不是您的代码。解决这个问题，你的问题就会消失。
