Editing Notepad file from VBA

mserfling

New Member
Joined
Nov 6, 2020
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
Hello, I am trying to edit a open notepad file from VBA. What I need to happen is deleting certain strings of text. "QTY. 1 EACH SIZE: .75 X 1.375" & "Multiline Text" & "Singleline Text" <-- If any of these shows up in the text file I need it gone and replaced with nothing. Any help will be appreciated.






VBA Code:
Private Sub CreateText_Click()
    Dim myCell
    Dim i As Integer
    Dim Str As String
    Dim LastRow As Variant
    Dim JobNum As String
    Dim ClearTxt As String
    
    On Error Resume Next
    
        JobNum = Right([B3], 6) '<----------------------------------- Selects just the number for the job
        MkDir "C:\Trotec\Laser Jobs\JobNumbers\" & JobNum '<--------- Creates a folder named with the job number

        
        
        
    Range("A1:A150").Select '<--------------------------------------- Specified cells with blanks will be deleted
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete '<----- Deletes rows with condition
    'MichaelSerfling





    LastRow = Cells(Rows.Count, "A").End(xlUp).Row '<---------------- Sweeps through until Str is seen
    ClearTxt = "QTY. 1 EACH SIZE: .75 X 1.375" & "Multiline Text"
    Str = "QTY. 1 EACH SIZE: .75 X 1.375" '<------------------------- Specify term

    For i = 1 To LastRow Step 1 '<-----------------------------------
       If Sheet1.Range("A" & i).Value = Str Then '<------------------ Looks for the phrase set above
          Sheet1.Range("A" & i + 7).Resize(LastRow, 4).Copy '<------- Offsets by 7 to avoid certain rows
          Shell "notepad.exe", vbNormalFocus '<---------------------- Opens Notepad
          SendKeys "^V" '<------------------------------------------- Paste the range
          
          Exit Sub
       End If
    Next i

End Sub
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Is the use of notepad strictly required? If not it might be worth to consider, to load the contents of your file directly into memory, do some parsing / replacing and write the resulting content back to disk.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,236
Messages
5,576,892
Members
412,752
Latest member
LUIS SAMANO
Top