How to automatically get a specified range of data from Excel to XML in VBA

I need to automate the process in which the range of data is selected.

Right now the user gets prompted to input the data range through an input box and it creates an XML file with that data, but I need it so that the script gets the data range from the Excel sheet where it is specified in a cell Excel sheet example

In the end the XML file should look like this:

<?xml version="1.0"  encoding="ISO-8859-1"?>
<DeclarationFile>
<R13>
<K7>5555.555 </K7>
<K8>333.333 </K8>
<K9>22.22 </K9>
</R13>
<R14>
<K7>1.111 </K7>
<K8>2.222 </K8>
<K9>4.4444444 </K9>
</R14>
<R17>
<K7>444.44 </K7>
<K8>333.333 </K8>
<K9>9.999 </K9>
</R17>
</DeclarationFile>

Current script code:

Sub CreateXMLFile()
    Const THE_FOLDER As String = "C:\"
    Dim ws As Worksheet, rngData As Range, fName As String, rw As Long, col As Long
    Dim xml As String, tagId As String, tagVal As String, v
    
    
    fName = "C:\EDS\xml1.xml"
    
    
    On Error Resume Next
    Set rngData = Application.InputBox("2. Enter the range of data (Including Headers):", _
                                       "CreateXMLFile", Type:=8)
    On Error Resume Next
    
    If rngData Is Nothing Then
        Debug.Print "Range not specified"
        Exit Sub
    End If
    
    Open fName For Output As #1
    Print #1, "<?xml version=""1.0""  encoding=""ISO-8859-1""?>"
    Print #1, "<DeclarationFile>"
    
    For rw = 2 To rngData.Rows.Count
        tagId = rngData.Cells(rw, 1).Value
        Print #1, "<" & tagId & ">"
        For col = 2 To rngData.Columns.Count
            tagVal = rngData.Cells(1, col).Value
            v = rngData.Cells(rw, col).Value
            Print #1, "<" & tagVal & ">" & Replace(CheckForm(v), "&", "+") & "</" & tagVal & ">"
        Next col
        Print #1, "</" & tagId & ">"
    Next rw
    Print #1, "</DeclarationFile>"
    
    Open fName For Output As #1
    Close #1
    
    MsgBox fName & " created." & vbLf & "Done", vbOKOnly + vbInformation, "CreateXMLFile"
    Debug.Print fName & " saved."
End Sub

Function CheckForm(v) As String
    If IsNumeric(v) Then v = Format(v, "#.######## ;(0.########)")
    CheckForm = CStr(v)
End Function

I tried getting the range from the A1 cell into a string variable and then into rngData, but that only gave me an empty XML file:

<?xml version="1.0"  encoding="ISO-8859-1"?>
<DeclarationFile>
</DeclarationFile>

I also tried it with Range(), but I just kept getting errors.

Any help is appreciated!

>Solution :

Range method works, always identify the sheet where the range is located

    Set rngData = Sheets("Sheet1").Range(Sheets("Sheet1").Range("A1"))

Leave a Reply