How do you program Excel to import/export data to XML files

[Guest]

I am looking for some guidance, pointers and examples to do the following in
Excel 2003 (or later).
Given a workbook with 3 worksheets with each worksheet containing unique
tables, I would like to add toolbar menu options such that the user is able
to:
1. Export every table to one single XML file
2. Export a single table to an XML file
3. Import XML files into the workbook overriding all exiting data
4. Import XML files into the workbook appending the existing data
This file will be a template file where users can save and share data
through the XML files.
Any help is appreciated. I’ve found some XML information but not to the
level I need to achieve.

TIA
- Pat

 

[gimme_this_gimme_that]

Are you on board with using a VBA solution?

 

[Guest]

Yes, I have no problems with VBA.

 

[gimme_this_gimme_that]

Here is a clip of some code in one of my Worksheets.

This shows you how to use #1 to write to a file.

The function BuildXML would have to be modified to match the data in
your application ....


Post an example element of your XML complete with attributes and I'll
take a look at reading and parsing it. (You could do that yourself by
reading up on the XML DOM and MSXML6.0.)


Sub WriteExcelData()
Open "C:\\ed2007.xml" For Output As #1
' change ' into '' for sqlplus after creating xml file

Sheets("Sheet1").Select
Range("A1").Select

Dim i As Integer
Dim s As String

Print #1, "<ExcelData>"
For i = 0 To 300
s = ActiveCell.Offset(i, 0)
If "" <> s Then
t = BuildXML(i + 1, 1, "CAN")
'MsgBox (t)
Print #1, t
End If
Next i
Print #1, "</ExcelData>" + Chr(10)

Close #1

End Sub


Function BuildXML(irow As Integer, ilob As Integer, c As String) As
String

Dim phdr As String
Dim ptt As String
Dim lcnt As Integer
Dim j As Integer


jct = "<CategoryName>" + Format(ActiveCell.Offset(irow - 1, 2)) + "</
CategoryName>" + Chr(10)
jcn = "<CategoryDisplayNumber>" + Format(ActiveCell.Offset(irow - 1,
3)) + "</CategoryDisplayNumber>" + Chr(10)
jhdr = "<CategoryCreate>" + Format(ActiveCell.Offset(irow - 1, 4)) +
"</CategoryCreate>" + Chr(10)

ctr = "<Country>" + c + "</Country>" + Chr(10)

lcnt = ActiveCell.Offset(irow, 1)
slabels = "<Labels>" + Chr(10)
smins = "<Min2007s>" + Chr(10)
smaxs = "<Max2007s>" + Chr(10)
seds = "<GuiEdits>" + Chr(10)

bJ = "<Bundle>" + Chr(10)
Ej = "</Bundle>" + Chr(10)

bL = "<Label>"
eL = "</Label>" + Chr(10)
bM = "<Min2007>"
eM = "</Min2007>" + Chr(10)
bN = "<Max2007>"
eN = "</Max2007>" + Chr(10)
bG = "<GuiEdit>"
eG = "</GuiEdit>" + Chr(10)

m = 0

For j = 0 To lcnt - 1
slabels = slabels + bL + ActiveCell.Offset(irow + j, 2) + eL
smins = smins + bM + Format(ActiveCell.Offset(irow + j, 3)) + eM
smaxs = smaxs + bN + Format(ActiveCell.Offset(irow + j, 4)) + eN
seds = seds + bG + ActiveCell.Offset(irow + j, 5) + eG
Next j

slabels = slabels + "</Labels>" + Chr(10)
smins = smins + "</Min2007s>" + Chr(10)
smaxs = smaxs + "</Max2007s>" + Chr(10)
seds = seds + "</GuiEdits>" + Chr(10)

BuildXML = bJ + jhdr + jct + jcn + phr + slabels + smins + smaxs +
seds + ctr + Ej

End Function

 

[Guest]

Thanks gimme_this_gimme_that,

This is getting me in the roght direction.
I will study your code and create an example workbook.
I'll post for your critique. I will not get to if for a few weeks.

- Pat