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:


  • Maybe

    This will create a new workbook for each iteration of the For....Next loop, save that workbook as .xlm and close it.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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

  • Creating the new workbook is necessary for exporting as XML.


    The code does loop through values in column AS from AS4 onwards, those values are loaded into an array and then the code loops through the array. That looping is done within computer memory and is much faster that the code having to refer back to the worksheet for every values in column AS

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Ohh, so your xml schema exports only sheet 2?


    If so try

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Edited once, last by KjBox ().

  • 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"


  • lRow = .Cells(.Rows.Count, 20).End(xlUp) 'Here it breaks with value 45, but it pass with 20'

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

    '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'

    Sorry my mistake I forgot to remove ".Sheet1" when I amended the code.

    '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.

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


    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

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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...

    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

  • That would be perfect, but please retain at least some of the formulas.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Testing on an actual file certainly helps! Try this (make sure the button has the correct macro assigned to it, the sample file you sent had your original macro assigned).

    I also included the message box from your original code, in the event that the oMap is not exportable, which I omitted from my code. Also added a message box when code run completes to tell you the number of xml files created successfully.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Edited 3 times, last by 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

  • Sorry my bad, I removed your path for testing and forgot to put it back, without Path it will save to the same location as the original file.


    Change

    Code
    ActiveWorkbook.SaveAsXMLData File, oMap

    to

    Code
    ActiveWorkbook.SaveAsXMLData Path & "/" & File, oMap


    I will post code that allows you to select specific rows in Column AS (start row & end row) shortly.


    Do you only need continuous rows i.e. 20 to 40, or do you want to select individual non-continuous rows i.e. 20, 25 - 28, 35, 37, 39, 40?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

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

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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 :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!