Consulting

Results 1 to 4 of 4

Thread: Excel to Text File

  1. #1
    VBAX Newbie
    Joined
    Nov 2018
    Posts
    2
    Location

    Excel to Text File

    I have a macro that handles most of what I need.

    I have data in Column A, in a spreadsheet, and I am exporting it to a text file. Currently the macro does that wonderfully. However, it is the format in the text file that is the issue. Here is some sample data:

    Column A (All Data in column A in excel worksheet)
    12345678901012018Bob Smith 35698
    12345678901012018Bob Smith 35698
    12345678901012018MikeSmith 43212
    12345678901012018Bob Johnson 35698
    12345678901012018Bob Jones 35698
    12345678901012018Bob Smith 35698
    12345678901012018Bob Smith 35698
    12345678901012018Bob Smith 35698

    Here is what is on the exported text file:
    12345678901012018Bob Smith 3569812345678901012018Bob Smith 3569812345678901012018MikeSmith 4321212345678901012018Bob Johnson 3569812345678901012018Bob Jones 3569812345678901012018Bob Smith 3569812345678901012018Bob Smith 3569812345678901012018Bob Smith 35698

    I need the data in the text file, exactly as it is in the excel worksheet.

    Here's the macro:

    Sub ExportToNotepad()
    Dim wsData As Variant
    Dim myFileName As String
    Dim FN As Integer
    Dim p As Integer, q As Integer
    Dim path As String
    Dim myString As String
    Dim lastrow As Long, lastcolumn As Long

    lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    path = "C:\Users\wbamford\Desktop"

    For p = 1 To lastcolumn
    wsData = ActiveSheet.Cells(1, p).Value
    If wsData = "" Then Exit Sub
    myFileName = wsData
    myFileName = myFileName & ".txt"
    myFileName = path & myFileName
    MsgBox myFileName
    For q = 2 To lastrow
    myString = myString & "" & Cells(q, p)

    FN = FreeFile
    Open myFileName For Output As #FN
    Print #FN, myString
    Close #FN
    Next q
    myString = ""
    Next p

    End Sub

  2. #2
    VBAX Regular
    Joined
    Oct 2018
    Posts
    18
    Location
    Hi bill

    just add vbCrLf to myString, it should fix the problem:
    myString = myString & "" & Cells(q, p) & vbCrLf
    good luck

  3. #3
    VBAX Newbie
    Joined
    Nov 2018
    Posts
    2
    Location

    Thumbs up

    Quote Originally Posted by Toubkal View Post
    Hi bill

    just add vbCrLf to myString, it should fix the problem:
    myString = myString & "" & Cells(q, p) & vbCrLf
    good luck
    Thanks! Worked.

  4. #4
    VBAX Regular
    Joined
    Oct 2018
    Posts
    18
    Location
    YW, please mark the thread as SOLVED.

Posting Permissions

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