asked on

How to Insert an XML Tag in a Certain Place in the XML Document

I have some VBA Code in Sheet1 that loops through Sheet1 of the spreadsheet and places certain data on Sheet2, then the Code creates an XML file from the data on Sheet2.

I'm not sure exactly how to incorporate code to my existing code that will place the XML Tags in the proper place (I have included the XML file to show you the different tags/structure of the XML file).

Here is the VBA Code that creates the XML File:

Public Function ExportToXML(FullPath As String, RowName As String) As Boolean
On Error GoTo ErrorHandler

Dim colIndex As Integer
Dim rwIndex As Integer
Dim asCols() As String
Dim oWorkSheet As Worksheet
Dim sName As String
Dim lCols As Long, lRows As Long
Dim iFileNum As Integer
 
Application.ScreenUpdating = False
 
Set oWorkSheet = ThisWorkbook.Worksheets(1)
sName = oWorkSheet.Name
lCols = oWorkSheet.ListObjects(1).ListColumns.Count
lRows = oWorkSheet.ListObjects(1).ListRows.Count

 ReDim asCols(lCols) As String

 iFileNum = FreeFile
 Open FullPath For Output As #iFileNum
 
' Change the Column Names
  For i = 0 To lCols - 1
     'Assumes no blank column names
     If Trim(Sheet2.Cells(1, i + 1).Value) = "" Then Exit For
     Select Case Sheet2.Cells(1, i + 1).Value
        Case "InvIndex"
            ColName = "InvMaster KeyValue"
        Case "InvElecHistoryID"
            ColName = "InvElecHistory"
        Case "DateSubmitted"
            ColName = "DateSent"
        Case Else
            ColName = Sheet2.Cells(1, i + 1).Value
     End Select
     asCols(i) = ColName
 Next i
 
 If i = 0 Then GoTo ErrorHandler
 lCols = i
 
 Print #iFileNum, "<InvMaster xmlns=""http://elite.com/schemas/transaction/process/write/InvMaster"">"
 Print #iFileNum, "<Initialize xmlns=""http://elite.com/schemas/transaction/object/write/InvMaster"">"
 Print #iFileNum, "<Edit>"
 
 For i = 2 To lRows
 If Trim(Sheet2.Cells(i, 1).Value) = "" Then Exit For
     For j = 1 To lCols
         If Trim(Sheet2.Cells(i, j).Value) = "InvIndex" Then
         Print #iFileNum, "  <" & asCols(j - 1) & ">"
         Print #iFileNum, Trim(Sheet2.Cells(i, j).Value);
         Print #iFileNum, "</" & asCols(j - 1) & ">"
         End If
         
         If Trim(Sheet2.Cells(i, j).Value) <> "" Then
         Print #iFileNum, "  <" & asCols(j - 1) & ">"
         Print #iFileNum, Trim(Sheet2.Cells(i, j).Value);
         Print #iFileNum, "</" & asCols(j - 1) & ">"
         
         DoEvents 'OPTIONAL

         End If
 
     Next j
 Next i

 Print #iFileNum, "</Children>"
 Print #iFileNum, "</Edit>"
 Print #iFileNum, "</Initialize>"
 Print #iFileNum, "</InvMaster>"
 
 ExportToXML = True
ErrorHandler:
 If iFileNum > 0 Then Close #iFileNum
 Exit Function
 
Application.ScreenUpdating = True

End Function

Open in new window

Here is an example of what the XML file that is created from the above VBA Code should look like:

<InvMaster xmlns="http://elite.com/schemas/transaction/process/write/InvMaster">
    <Initialize xmlns="http://elite.com/schemas/transaction/object/write/InvMaster">
        <Edit>

            <InvMaster KeyValue="707786">
                <Children>
                    <InvElecHistory>
                        <Edit>
                            <InvElecHistory KeyValue="GUID">
                                <Attributes>
                                    <DateSent>0001-01-02T00:00:00</DateSent>
                                    <Comments>NewValue</Comments>
                                </Attributes>
                            </InvElecHistory>
                        </Edit>
                    </InvElecHistory>
                </Children>
            </InvMaster>



            <InvMaster KeyValue="707786">
                <Children>
                    <InvElecHistory>
                        <Edit>
                            <InvElecHistory KeyValue="GUID">
                                <Attributes>
                                    <DateSent>0001-01-02T00:00:00</DateSent>
                                    <Comments>NewValue</Comments>
                                </Attributes>
                            </InvElecHistory>
                        </Edit>
                    </InvElecHistory>
                </Children>
            </InvMaster>


            <InvMaster KeyValue="707786">
                <Children>
                    <InvElecHistory>
                        <Edit>
                            <InvElecHistory KeyValue="GUID">
                                <Attributes>
                                    <DateSent>0001-01-02T00:00:00</DateSent>
                                    <Comments>NewValue</Comments>
                                </Attributes>
                            </InvElecHistory>
                        </Edit>
                    </InvElecHistory>
                </Children>
            </InvMaster>


        </Edit>
    </Initialize>
</InvMaster>

Open in new window

I've never worked with XML. Could someone please help me on this?

Thanks,
gdunn59

regmigrant

When you store data in an excel table you have a heading row and a list of data.

In excel the headings are treated as the XML Tags and the data underneath is the value of each of the tags for a given instance (or row). How the Excel headings are translated to XML tags is stored in an XML MAP - but you aren't using Excel XML functionality here - you are creating a text file which uses some XML standard formatting (like angle brackets <>)

The sequence 'Select Case' is reading data on sheet2 and creating column names in the active sheet whilst the second loop (For j = 1 To lCols) is reading the table and outputting the data to a text file with <> around it. Outside of this loop you have some standalone statement outputting some other XML style tags (ie: data wrapped in <> like <children>).

for this vba to produce the file listing shown you will need to have a column heading for each of the tags:-
<InvMaster KeyValue="707786">
<Children>
<InvElecHistory>
<Edit>
<InvElecHistory KeyValue="GUID">
<Attributes>
<DateSent>0001-01-02T00:00:00</DateSent>
<Comments>
your code then needs to loop through the data in each row of the table and generate output line for each cell with the header name as the tag followed by the cell contents as the data, once you reach the end of the row it will then need to output the close tag sequence by outputting the header with a / in front:
</Attributes>
</InvElecHistory>
</Edit>
</InvElecHistory>
</Children>
</InvMaster>

this will be quite a useful and instructive way of learning about well formed XML but if you download the MS Addin I recommended in the other question you can start to use excel built in XML handling - in that case you would create the table in excel, create a map of the table headers to XML tags and excel would handle the creation of the well formed XML automagically