Script To Create Fixed Width Export Stub File

I'm building an export routine in SSIS and I was forced to create a template text file for the Flat File Destination. I'm sure there are more creative was of doing this, but I decided to go quick and dirty and opened my VBA editor and pounded out the following script. It will setup a fixed width (with row delimiter) column based stub for you to export to.

If you need to change the column widths' than do so by modifying the Array's.

Option Explicit

Sub MakeExportStub()
Dim intCnt As Integer ' create an iterator
Dim intInnerCnt As Integer
Dim intInsertChars As Integer
Dim strPrintLine As String
Dim strEndColWidth As String
Dim OutputFile ' create a long int to hold file pointer
Dim strFilePath As String ' create variable to hold file path info
Dim ROOT_PATH As String
Dim strTableName As String
Dim strColumnNames As Variant
Dim intColumnWidths As Variant

strColumnNames = Array("MASTERID", "FIRSTNAME", _
"LASTNAME", "ADDRESS1", _
"ADDRESS2", "ADDRESS3", "CITY", "ZIPPOSTALCODE")
intColumnWidths = Array(255, 200, 200, 255, 255, 255, 255, 20)

strTableName = "ExportStub"
ROOT_PATH = "C:\PATHTOYOURLOCALFOLDERS\"
strFilePath = ROOT_PATH & strTableName & ".txt" ' assign file path
OutputFile = FreeFile ' get the long for a free file

If Dir(strFilePath, vbNormal) <> "" Then Kill (strFilePath) ' if the filepath exists then kill the existing file
Open strFilePath For Output As #OutputFile ' open the output file


For intCnt = 0 To UBound(strColumnNames)
intInsertChars = CInt(intColumnWidths(intCnt)) - Len(CStr(strColumnNames(intCnt)))
For intInnerCnt = 0 To intInsertChars
strEndColWidth = strEndColWidth & " "
Next intInnerCnt
strPrintLine = strPrintLine & CStr(strColumnNames(intCnt)) & strEndColWidth
strEndColWidth = ""
Next intCnt

Print #OutputFile, strPrintLine & vbCrLf
Close #OutputFile ' THERE SHOULD BE ERROR TRAPPING TO ENSURE THIS IS EXECUTED!

End Sub

Over and Out