PDA

View Full Version : Export to XML



DragonWood
10-02-2017, 02:08 PM
Greetings,

I am trying to export the customer information from my Excel file to a XML file.

I tried using the same export code I’ve used for both exporting to PDF and to Text file. However, it’s not working.

Note: This code works fine for the text and PDF export. It just doesn't work for XML.


Public Sub ExportDailyActivities()
'Exports the data to the Daily Activities.txt file and puts it in the same directory as this Workbook

Application.ScreenUpdating = False

With Sheets("Daily Activities")

Dim intDACount As Integer
Dim lngRow As Long
Dim lngCount As Long
Dim strFile As String

strFile = ThisWorkbook.Path & "\" & "Daily Activities.txt"
lngRow = Range("B" & Rows.Count).End(xlUp).Row
intDACount = FreeFile

Open strFile For Output As #intDACount

For lngCount = 1 To lngRow
Print #intDACount, .Range("A" & lngCount).Value; vbTab; .Range("B" & lngCount).Value; vbTab; .Range("C" & lngCount).Value; vbTab; .Range("D" & lngCount).Value; vbTab; .Range("E" & lngCount).Value

Next lngCount

Close #intDACount

End With

Application.ScreenUpdating = True

End Sub


I would appreciate some help.

Thanks.

Kenneth Hobs
10-02-2017, 07:04 PM
It is more involved than that. You need to create a schema or xml map. e.g. https://support.office.com/en-us/article/Map-XML-elements-to-cells-in-an-XML-Map-ddb23edf-f5c5-4fbf-b736-b3bf977a0c53?NS=EXCEL&Version=16&SysLcid=1033&UiLcid=1033&AppVer=ZXL160&HelpId=xlmain11.chm327910&ui=en-US&rs=en-US&ad=US

snb
10-03-2017, 04:27 AM
1. Make a listobject of your data

Then you can use:


Sub M_snb()
c00 = "<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>" & vbLf & "<root>" & vbLf

For Each it In ListObjects(1).HeaderRowRange
c01 = c01 & Replace("<~>#</~>", "~", it)
Next
c01 = "<record>" & c01 & "</record>" & vbLf

sn = ListObjects(1).DataBodyRange
For j = 1 To UBound(sn)
c02 = c02 & c01
For jj = 1 To UBound(sn, 2)
c02 = Replace(c02, "#", sn(j, jj), , 1)
Next
Next
c02 = c02 & "</root>"

CreateObject("scripting.filesystemobject").CreateTextFile("G:\OF\snb.xml").write c00 & c02
End Sub