Results 1 to 3 of 3

Thread: Creating a formatted text file with vb

  1. #1

    Creating a formatted text file with vb

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Excel Version
    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
    This website wants to know your momentum - | Deny | | Allow |

  3. #3
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts