Replace Text from Excel Sheet to Notepad

In this post we will show how to modify data in the Notepad from the Excel spreadsheet. We will write one macro here to do our job. I am sure you must have done manual copy-paste from excel to notepad many times. This macro will not only save your time but also make sure that there is no error that you could do manually.

There is an excel sheet with the two columns, Employee ID and Employee Name. We also have a notepad file which has only the Employee ID of the employee.

Our task is to replace the Employee Id in the notepad with their corresponding Employee Name. The Employee Name needs to be picked from the excel file.

Our Macro will one by one go through each of the Employee Id in the excel sheet and search it in the notepad file, if Employee Id is found in the notepad it will be replaced by the Employee Name which is present right next to the Employee Id

Backup of the Notepad file

As a safety measure a backup of the Notepad file is created and saved in a folder with the name Backup_ExcelSheetFileName. The Backup Notepad file will have Backup_ Prefixed to original name and each time the Macro is run the old Backup file will be over written.

How to use this Macro

1. Lets assume the name of your excel sheet is replace_text_notepad.xls and the name of your notepad file is replace_text_notepad.txt

2. The data in the Excel sheet and Notepad file is same as shown in the examples attached. Of course your data could be different.

NOTE: The important point to note here is that the Employee Id should be present in the first column starting from Cell A2 and the corresponding Employee Name should be present in second column starting from Cell B2.

3. Press ALT + F11 to open the Visual Basic Editor

4. Create a Module and copy-past the below code.

5. Again come back to the Excel sheet and press ALT + F8 and run the Macro Macro_Replace_From_Excel_To_Notepad

6. Enter the Notepad file name. In our case the name is replace_text_notepad.txt

Click OK to Run the Macro. The Notepad file should be present in the same folder where the excel sheet is located.

If you enter wrong File name here an error is thrown.

7. Now the task has been executed successfully and the Notepad File is also processed. You can open the Notepad and see the changes.

8. After the execution of the Macro you can see that a new folder named replace_text_notepad.xls (Folder name is same as the Excel Sheet name with .xls extension) has been created which has the backup of the original Notepad file with the name Backup_replace_text_notepad.txt

Sub Macro_Replace_From_Excel_To_Notepad()
 
Dim NotepadPath, NotepadFileName As String
 
''Take the Text file name from the user
NotepadFileName = InputBox("Enter the File Name of the Notepad file. File should be " & _
"present in the same folder (For example: FileName.txt)")
 
If NotepadFileName = vbNullString Then
    Exit Sub
End If
 
NotepadPath = ActiveWorkbook.path & "\" & NotepadFileName
 
' This for loop should only be called only if the Notepad file is found
If Not Len(Dir(NotepadPath)) = 0 Then
' Backup folder is created is not already created
Dim BackupPath As Variant
BackupPath = ActiveWorkbook.path & "\" & "Backup_" & ActiveWorkbook.Name
 
    If Not Len(Dir(BackupPath, vbDirectory)) = 0 Then
        'Do nothing
    Else
 
         MkDir (BackupPath)
    End If
 
' Text is file is copied to the Backup folder
FileCopy NotepadPath, BackupPath & "\" & "Backup_" & NotepadFileName
 
        'Open the Text file in Notepad
        value = Shell("C:\windows\system32\notepad.exe " & NotepadPath, vbNormalFocus)
        ' space after \notepad.exe_ and " is imp
        ActiveSheet.Select ' Select the Sheet
        Range("a2").Select
        For Each cell In Range(Selection, Selection.End(xlDown))
 
            value = OpenReplace(cell.value, cell.Offset(0, 1).value)
            ' Call OpenReplace function
        Next cell
 
        SendKeys "^s" 'CTRL + s (Save)
        SendKeys "%{F4}~" 'ALT + F4 (Close)
 
Else
MsgBox "File not found"
 
End If
Range("a2").Select
Range(Selection, Selection.End(xlDown)).Select
 
End Sub
 
 
 
Function OpenReplace(vText As Variant, vReplace As Variant)
' This function will open the notepad file and perform
' replace all function by sending keyboard actions
    SendKeys "^h" 'CTRL + H (Replace)
    SendKeys vText 'SHIFT + 2 (")
    SendKeys "{TAB}"
    SendKeys vReplace
    SendKeys "%a" 'ALT + A (Replace All)
    SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}" 'TAB x6 (to get to Cancel button)
    SendKeys "~" 'ENTER (press on Cancel button)
 
End Function

Have fun!