Writing XML using Excel and VBA with a twist

Consider the following as a (very typical) excel worksheet:
Study	Filo	Mean	Olp	Std Dev	Bell
Sun	9	29.998	77	33.887	G
Mercury	66	30.686	29	37.03	R
Venus	53	993.09	65	643	H
Earth	44	44.099	22	34.06	J
Mars	78	77.94	90	22.796	B

Open in new window

...and so on for sometimes MANY rows and columns.
The goal it to create an .xml file from *some* columns of the worksheet.  I found the following code that writes all of the rows and columns with the exception of the 'label' row:
Sub MakeXML(iCaptionRow As Integer, iDataStartRow As Integer, sOutputFileName As String)
    Dim Q As String
    Q = Chr$(34)

    Dim sXML As String
    sXML = "<?xml version=" & Q & "1.0" & Q & " encoding=" & Q & "UTF-8" & Q & "?>"
    sXML = sXML & "<rows>"
    ''--determine count of columns
    Dim iColCount As Integer
    iColCount = 1
    While Trim$(Cells(iCaptionRow, iColCount)) > ""
        iColCount = iColCount + 1

    Dim iRow As Integer
    iRow = iDataStartRow

    While Cells(iRow, 1) > ""
        sXML = sXML & "<row id=" & Q & iRow & Q & ">"

        For icol = 1 To iColCount - 1
           sXML = sXML & "<" & Trim$(Cells(iCaptionRow, icol)) & ">"
           sXML = sXML & Trim$(Cells(iRow, icol))
           sXML = sXML & "</" & Trim$(Cells(iCaptionRow, icol)) & ">"

        sXML = sXML & "</row>"
        iRow = iRow + 1
    sXML = sXML & "</rows>"

    Dim nDestFile As Integer, sText As String

    ''Close any open text files

    ''Get the number of the next free text file
    nDestFile = FreeFile

    ''Write the entire file to sText
    Open sOutputFileName For Output As #nDestFile
    Print #nDestFile, sXML
End Sub

Sub test()
    MakeXML 1, 2, "C:\output2.xml"
End Sub

Open in new window

and it works very well and gets me close, but I need something different.  
I have to write exactly three columns: Study, Mean and Std Dev.  But here is the thing:  I need to somehow write only those columns, but the columns can be in any order on the worksheet, and I can't change that.  I can't discern how to reference those columns by name [represented by the values in row 1] (the lookup function seems to come close but does not seem to work when referencing 'variable' cell coordinates) and output the cell values when stepping through the worksheet.
How can I modify the above to only output those columns I need, regardless of their column order in the worksheet?  Everything I've seen wants a range (have no idea) or a table.
As you might have guessed, I need to avoid opening these and rearranging the columns to make the code easier, but I have a great many of them and need to automate it as much as possible.
Thanks, and hopefully that is a clear explanation of my problem.
Who is Participating?
slubekConnect With a Mentor Commented:
Loop through all columns, but add cell value to sXML only if there is a proper column header.
I mean something like this:
For icol = 1 To iColCount - 1
  if (Cells(iCaptionRow,icol)="Study") or (Cells(iCaptionRow,icol)="Mean") or (Cells(iCaptionRow,icol)="St dev") then
    sXML = sXML & "<" & Trim$(Cells(iCaptionRow, icol)) & ">"
    sXML = sXML & Trim$(Cells(iRow, icol))
    sXML = sXML & "</" & Trim$(Cells(iCaptionRow, icol)) & ">"
  end if

Open in new window

Program652Author Commented:
Schweet!  Just what I was looking for
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.