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

0 votes

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.

Feb 27, 2023 in Others by Kithuzzz
• 38,000 points
831 views

1 answer to this question.

0 votes

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

    Set rngData = Sheets("Sheet1").Range(Sheets("Sheet1").Range("A1"))
answered Mar 18, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

How to get rid of a #value error in Excel?

Changing the format to "Number" doesn't actually ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,600 points
786 views
0 votes
1 answer

How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,600 points
1,130 views
0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,600 points
2,339 views
0 votes
1 answer

How to remove borders from cells in a range in Excel using VB.net?

range.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle ...READ MORE

answered Jan 5, 2023 in Others by narikkadan
• 63,600 points
1,840 views
0 votes
1 answer
0 votes
1 answer

Convert image (jpg) to base64 in Excel VBA?

Heres a function. Can't remember where I ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,600 points
3,044 views
0 votes
1 answer

Sending excel data to Tally

You can use Requests to send the ...READ MORE

answered Nov 19, 2022 in Others by narikkadan
• 63,600 points
889 views
0 votes
1 answer

How can I preserve the format while exporting data from excel to evernote

The contents for an Evernote note are ...READ MORE

answered Jan 5, 2023 in Others by narikkadan
• 63,600 points
1,098 views
0 votes
1 answer

How to programmatically get the values of a spilled Excel range in VBA?

By using the Text property, I was ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,600 points
1,219 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
3,720 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP