Create variables, perform replace, then write text file.

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.

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

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

Sub LastRowAndColumn()
    Dim S1LC As Long
    Dim S1LR As Long
    Dim S2LC As Long
    Dim S2LR As Long
    Dim SheetCreated As Boolean
    
    Sheets("Input").Select
        S1LC = Sheets("Input").Cells(3, Columns.Count).End(xlToLeft).Column
        S1LR = Sheets("Input").Cells(Rows.Count, 3).End(xlUp).Row
        
     Sheets("Template").Select
        S2LC = Sheets("Template").Cells(1, Columns.Count).End(xlToLeft).Column
        S2LR = Sheets("Template").Cells(Rows.Count, 1).End(xlUp).Row
        
    Sheets("Input").Select
        Dim S1RowLength As Long
        S1RowLength = S1LR - 3
        
        ReDim S1SValue(1 To S1RowLength)
        x = 4
        For i = 1 To S1RowLength
            S1SValue(i) = Sheets("Input").Cells(x, 2)
            x = x + 1
        Next i
        
        Dim S1ColumnLength As Long
        S1ColumnLength = S1LC - 2
        
        ReDim S1RValue(1 To S1RowLength)
        y = 3
        x = 4
        SheetCreated = False
        For ii = 1 To S1ColumnLength
            For i = 1 To S1RowLength
                S1RValue(i) = Sheets("Input").Cells(x, y)
                x = x + 1
                If SheetCreated = False Then
                    Application.ScreenUpdating = False
                    Sheets("Template").Copy After:=Sheets("Template")
                    SheetCreated = True
                    ActiveSheet.Name = "TempSheet"
                End If
                Sheets("TempSheet").Select
                Columns(1).Select
                Selection.Replace What:=S1SValue(i), Replacement:=S1RValue(i), LookAt:=xlPart, _
                SearchOrder:=xlByColumns, MatchCase:=False
                Sheets("Input").Select
            Next i
            y = y + 1
            x = 4
            SheetCreated = False
            Open S1RValue(1) & ".txt" For Output As #1
                Sheets("TempSheet").Select
                For S2NR = 1 To S2LR
                    ExpData = Selection.Cells(S2NR, 1).Value
                    Print #1, ExpData
                Next S2NR
                Application.DisplayAlerts = False
                Sheets("TempSheet").Delete
                Sheets("Input").Select
            Close #1
            Application.DisplayAlerts = True
            Sheets("Input").Select
        Next ii
    MsgBox "Configs Generated"
End Sub

Yours is way cleaner... Thanks again.