Creating a formatted text file with vb

[cjaye]

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]

Try something like this:

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]

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!