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.
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
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.
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.
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?
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.
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
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.
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.
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.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks