VBA code - Export to xml and save as name from excel cell

In my workbook I have sheet1 which contains my table with some 2000+ rows. This is my data source.

On sheet2 I have something like form which is already mapped with an xml schema and can be exported (right click/xml/export or developer tab/xml/export).

What I need is to create as many xml files as there is rows in my source table. I need to copy from source only one cell (starting from AS4) to my form "F2" and all other fields will be filled based on that value (added by xlookup).

What VBA code suppose to do is:

1. Copy from sheet1 "AS4" (source table) to sheet2 "F2" (form sheet)
2. Export from sheet2 to xml
3. Save xml to folder whose path is written in sheet2 "I2"
4. File name to be taken from sheet2 "I3"
5. Copy from sheet1 "AS5"...and so on to the last row

I would appreciate if someone has some solution.

Thanks

I have made something like this, but it does not work:

Sub ExportToXml_2()


Dim i As Long, LastRow As Long
Dim path As String
Dim file As String
Dim sh As Worksheet


LastRow = Worksheets("sheet1").Range("AS2000").End(xlUp).Row


'For i = 4 To LastRow


Set sh = ActiveSheet


sh.Range("F2").Value = Worksheets("sheet1").Range("A" & i).Value


file = sh.Range("I3").Value
path = sh.Range("I2").Value


Dim objMapToExport As XmlMap
Set objMapToExport = ActiveWorkbook.XmlMaps("Mapname_Map")


If objMapToExport.IsExportable Then
ActiveWorkbook.SaveAsXMLData path & "\" & file, objMapToExport
Else
MsgBox "Neodgovarajuća šema za eksport XML " & objMapToExport.Name
End If


'Next i


End Sub

Hi KjBox. Thanks a lot for your help.

However, this is not what I need.

It is not necessary to create new workbook on the loop.

The loop should only copy one cell from source table (start from "AS4" to "I2" of my template which is mapped to xml schema and the rest of vba code (which I already works) will export template to xml to the path in I2 and saved as name in I3.

Next iteration: copy AS5 to "I2" / export to xml

Next iteration: copy AS6 to "I2" / export to xml and so on to the first blank row.

Would appreciate if you can adjust the code to work as explained above.

Thanks

Exactly. I don't want to save as xml the whole workbook, but just to export sheet2 to xml which is already mapped.

I'm gonna try it in minute. Thanks

I'm not getting it to work.

Here is your code with my real sheet names which I have added: sheet "Porez Srbija" contains source for loop, sheet "PP-OPO" contains exportable xml map called "PodaciPoreskeDeklaracije_Map"

Sub ExportToXml_3()
Dim x, i&, lRow&, Path$, File$, oMap As XmlMap


Application.ScreenUpdating = 0


With ActiveWorkbook
Set oMap = .XmlMaps("PodaciPoreskeDeklaracije_Map")
With .Sheets("Porez Srbija") 'instead of sheet1)
lRow = .Cells(.Rows.Count, 20).End(xlUp) 'Here it breaks with value 45, but it pass with 20'
x = .[as4].Resize(lRow) 'On this place it brakes with .sheet1 and also when I have replaced .sheet1 with my real sheet name, but it pass when I have removed .sheet1'
End With


With .Sheets("PP-OPO")
For i = 1 To UBound(x, 1)
.Cells(2, 6) = x(i, 1)
Path = .Cells(2, 9): File = .Cells(3, 9)
If oMap.IsExportable Then
.SaveAsXMLData Path & "\" & File, oMap 'Finally, it breaks here. Not sure what could be the reason. Path and file are ok. These cells contain formulas, but should not be the issue.
End If
Next
Set oMap = Nothing
End With
End With


End Sub

Quote from KjBox

You said you need the values in Column AS, that is Column number 45, Column number 20 is Column T

My mistake. For a moment I thought it is number of row, but yes, I need column 45.

Quote

No idea why it would break there when you said that it worked before with the same code.

In my sample it worked with active workbook, not with sheet, not sure if that has something to do with break...

Quote from KjBox

Can you attach your workbook, I will look and see what could be causing the error, but cannot test fully because I do not have your xml schema

These are all sensitive information and contains personal data. I will try to make an excel file which would contain only these two sheets, xml map and a few samples with imaginary names. That would make thing much easier for you.

Thanks

I think this is all you need.

Quote from KjBox

...(make sure the button has the correct macro assigned to it, the sample file you sent had your original macro assigned).

I was aware about this. For testing purposes I have run macro through Developer tab/macros.

Before I test new vba, would there be any option to limit number of rows or iterations? Would not like to create 2000+ xml just for testing and also, I might need sometimes just partial number of rows, for example from row - to row.

Edit: made to test with a few rows. The path is missing, so it saved xml files in C/Documents, but it created new folders as well under the same name as xml files. This part of code I would say: ActiveWorkbook.SaveAsXMLData File, oMap

Thanks

Continuos rows will be perfectly enough.

Quote from KjBox

BTW, much easier than going to Developer tab>Macros is just to press alt+f8

Thanks, great to know

This works like a charm now

Hey, don't need to add filter by row range, I will resolve it in some other way. You have already done much for me, please do not waste time on this.

I will mark this one as resolved.

Thanks a lot for all your help