CODEWITHSUNDEEP
excelxmlvba
YumYum
YumYum

Reputation: 51

Run-time error while converting rows of Excel sheet to separate XML files

I want to export an xml file per row (see the example in the print screen).

I obtain following error

Run-time error '2147024891 (80070005)': System error: -2147024891.

on

doc.Save sFile

I use the following code for reading the Excel sheet (Microsoft Excel for Mac Version 16.49) and creating a xml file:

Sub CustomerOutToXML()

 sTemplateXML = _
        "<?xml version='1.0'?>" + vbNewLine + _
        "<ENVELOPE>" + vbNewLine + _
            "<TRANSACTION>" + vbNewLine + _
                "<TYPE>" + vbNewLine + "</TYPE>" + vbNewLine + _
            "</TRANSACTION>" + vbNewLine + _
            "<CONTENT>" + vbNewLine + vbNewLine + _
                "<DATE>" + vbNewLine + "</DATE>" + vbNewLine + _
                "<SSCC>" + vbNewLine + "</SSCC>" + vbNewLine + _
                "<ORDER>" + vbNewLine + "</ORDER>" + vbNewLine + _
            "</CONTENT>" + vbNewLine + _
        "</ENVELOPE>"

 Set doc = CreateObject("MSXML2.DOMDocument")
 doc.async = False
 doc.validateOnParse = False
 doc.resolveExternals = False

 With ActiveWorkbook.Worksheets(1)
  lLastRow = .UsedRange.Rows.Count

  For lRow = 2 To 7
   sFile = "/Users/xxx/Documents/" & .Cells(lRow, 1).Value & ".xml"
   Dim sDATE As String
   Dim sSSCC As String  'Not Long
   Dim sORDER As String

   sDATE = CStr(.Cells(lRow, 2).Value)
   sSSCC = .Cells(lRow, 3).Text ' <<< Not .Value
   sORDER = CStr(.Cells(lRow, 4).Value)
   sTransactionType = ActiveSheet.Name

   doc.LoadXML sTemplateXML
   doc.getElementsByTagName("DATE")(0).appendChild doc.createTextNode(sDATE)
   doc.getElementsByTagName("TYPE")(0).appendChild doc.createTextNode(sTransactionType)
   doc.getElementsByTagName("SSCC")(0).appendChild doc.createTextNode(sSSCC)
   doc.getElementsByTagName("ORDER")(0).appendChild doc.createTextNode(sORDER)


   doc.Save sFile
  Next

 End With

End Sub

Example of the Excel sheet:
enter image description here

Upvotes: 2

Views: 203

Answers (1)

Parfait
Parfait

Reputation: 107567

While your issue is not reproducible, consider some tips that may help you diagnose or resolve your issue:

  1. Use Option Explicit at the very top of module (outside of Sub or Function) to be sure all objects are properly defined. Relatedly, place all Dim objects to top of subroutine or function to help readability.

  2. Incorporate proper error handling with On Error GoTo... to capture runtime exceptions and appropriately exit the sub routine after unsetting objects with or without errors.

  3. Avoid use of ActiveWorkbook and ActiveSheet which can affect workflow if you have many workbooks open. Instead use actually assigned object or ThisWorkbook object. See this canonical Excel post, How to avoid using Select in Excel VBA (where second answer discusses Active* methods).

  4. Try using the early binding version of external libraries like MSXML that can expose useful error properties and enable Intellisense in VBA editor. Currently, you use late binding with CreateObject. If needing late binding for portability among many users, then ignore.

    Dim doc As MSXML2.DOMDocument

Set doc = New MSXML2.DOMDocument

  5. When using With blocks, follow through in all its properties. In fact, you can avoid use of ActiveSheet:

    sTransactionType = .Name

Upvotes: 1

Related Questions