+ Reply to Thread
Results 1 to 11 of 11

Create multiple XML files from spreadsheet

  1. #1
    Registered User
    Join Date
    12-27-2012
    Location
    Nowhere
    MS-Off Ver
    Excel 2011
    Posts
    3

    Create multiple XML files from spreadsheet

    Hello, I come here in search for help and guidance

    Here's what I have to do:
    I have a spreadsheet with many lines. Each Line represents a file, and the rows in each line are values to be written in this file.

    File Name | Value 1 | Value 2|
    -----------------------------------
    001 | 27 | 58 |
    -----------------------------------
    002 | 21 | 69 |

    ...and so on

    Now, for each line, I need to write and save an XML file with some static xml data (stuff that is always the same for each xml) and the values from the spreadsheet.

    Example

    XML
    <bla bla bla>
    <bla bla bla>
    <bla bla bla>
    <bla bla bla>

    <bla bla = Value 1>
    <bla bla = Value 2>

    <bla bla bla>
    <bla bla bla>
    <bla bla bla>
    /XML


    I'm totally new to VBA, so any advice is welcome.
    Thanks in advance.

  2. #2
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Create multiple XML files from spreadsheet

    You have a few options however without more information it is difficult to give an exact answer. You could use DOMDocument to create an XML file or consider that an XML file is basically a text file and you could just write the data to a text file including the elements/nodes that you require. You will need to loop through each line of your excel worksheet, split the data on each line and then write the data to an xml file. Google DOMDocument or take a look at this link for a code example
    Without knowing the data structure or the nodes required it is impossible to give much more direction. If you upload a sample workbook containing no sensitive data as well as the exact format of the XML file (rather than bla bla) then perhaps you may get a more specific answer.
    Hope this helps.
    Anthony
    Pack my box with five dozen liquor jugs
    PS: Remember to mark your questions as Solved once you are satisfied. Please rate the answer(s) by selecting the Star in the lower left next to the Triangle. It is appreciated?

  3. #3
    Registered User
    Join Date
    12-27-2012
    Location
    Nowhere
    MS-Off Ver
    Excel 2011
    Posts
    3

    Re: Create multiple XML files from spreadsheet

    Workbook1.xlsxpano1.xml.txt
    Thanks for the kind reply
    Ok, so the whole thing is for a virtual tour with quite a few picture in it. The panorama plugin uses one xml
    file for each panorama, where it defines the coordinates of the hotspots needed to move forward / backward.
    In the excel sheet, I give the name of the panorama (same as the xml file), and then give values for the
    hotspots to be placed in that panorama.
    In the XML, look for <hotspot name="">, those are the nodes I need to add and write the value ath="" as reported in the excel sheet.

  4. #4
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Create multiple XML files from spreadsheet

    Since you are only changing 4 values in the main xml file it is easier just to treat the file as a text file. The code works by reading a "stock standard file" called pano1.xml.txt (which I have attached) - I have altered this file so the code can find the sections that need to be replaced with data from your worksheet. Do not alter this text file as it is used to determine the replacement areas. This file is read and then the code iterates through the excel worksheet replacing the values. You will need to alter the location of the pano1.xml.txt file in the code and the output directory for the xml files. The code just generates files called pano1.xml, pano2.xml etc. The code is as follows. Workbook attached. Any problems let me know.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-27-2012
    Location
    Nowhere
    MS-Off Ver
    Excel 2011
    Posts
    3

    Re: Create multiple XML files from spreadsheet

    Wow, thanks! I took some time to actually test it all out and it works!
    The only "problem" is I can only change a limited number of settings already given in the txt file. That is fine in some case, but regarding hotspots, each xml file will have a different number of hotspots (xml1 could have 5, xml2 could have 3). Could I make a template file with, say, 20 hotspots and then make it so that if I set a vale of 0 or leave the cell blank, the script won't write those nodes in the output xml?

  6. #6
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Create multiple XML files from spreadsheet

    You could create a template to do this and use the same procedure. You need to balance your requirements against actually creating the whole XML file from code rather than a template. I would likely use arrays to store each file (xml1, xml2 etc) requirements/replacements so that way you can loop through rather than coding each file separately.

  7. #7
    Registered User
    Join Date
    12-22-2010
    Location
    Greece
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Create multiple XML files from spreadsheet

    Hi, I found this thread very useful.

    I have a similar problem. I have a table with records that I want to create multiple xml files from it.
    I have an xml file in the resulting format to use as base.
    I also have an xsd and dtd file, but the solution of the macro seems better.

    The difference with the above table is that I need to put multiple records in one xml.
    I have an extra column that contains a common keycode for repetitive objects (rows of data).
    I need the macro to count the common keycodes and:

    1. store that count number to the resulting xml file in a certain field.
    2. use the common keycode as file name
    3. create one object for each row of data with common keycode

    Thanks

  8. #8
    Registered User
    Join Date
    01-09-2013
    Location
    Türkiye
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Create multiple XML files from spreadsheet

    Hi,

    I have a similar problem and I am a very very beginner. I want to create multiple files to import 'n Navisworks. One of the files I am already importing and my sample excel file is attached.
    Attached Files Attached Files

  9. #9
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Create multiple XML files from spreadsheet

    Good morning ertan_yuce_1974

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  10. #10
    Registered User
    Join Date
    05-29-2012
    Location
    Cyprus
    MS-Off Ver
    Excel 365
    Posts
    45

    Re: Create multiple XML files from spreadsheet

    Hi smuzoen,

    You do a good job on the above, it helps me a lot and saves me from a lot of work.

    Can I ask a question? When i have Greek characters i get unreadable xml values. There is any way to solve this?

  11. #11
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Create multiple XML files from spreadsheet

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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