How to Automate Transfer of Excel data to Notepad


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:

11 thoughts on “How to Automate Transfer of Excel data to Notepad

  1. Park

    Hello sir,

    I would like to develop one automation tool using macro to minimize of manual work. Please give me a solution for the below question.

    Question :

    I need four input text fields like name, age, address and phone number and one command button. I have to update all the four text fileds and have to clck the command button. Once its done all the given informations should update in a .dat formated notepad files.

    Reply
    1. Thomas Berg

      Hello sir

      I need to transfer Excel data to multiple csv files in the same folder.
      Filenames should be in column A.
      Row 1 = 1 csv file with A1 as filename
      Row 2 = next csv file with A2 as filename
      and so on.
      I got 22 colums, and over 400 rows.
      Some of the cells in each row has blank, need them as blank in csv file.

      Every csv file will then moved to a folder and uploaded to another program.

      Please help me.

      Reply
  2. Thomas Berg

    Hello sir

    I need to transfer Excel data to multiple csv files in the same folder.
    Filenames should be in column A.
    Row 1 = 1 csv file with A1 as filename
    Row 2 = next csv file with A2 as filename
    and so on.
    I got 22 colums, and over 400 rows.
    Some of the cells in each row has blank, need them as blank in csv file.

    Every csv file will then moved to a folder and uploaded to another program.

    Please help me

    Reply
  3. Edwin Hughes

    Dinesh,
    I find your tutorial very helpful.
    I would like to go a step further with it.
    I want to allow the user to pick the destination folder that will receive the exported text files.
    Here is code that I found on the web:

    Function GetFolder(strPath As String) As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
    .Title = “Select a Folder”
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
    End With
    NextCode:
    GetFolder = sItem
    Set fldr = Nothing
    End Function

    Can I use this code in your subroutine somehow to prompt the user for the destination folder?
    Your guidance is much appreciated!
    -Edwin

    Reply
  4. Serhii

    Hello,

    I need to transfer data from Excel file to notepad using any code.
    I need to create a simple text which will consist of exact cells.
    For example I my result text should appear:
    “A5
    AO16
    P13
    R16
    B9 C3
    P3”

    ,where instead of A5, AO16…..should be data from these cells.
    Is it possible to create such code?

    P.S
    1)Excel file is located on Google Drive
    2)Excel file has a lot of tabs, so I should mention in code the number of this tab
    3)Result can be in any text form, so I will have possibility to copy it.

    Reply
  5. kapil

    Hello sir,

    I want to transfer data from excel to notepad, and for that your video help me.
    But i want each row data in new line in notepad.
    so, please help me.
    I don’t have any knowledge of scripting.

    Reply
  6. sathishkumar

    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. also first column of the sheet is static and must be concatenated to the each column and save to notepad. How can I copy from B2 to B20 from the first row in excel, concatenated with A2 to A20 simultaneously and open and paste it in a notepad and name it as (B1 value) and close it. And then again copy the 2nd row from C2 to C20,concatenated with A2 to A20 simultaneously and and open and paste it in a notepad and name it as the (C1 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.

    Reply
  7. Ramesh

    Hello

    Thanks for your valuable post, I would like to add some fixed string in some format with data available in Excel sheet. Can you help me here?

    For Ex: I have 2 coulmns like Fruits and Count For Ex Apple in first column 250 in second column
    I want a notepad file like

    Apple
    250

    Reply
  8. nitin matkar

    Hi sir,
    i have a same task to do as Sudhakar having. i want to transfer data from excel to notepad. But with same extra things to do . I have data from A1:A5000.
    1) I want to automatically seperate this data to 10 columns having 1 to 500 data in first column 2 to 500 in next column ,and so on.
    2) Then I want transfer these column to notepad file.
    3) Data in the notepad transfered from excel to notepad should not be inlined, it should be like a column in notepad

    Reply
  9. Waseem Tahir

    Hi Sir,

    Thanks very much for this code it helps me very much.

    But Sir, i have some problem with the text file when the files saved with urdu text it’s data not displayed properly.

    Sir, my question is that how to save the notepad file in unicode format.

    Wait for your promt reply..

    Thanks

    Waseem Tahir

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *