Excel conversion to XML with VBA

[Jorgi]

Dear All smart people. I'm looking for VBA code to convert data from multiple excel rows into one xml file. Ideally will be if the XML could be saved in the same folder where the excel file is located. Thank you very much for you help.

Excel data to be converted to XML

XML

 

[John_w]

Try this macro - fill in the rest of the data-set line.

Public Sub Create_XML_File()

    Dim XMLfileName As String
    Dim r As Long
    
    With ActiveWorkbook
    
        XMLfileName = .Path & "\Data.xml"
        
        Open XMLfileName For Output As #1
        Print #1, "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>"
        Print #1, "<data-set ....>"   'fill in the rest
        
        With .ActiveSheet
            For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
                Print #1, "  <record>"
                Print #1, "    <Type>" & .Cells(r, "A").Value & "</Type>"
                Print #1, "    <Type_Number>" & .Cells(r, "B").Value & "</Type_Number>"
                Print #1, "    <Software>" & .Cells(r, "C").Value & "</Software>"
                Print #1, "  </record>"
            Next
        End With
        
        Print #1, "</data-set>"
        Close #1
    
    End With
    
End Sub

 

[Jorgi]

Try this macro - fill in the rest of the data-set line.

Public Sub Create_XML_File()

    Dim XMLfileName As String
    Dim r As Long
   
    With ActiveWorkbook
   
        XMLfileName = .Path & "\Data.xml"
       
        Open XMLfileName For Output As #1
        Print #1, "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>"
        Print #1, "<data-set ....>"   'fill in the rest
       
        With .ActiveSheet
            For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
                Print #1, "  <record>"
                Print #1, "    <Type>" & .Cells(r, "A").Value & "</Type>"
                Print #1, "    <Type_Number>" & .Cells(r, "B").Value & "</Type_Number>"
                Print #1, "    <Software>" & .Cells(r, "C").Value & "</Software>"
                Print #1, "  </record>"
            Next
        End With
       
        Print #1, "</data-set>"
        Close #1
   
    End With
   
End Sub

That is brilliant/Perfect! Thank you so much John__w. You are my hero

 

[Jorgi]

John_w how the macro will look like if there will be empty rows mixed with rows with data? The XML will be created only for the rows with data without the empty rows where is no data? Thank you

 

[John_w]

Just put all the Print # lines for a record inside an If .Cells(r, "A").Value <> "" Then .... End If block.

 

[Jorgi]

Just put all the Print # lines for a record inside an If .Cells(r, "A").Value <> "" Then .... End If block.

John_w, I did as you said but I'm not sure about the END IF statement. I was able to get XML without empty rows which is great and thank you again but still not sure where the end If statement should be placed. Replace End With with End If? Please advise. I'm not really good with VBA just trying to learn from the best. Thank you so much.

With .ActiveSheet
For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If .Cells(r, "A").Value <> "" Then Print #1, " <record>"
If .Cells(r, "A").Value <> "" Then Print #1, " <Type>" & .Cells(r, "A").Value & "</Type>"
If .Cells(r, "A").Value <> "" Then Print #1, " <Type_Number>" & .Cells(r, "B").Value & "</Type_Number>"
If .Cells(r, "A").Value <> "" Then Print #1, " <Software>" & .Cells(r, "C").Value & "</Software>"
If .Cells(r, "A").Value <> "" Then Print #1, " </record>"
Next
End With

Print #1, "</data-set>"
Close #1

End With

End Sub

 

[John_w]

Replace End With with End If?

No. The End With ends the block of code which starts With .ActiveSheet

An If ... End If block like this:

            If .Cells(r, "A").Value <> "" Then
                Print #1, "  <record>"
                Print #1, "    <Type>" & .Cells(r, "A").Value & "</Type>"
                Print #1, "    <Type_Number>" & .Cells(r, "B").Value & "</Type_Number>"
                Print #1, "    <Software>" & .Cells(r, "C").Value & "</Software>"
                Print #1, "  </record>"
            End If

 

[Jorgi]

No. The End With ends the block of code which starts With .ActiveSheet

An If ... End If block like this:

            If .Cells(r, "A").Value <> "" Then
                Print #1, "  <record>"
                Print #1, "    <Type>" & .Cells(r, "A").Value & "</Type>"
                Print #1, "    <Type_Number>" & .Cells(r, "B").Value & "</Type_Number>"
                Print #1, "    <Software>" & .Cells(r, "C").Value & "</Software>"
                Print #1, "  </record>"
            End If

Thank you very much John_w it works perfect