I work with Boy Scouts of America as a volunteer (Assistant Scout Master)
I'm not the only adult in I/T (imagine that) who is an adult leader with the troop but I am the only one who scripts (sort of).
I was requested to make an Excel spreadsheet useable as a webpage (they think converting these things is just magic).
I looked at the converter Excel has and couldn't make heads nor tails out of it, I googled for scripts or plug-ins, then I kicked myself and wrote the VBScript.
The first thing I did was to define the XML tags for each column in the spreadsheet then I added the static tags.
The rest is just reading an Excel spreadsheet, formatting the data, and writing a text file.
I setup an HTML file to display the XML using an HTML table and dropped it in to the secure folder in the Troop website. The webmaster gets to finish.
I hope someone can use it to make life easier for themselves, it's not definitive by any stretch of the imagination but it works.
'### Excel to XML Converter
'### This code is the foundation for converting an Excel table to an XML file.
Option Explicit
'### Dimension XML Tag Variables
DIM XMLHeader, Hd1, Hd2, Hd3, Hd4, Hd5
DIM DBs, DBe
DIM R1s, R1e
DIM E1s, E1e, E2s, E2e, E3s, E3e, E4s, E4e, E5s, E5e
DIM E6s, E6e, E7s, E7e, E8s, E8e, E9s, E9e
DIM E10s, E10e, E11s, E11e, E12s, E12e, E13s, E13e, E14s, E14e
'### Dimension Variables
DIM oFile
DIM fSo1
DIM wrFile
DIM intRow
DIM CLM1, CLM2, CLM3, CLM4, CLM5, CLM6, CLM7, CLM8, CLM9, CLM10, CLM11, CLM12, CLM13, CLM14
DIM Sname, Sstreet, Scity, Sstate, Szip, Sdob, Srank, Srankd, Spatrol, Spos, Sposd, Sparent, Sphone, Semail
DIM Line1, Line2, Line3, Line4, Line5, Line6, Line7, Line8, Line9, Line10, Line11, Line12, Line13, Line14
DIM ObjExcel
DIM strPathExcel
DIM ObjSheet
'### Value XML Header
Hd1 = "<?xml version= "
Hd2 = """1.0"""
Hd3 = " encoding= "
Hd4 = """ISO-8859-1"""
Hd5 = " ?>"
XMLHeader = Hd1 & Hd2 & Hd3 & Hd4 & Hd5
'### Values XML Tag Variables
DBs = "<TROOPROSTER>"
DBe = "</TROOPROSTER>"
R1s = "<SR>"
R1e = "</SR>"
E1s = "<Scout>"
E1e = "</Scout>"
E2s = "<Street>"
E2e = "</Street>"
E3s = "<City>"
E3e = "</City>"
E4s = "<State>"
E4e = "</State>"
E5s = "<Zip>"
E5e = "</Zip>"
E6s = "<DOB>"
E6e = "</DOB>"
E7s = "<Rank>"
E7e = "</Rank>"
E8s = "<RankDate>"
E8e = "</RankDate>"
E9s = "<Patrol>"
E9e = "</Patrol>"
E10s = "<LdPosition>"
E10e = "</LdPosition>"
E11s = "<LdDate>"
E11e = "</LdDate>"
E12s = "<Parents>"
E12e = "</Parents>"
E13s = "<Phone>"
E13e = "</Phone>"
E14s = "<Email>"
E14e = "</Email>"
'### Value Variables
oFile = "ofile.xml"
intRow = 1
'### File System Object Definitions
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Set fSo1 = CreateObject("Scripting.FileSystemObject")
Set wrFile = fSo1.OpenTextFile(oFile, ForAppending)
wrfile.WriteLine XMLHeader
wrfile.WriteLine DBs
'### Excel Object Definitions
Set objExcel = CreateObject("Excel.Application")
strPathExcel = "C:\_Script\XML\TroopRoster.xls"
objExcel.Workbooks.open strPathExcel
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
'### Create the XML file from the Excel Spreadsheet
do while objSheet.Cells(intRow, 1).Value <> ""
CLM1 = Trim(objSheet.Cells(intRow, 1).Value) '### Scout
CLM2 = Trim(objSheet.Cells(intRow, 2).Value) '### Street
CLM3 = Trim(objSheet.Cells(intRow, 3).Value) '### City
CLM4 = Trim(objSheet.Cells(intRow, 4).Value) '### State
CLM5 = Trim(objSheet.Cells(intRow, 5).Value) '### Zip
CLM6 = Trim(objSheet.Cells(intRow, 6).Value) '### DOB
CLM7 = Trim(objSheet.Cells(intRow, 7).Value) '### Rank
CLM8 = Trim(objSheet.Cells(intRow, 8).Value) '### Rank Date
CLM9 = Trim(objSheet.Cells(intRow, 9).Value) '### Patrol
CLM10 = Trim(objSheet.Cells(intRow, 10).Value) '### Leadership Position
CLM11 = Trim(objSheet.Cells(intRow, 11).Value) '### Leadership Positoin Date
CLM12 = Trim(objSheet.Cells(intRow, 12).Value) '### Parents
CLM13 = Trim(objSheet.Cells(intRow, 13).Value) '### Phone
CLM14 = Trim(objSheet.Cells(intRow, 14).Value) '### Email
Sname = CLM1
Sstreet = CLM2
Scity = CLM3
Sstate = CLM4
Szip = CLM5
Sdob = CLM6
Srank = CLM7
Srankd = CLM8
Spatrol = CLM9
Spos = CLM10
Sposd = CLM11
Sparent = CLM12
Sphone = CLM13
Semail = CLM14
Line1 = E1s & Sname & E1e
Line2 = E2s & Sstreet & E2e
Line3 = E3s & Scity & E3e
Line4 = E4s & Sstate & E4e
Line5 = E5s & Szip & E5e
Line6 = E6s & Sdob & E6e
Line7 = E7s & Srank & E7e
Line8 = E8s & Srankd & E8e
Line9 = E9s & Spatrol & E9e
Line10 = E10s & Spos & E10e
Line11 = E11s & Sposd & E11e
Line12 = E12s & Sparent & E12e
Line13 = E13s & Sphone & E13e
Line14 = E14s & Semail & E14e
wrfile.WriteLine R1s
wrfile.WriteLine Line1
wrfile.WriteLine Line2
wrfile.WriteLine Line3
wrfile.WriteLine Line4
wrfile.WriteLine Line5
wrfile.WriteLine Line6
wrfile.WriteLine Line7
wrfile.WriteLine Line8
wrfile.WriteLine Line9
wrfile.WriteLine Line10
wrfile.WriteLine Line11
wrfile.WriteLine Line12
wrfile.WriteLine Line13
wrfile.WriteLine Line14
wrfile.WriteLine R1e
introw = introw + 1
Loop
wrfile.WriteLine DBe
ObjExcel.Workbooks.close
ObjExcel.Application.Quit
wrfile.close
wscript.Echo "Excel to XML Conversion Complete."
Wscript.Quit