Create multiple workbook templates from delimited text file.


New Member
Apr 12, 2013
I am in need of assistance with creating a VBA script to pull info from a delimited text file and create multiple new spreadsheets.

What I have is a spreadsheet that is used for submitting vender disbursements. I need to take the existing form, fill in various cells with vender info from a delimited text file and then save each vender to a new spreadsheet as a "template".

Here is the details for they want:

  1. Vendor Name – cell B9
  2. Vendor Address – cells B11, B12, B13 and B14
  3. Receiving Bank ABA Number – cell B17
  4. Receiving Bank Name – cell D17
  5. Receiving Bank Account Number – cell B19
  6. If possible, alter the text in cell F1 to reflect “CHECK (X) WIRE ( ) ACH ( )” when no banking information is present and “CHECK ( ) WIRE ( ) ACH (X)” when banking information is present

Headers from ~ (tilde) delimited text file:

Help, way in over my head here.

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi, welcome to the forum!

Leaving myself a quick note here to come back and look at this tomorrow as I don't have time tonight. If I don't I'll forget :rolleyes:
If there's no solution up by the time I look at it tomorrow I'll have a go at writing it for you.
Upvote 0
Right had a look time to ask some questions,

Are you saying there will always be these fields, ~NAME~ADDRESS~CITY~STATE~ZIP, on each line in the same order? But there will only sometimes be these fields, ~ROUTING NUMBER~BANK NAME~ACCOUNT NUMBER?

Also once the import has happened do you want to save the file as a new workbook (if yes what file name?) or simply create a new worksheet?
Upvote 0
Regarding the fields, yes, some of the records there is nothing for the ~ROUTING NUMBER~BANK NAME~ACCOUNT NUMBER.

Once imported I want to save each workbook as a template and name it with the vendor info from B9.
Upvote 0
Ok give this a go. I've tested and as far as I can tell it works with no errors.

Open the file you want to create the templates from and press Alt + F11 to get into the VBA Editor.
Then go to Tools > References and find "Microsoft Scripting Runtime". Once found make sure to put a tick in it's box and then click OK.

Then Insert a new Module and paste the following code in there:
Option Explicit

Sub CreateVendorTemplatesFromTXT()
    Const txtPath   As String = "C:\Temp\TempTxt.txt"
    Dim strLine     As String
    Dim arrVend()   As String
    Dim ws          As Worksheet
    Dim fPath       As String
    Dim fName       As String
    Dim fExt        As String
    Dim fFull       As String
    Dim i           As Integer  'Loop Counter
    Set ws = ActiveSheet
    i = 1
    With ThisWorkbook
        fPath = .Path
        fName = Left(.Name, InStrRev(.Name, ".") - 1)
        fExt = Right(.Name, Len(.Name) - InStrRev(.Name, "."))
    End With
    With New FileSystemObject
        With .OpenTextFile(txtPath, ForReading)
                strLine = .ReadLine
                If strLine <> "" Then
                    arrVend = Split(Right(strLine, Len(strLine) - 1), "~")
                    ws.Range("B9").Value = arrVend(0)
                    ws.Range("B11").Value = arrVend(1)
                    ws.Range("B12").Value = arrVend(2)
                    ws.Range("B13").Value = arrVend(3)
                    ws.Range("B14").Value = arrVend(4)
                    If UBound(arrVend) + 1 > 5 Then
                        ws.Range("B17").Value = arrVend(5)
                        ws.Range("D17").Value = arrVend(6)
                        ws.Range("B19").Value = arrVend(7)
                        ws.Range("F1").Value = "CHECK ( ) WIRE ( ) ACH (X)"
                        ws.Range("B17").Value = ""
                        ws.Range("D17").Value = ""
                        ws.Range("B19").Value = ""
                        ws.Range("F1").Value = "CHECK (X) WIRE ( ) ACH ( )"
                    End If
                    fFull = fPath & "\" & fName & i & "." & fExt
                    Do While Dir(fFull) <> ""
                        i = i + 1
                        fFull = fPath & "\" & fName & i & "." & fExt
                    ThisWorkbook.SaveAs fFull
                End If
            Loop Until .AtEndOfStream
        End With
    End With
    MsgBox "All templates have been created"
End Sub

Upvote 0
First run went very well.

A couple of tweeks. The new workbooks are being saved with the name of orignal workbook and then adding a number:
  • VenderForm1
  • VenderForm2
  • VenderForm3

What is needed is for each workbook saved as the unique name of each vender.
  • ABC Company
  • Acme, Inc.
  • Best Services LLC

Also, is it possible to save them as a template .xlt file? I would like lockdown cells so that users can only edit certain fields and prevent them from overwriting the orginal file.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back