Create variables, perform replace, then write text file.

**tunerX** · 09-15-2012, 05:02 PM

I have never tried to do any type of macros or coding with Excel before, and am totally lost. My only experience is with Perl. My previous workbook(workbook2 attached) had 2 sheets. Sheet1 used column formatted data for ease of input. I then linked the data from column format to a row format in sheet2. Then I had to export the sheet2 as a CSV. Then I took the CSV and fed that as input to a Perl script. The Perl script would open a template, then use the input from the CSV to create an array for each row in the CSV. Finally the Perl script would do a search and replace and create multiple text files with my CSV data.

Workbook2.xlsx

I am thinking that with my second workbook(workbook1 attached). I could do the same thing without having to have Perl or an external template. I want to take the input from sheet1 and do a search and replace and create multiple files named "##NAME##.xml from the template in sheet2 and the data in sheet1.

Workbook1.xlsx

With my example workbook(workbook1 attached) I want to end up with two files (named from the ##NAME## cell) containing the text below.

---John Jones.xml---

<?xml version="1.0" encoding="utf-8"?>
<ConfigData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" AssemblyVersion=".01" AppConfigVersion=".01" ProductName="SAMPLE" ProductVersion="1.0">
<NAME>John Jones</NAME>
<PLACE>Washington Square</PLACE>
<DATE>9/20/2012</DATE>
<TIME>16:00:00</TIME>

---Bill Baily.xml---

<?xml version="1.0" encoding="utf-8"?>
<ConfigData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" AssemblyVersion=".01" AppConfigVersion=".01" ProductName="SAMPLE" ProductVersion="1.0">
<NAME>Bill Baily</NAME>
<PLACE>Lincoln Plaza</PLACE>
<DATE>9/21/2012</DATE>
<TIME>17:00:00</TIME>

Any help would be appreciated.

**watersev** · 09-15-2012, 07:53 PM

hi tunerX, welcome to Excelforum, please check attachment, save the file, press Run button. New files will be created in the same location the Excel workbook is located

**tunerX** · 09-16-2012, 02:00 AM

Thanks watersev
I hacked my way through it and came up with the following...

Please Login or Register  to view this content.

Yours is way cleaner... Thanks again.

**tunerX** · 09-16-2012, 02:02 AM

Found that watersevs file doesn't work to well with mac 2011 with large template files and files with ^M new line feeds. I keep getting an error when I paste over 200 lines of text into the template sheet. The error I get is "Document Not Saved", which means I cannot save the file after setting up the template.

My code is about 6-10 seconds slower on the same amount of data but my code has now scaled to several thousand lines of sheet2 template entries and over 100 sheet1 columns of input entries with several dozen variables per column. With the larger template files and greater number of column entries it is taking a minute or so to generate the files.

So far I have generated 400 router/switch/firewall/VPN configuration files.