Program652
asked on
Writing XML using Excel and VBA with a twist
Consider the following as a (very typical) excel worksheet:
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:
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.
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
...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
Wend
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)) & ">"
Next
sXML = sXML & "</row>"
iRow = iRow + 1
Wend
sXML = sXML & "</rows>"
Dim nDestFile As Integer, sText As String
''Close any open text files
Close
''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
Close
End Sub
Sub test()
MakeXML 1, 2, "C:\output2.xml"
End Sub
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER