PDA

View Full Version : Creating an xml file from each row in excel



garko75
08-01-2017, 08:31 AM
Hi All
As per my title I need to convert each row in excel to a single xml file in a certain folder.
I don't how to do that and I am not sure if it is allowed from excel-vba.
Please help me.
thanks
Garko

mdmackillop
08-01-2017, 10:46 AM
Something like this. You'll need to adjust the SaveAs line to suit

Sub Test()
pth = "C:\VBAX\"
Application.ScreenUpdating = False
Set r = ActiveSheet.UsedRange
For i = 1 To r.Rows.Count
Set ws = Sheets.Add
r.Rows(i).Copy ws.Cells(1, 1)
ws.Move
ActiveWorkbook.SaveAs Filename:=pth & "BookXX" & i & ".xlsx", FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
Next i
Application.ScreenUpdating = True
End Sub

garko75
08-02-2017, 12:27 AM
Hi Mdmackillop, Thanks for helping me. I tried your code, yet there is something that I have not understood. When I launch the code the create .xlsx files but I'd need those as .xml so I tried to adjust the code:
ActiveWorkbook.SaveAs Filename:=pth & "BookXX" & i & ".xlsx", FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.SaveAs Filename:=pth & "BookXX" & i & ".xml", FileFormat:=xlOpenXMLWorkbook
My problem is that I also need to apply a certain structure to xml files. What I am thinking is to have column header with the tag names so I have to keep also the first line for each row. Sorry but even if I have a bit of experience with VBA don't know how to go so far with xml

mdmackillop
08-02-2017, 03:15 AM
I know nothing about XML and recording the action fails for lack of XML mapping or such. See here for file formats (https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlfileformat-enumeration-excel)
If you need the top and other rows then use

r.Rows(1).Copy ws.Cells(1, 1)
r.Rows(i).Copy ws.Cells(2, 1)