How to transfer Excel data to Notepad and automatically create new individual text files.

Dear Sir,
I have a problem in creating a VBA to perform a certain task. I have a data in excel which has exactly 20 rows and 1500 columns, in which 1st row of each column is the filename of a text file which has to be created. How can I copy from A2 to A20 from the first row in excel and open and paste it in a notepad and name it as (A1 value) and close it. And then again copy the 2nd row from B2 to B20 and open and paste it in a notepad and name it as the (B1 value), so on and so forth for 1500 columns. How can I program this task other than manually copying from excel and pasting it in a particular notepad. Kindly guide me.

Warm Regards,
R. Sudhakar

So, Sudhakar wants to do the following:

1. Loop through his Excel data
2. Capture the the data from the top-most row and use it as a file name
3. Once the text file has been named he wishes to transfer the data in the rows below this header cell into his text file
4. Save the text files

Now doing this manually would make his hair grey. Let’s see how we can use VBA or macro code to find a solution quickly and easily. Before you get intimidated by the VBA code please have a look at the training video below (about 50 MB):

You can view this video on YouTube.

The complete VBA code:

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\takyar\Desktop\sudhakar\”

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

Further reading:

Free File Function

Open Notepad from VBA

Download sample file to your desktop for further study: