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
