Create multiple workbook templates from delimited text file.

[jryker]

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:
~NAME~ADDRESS~CITY~STATE~ZIP~ROUTING NUMBER~BANK NAME~ACCOUNT NUMBER


Help, way in over my head here.

 

[AD_Taylor]

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
If there's no solution up by the time I look at it tomorrow I'll have a go at writing it for you.

 

[AD_Taylor]

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?

 

[jryker]

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.

 

[AD_Taylor]

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)
            Do
                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)"
                    Else
                        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
                    Loop
                    
                    ThisWorkbook.SaveAs fFull
                End If
            Loop Until .AtEndOfStream
        End With
    End With
    
    MsgBox "All templates have been created"
End Sub

HTH!

 

[jryker]

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.