Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Writing XML using Excel and VBA with a twist

Posted on 2014-10-15
Medium Priority
395 Views
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
``````
...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.
0
Question by:Program652

LVL 7

Accepted Solution

slubek earned 2000 total points
ID: 40382746
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
Next
``````
0

Author Comment

ID: 40383919
Schweet!  Just what I was looking for
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
###### Suggested Courses
Course of the Month9 days, 20 hours left to enroll