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

Question

I must automate the procedure for choosing the data range.

I need it such that the script obtains the data range from the Excel sheet where it is defined in a cell instead of now prompting the user to input the data range through an input box and creating an XML file with that data.

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.

Answer 1

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

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