Creating a formatted text file with vb

cjaye · May 7, 2013

Hello all,

I am trying to write vb code within an existing macro, that writes to and replaces a simple text file named benefit.txt that will have ^ for the field qualifier and , for the field deliminator. There seems to be a lot of info out there but nothing I found pertains to my needs and I'm not even sure where to start. I found an Excel Add-in that worked pretty good but not perfect and I really wanted to automate this as much as possible.

^239309^,^Doe, John K^,^17.16^
^239290^,^Banion, Michael P^,^7.41^
^293982^,^Brickland, Eric J^,^17.60^
^239352^,^Carlton, Pam E^,^183.70^

The data would be from the Code (General formatting), Employee (General formatting), & Owed Employee (Accounting formatting) columns from an Excel 2003 tab called Summary.
The data always starts on row 6 and number of rows can change each time the macro is run. The Owed Employee cells have a formula that uses defined names. Attached is a sample spreadsheet.

Any help or direction would be greatly appreciated. I'm not very good with vb and am really spinning my wheels on this.

JoePublic · May 8, 2013

Try something like this:

Code:

Sub testwrite()
   MakeTextFile "C:\test\blah.txt"
End Sub

Sub MakeTextFile(sPath As String)
   Dim data
   Dim lLastRow As Long
   Dim x As Long
   Dim y As Long
   Dim lFileNum As Long
   Dim sTemp As String
   With Sheets("Sheet1")
      lLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
      data = Application.Index(.Range("A6:E" & lLastRow), .Evaluate("ROW(1:" & lLastRow - 5 & ")"), Array(2, 1, 5))
   End With
   For x = LBound(data, 1) To UBound(data, 1)
      For y = LBound(data, 2) To UBound(data, 2) - 1
         sTemp = sTemp & "^" & data(x, y) & "^,"
      Next y
      sTemp = sTemp & "^" & data(x, y) & "^" & vbCrLf
   Next x
   
   lFileNum = FreeFile
   Open sPath For Output As #lFileNum
   Print #lFileNum, sTemp
   Close #lFileNum
End Sub

cjaye · May 9, 2013

JoePublic, your code worked great! It was easy to follow the flow of it and why it worked and mine failed. My biggest struggle was with the qualifier ^ and deliminator , and your method was simple and easy. Thank you for your time and expertise!

Creating a formatted text file with vb

cjaye

New member

Attachments

JoePublic

Super Moderator

cjaye

New member