Creating XML file using VBA Code

[drop05]

Hello, I am new to VBA and wanted to see if it is possible given a excel file with a sheet of data in it, and using VBA create an xml file? Is there ways or places to look at to get an idea/starting point? New to VBA and creating XML files.
Is there a format used with the code, any tips would i would be thankful for!
Thanks in advance.
Any other info needed from me please do let me know

 

[Rijnsent]

Hi there,
this is quite a good place to start: Working with XML files in VBA (VBA XML) - Analyst Cave
And as a working prototype, this is a macro I'm using to export styles from a workbook.
If you're a starting with VBA, these are some general tips:

• step through the code with F8, do not use F5 / the play button (when you're building code)
• when building code, learn to use break points in your code (F9, click in front of the line)
• open the Direct window & the local variables window to help you fix the code
• find a good use case: learning VBA can be really helpful, but if you have a purpose for that knowledge, it will grow much faster
• And if you get stuck: ask

Cheers,
Koen

Sub ExportStyles()

Dim St As Object

Application.ScreenUpdating = False

Set wb = ActiveWorkbook

ExpDir = ActiveWorkbook.Path
ExportNm = "my_file.xml"

'Start XML
Set XDoc = CreateObject("MSXML2.DOMDocument")
Set eRoot = XDoc.createElement("Root")
XDoc.appendChild eRoot

'style
Set eWbstyle = XDoc.createElement("WbStyle")
eRoot.appendChild eWbstyle
    
'ThemeColorScheme
Set eColors = XDoc.createElement("ThemeColorScheme")
eWbstyle.appendChild eColors
For c = 1 To Tm.ThemeColorScheme.count
    Set TCS = Tm.ThemeColorScheme(c)
    Set eElem = XDoc.createElement("Color")
    eElem.Text = TCS.RGB
    Set rel = XDoc.createAttribute("ThemeColorSchemeIndex")
    rel.NodeValue = TCS.ThemeColorSchemeIndex
    eElem.setAttributeNode rel
    eColors.appendChild eElem
Next c

'Styles
Set eStyles = XDoc.createElement("Styles")
eWbstyle.appendChild eStyles
For Each St In wb.Styles
    If St.BuiltIn = False Then
        Set eElem = XDoc.createElement("Style")
        eElem.Text = GetFormat(St)
        
        Set rel = XDoc.createAttribute("Name")
        rel.NodeValue = St.name
        eElem.setAttributeNode rel
        eStyles.appendChild eElem
    End If
Next St

'FONT
Set eFonts = XDoc.createElement("ThemeFontScheme")
eWbstyle.appendChild eFonts

Set FontMaj = Tm.ThemeFontScheme.MajorFont
Set FontMin = Tm.ThemeFontScheme.MinorFont
Set eElem = XDoc.createElement("MajorFont")
eElem.Text = FontMaj(1).name
eFonts.appendChild eElem
Set eElem = XDoc.createElement("MinorFont")
eElem.Text = FontMin(1).name
eFonts.appendChild eElem
    
'Save the XML file
XDoc.Save ExpDir & "\" & ExportNm

Application.ScreenUpdating = True

End Sub