+ Reply to Thread
Results 1 to 13 of 13

Generate XML from Excel sheet

  1. #1
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Generate XML from Excel sheet

    Hi,

    So, I have a file that generates an XML out of a worksheet. It has been set up ages ago by someone on a xls version and it takes ages to complete. I have no idea how to improve or create a new one, as I have not done this conversion type before. I hope someone can point out how can be improved.

    I've added a mock up file from my actual file as I cannot post the actual code in here, too many characters

    Thanks,
    Paul
    Attached Files Attached Files
    Last edited by PaulM100; 07-16-2019 at 04:44 AM.
    Click the * to say thanks.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Generate XML from Excel sheet

    First observation - code is well commented, shall be relatively easy to introduce improvements.

    Now on speeding things up:
    code all the time works on data taken from the sheet cell by cell.
    Loading sheet contents into an array and the referring to this array elements instead of cells could change minutes into seconds of excecution time
    like in such statement:
    Please Login or Register  to view this content.
    next point - instead of
    Please Login or Register  to view this content.
    where GetElementName is a function containing just one SelectCase instruction the names could be loaded into array and then used just as an array element.

    But ... what is the size of real file?

    I've checked sample data (corrected it, because of requirements about the format like 6 digits accounts, 4 digits cost centers, year as 2019/2020 etc.
    So made it 10 000 rows and on my (rather slow - i3, 4GB RAM) machine it works at some 500 rows per second and produces some ca 3.5MB xml file.
    Storing the sheet into array and then working on array makes it some 5000+ rows per second, so already 10 times improvement. The ammended code is in the attached file (of course shortened, to save the server space)
    Attached Files Attached Files
    Last edited by Kaper; 07-16-2019 at 07:44 AM.
    Best Regards,

    Kaper

  3. #3
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Generate XML from Excel sheet

    Hi Kaper, thank you for your solution and details on this. The actual file has a minimum of 30k lines of data. I just tested your code and indeed is really fast, but when I'm trying to open the XML file in notepad it stays in a not responding state. You have any ideas why?

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Generate XML from Excel sheet

    The first idea is: May be its too big for notepad?
    Have you tried to open it with other toll (for instance internet explorer)?

  5. #5
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Generate XML from Excel sheet

    Yeah, tried that and I figured that is size related. But the strangest part is that the old code saved an 66 kb xml file, and the one saved using your code has 9+ mb. Any reasons why?

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609
    That's bad. So probably now there is many more keys in xml file than it used to be before. May be some keys contained info from several records in a file. Use a small subset of data (say 5 - 8 records, but with 2-3 account numbers and 2-3 cost centers) and compare obtained xmls. If they are the same, use a bigger sample.

  7. #7
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Generate XML from Excel sheet

    so, I've tested it on 5 samples:10, 30,150,300,1000 rows of data and are the same. as well as the size. but when I try to use it on all 30k, the size increases.

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Generate XML from Excel sheet

    With your sample, took less than 1 sec when I tested current code.

    Few issue with your sample:
    1. CCC must be 4/8 char length and can't contain duplicates
    2. Account must be 6 char length numeric
    3. Year pattern must be either 2018/2019 or 2019/2020

    See amended sample.

    FYI - Another option, if your table structure remains consistent, is to set up xml mapping and use it to export xml without VBA. You can find details in link below.
    https://support.office.com/en-us/art...6-b3bf977a0c53

    Also attached sample workbook with xml Map added.
    Note: I didn't add "createdBy" cell mapping. As well, blank field will be skipped when exported as xml. To export xml file, you'd go to Developer Tab -> xml -> Export.

    EDIT: Oh and I forgot to mention, xml mapping method would not have validation on data (i.e. no duplicate, year pattern etc).
    EDIT2: More record you have longer it will take, since validations are performed on multiple columns at each row. And each row, will take about 250 bytes.
    Attached Files Attached Files
    Last edited by CK76; 07-16-2019 at 09:38 AM.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Generate XML from Excel sheet

    Oh, one more thing.

    If you use xml mapping method, you can export 30k row data in about 1~2 sec.
    Since it does not check data validity upon export.

    I'd recommend separating out data validation check from xml export portion.
    Last edited by CK76; 07-16-2019 at 10:05 AM.

  10. #10
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Generate XML from Excel sheet

    The xml mapping method is really,really great and helpful. Many thanks, you just saved me hours. As per your instructions I will keep them separately

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Generate XML from Excel sheet

    You are welcome and thanks for the rep

  12. #12
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Generate XML from Excel sheet

    Hi CK76,

    It's been a while since this thread was open, but I want to ask you, as I have no idea on how the XML mapping works, if the headers for the blank columns can be added in the map for each row, as per sample below, reed marked part? And how do I add created by part

    Thanks
    Attached Files Attached Files

  13. #13
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Generate XML from Excel sheet

    To include headers without data in export. You can just put "SomeName" in out of way cell. Right click on "createdBy" in XML Source pane and map it to that cell.

    As for headers without any value... typically, in XML header without data should not be included as it just takes up space and adds nothing to functionality.

    If you must, you could always use place holder value in cells (ex: single space, non-printing character). Or use some special character. Then use code or some other means to replace it once exported.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 02-20-2019, 09:20 AM
  2. [SOLVED] Auto generate sheets using template sheet and upon data entry. Link to main sheet
    By Coubs3 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-05-2017, 09:48 AM
  3. Replies: 1
    Last Post: 02-16-2016, 07:51 PM
  4. Intro
    By ksmith88 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 10-27-2014, 11:47 AM
  5. Replies: 2
    Last Post: 05-27-2014, 01:17 AM
  6. vba script to generate files based on an data from an Excel sheet
    By bananas in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-30-2007, 03:00 PM
  7. How to generate ms word doc files from excel sheet.
    By shishi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-18-2005, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1