Consulting

Results 1 to 7 of 7

Thread: Solved: Open notepad, find and replace text with the filename

  1. #1

    Solved: Open notepad, find and replace text with the filename

    I need a program to do the following:
    1. open a file in in notepad (in the current folder, or better yet it can get my input as to where it's located.)
    2. find the words "0test" in the file and replace it with the filename,
    A. Except for the .txt at the end,
    example if the filename is 112233.txt,
    I want to find 0test and replace it with 112233
    3. save the file

    I've got a small start with help from this thread:
    http://www.vbaexpress.com/forum/showthread.php?t=13737

    all I did with this code is to cut of the cell link at the end

    [vba]
    Sub MikeTextFileExample()
    Dim vFF As Long, vFile As String, tempStr As String

    'output filename, we can choose this programmatically if desired
    vFile = "C:\your file name.txt"

    'get a string to add to the file, just for this example
    tempStr = InputBox("Please enter string to add to file", "Enter text")

    'get an unused file number (for vba to refer to it)
    vFF = FreeFile

    'open the file so we can write to it
    Open vFile For Append As #vFF

    'add the line to the file
    Print #vFF, tempStr

    'close the reference to the file
    Close #vFF
    End Sub
    [/vba]

    In my case I need to change this to get the file name in stead of using an input box:
    [vba]
    'get a string to add to the file, just for this example
    tempStr = InputBox("Please enter string to add to file", "Enter text")

    [/vba]

    Any help is appreciated,

    Thanks,
    Rolly

  2. #2
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    Hi rolly,

    Place this code in a module. Then run Main.

    What this does is allows you to select a *.txt file, it opens said file, looks for "0test", then replaces it with the name of the file without the ".txt" then it writes the file back to the same location with the corrections. I did not open notepad as it wasn't neccessary, if you want to open it anyway let me know and I will open it and have it open the file.


    [vba]Private Declare Function GetOpenFileName Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" (pOpenfilename As OpenFileName) As Long
    Private Type OpenFileName
    lStructSize As Long
    hWndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
    End Type
    'Purpose : Allows the user to select a file name from a local or network directory.
    'Inputs : sInitDir The initial directory of the file dialog.
    ' sFileFilters A file filter string, with the following format:
    ' eg. "Excel Files;*.xls|Text Files;*.txt|Word Files;*.doc"
    ' [sTitle] The dialog title
    ' [lParentHwnd] The handle to the parent dialog that is calling this function.
    'Outputs : Returns the selected path and file name or a zero length string if the user pressed cancel

    Function BrowseForFile(sInitDir As String, Optional ByVal sFileFilters As String, _
    Optional sTitle As String = "Open File", Optional lParentHwnd As Long) As String
    Dim tFileBrowse As OpenFileName
    Const clMaxLen As Long = 254

    tFileBrowse.lStructSize = Len(tFileBrowse)

    'Replace friendly deliminators with nulls
    sFileFilters = Replace(sFileFilters, "|", vbNullChar)
    sFileFilters = Replace(sFileFilters, ";", vbNullChar)
    If Right$(sFileFilters, 1) <> vbNullChar Then
    'Add final delimiter
    sFileFilters = sFileFilters & vbNullChar
    End If

    'Select a filter
    tFileBrowse.lpstrFilter = "Text Files (*.TXT)" & vbNullChar & "*.TXT" & vbNullChar
    'create a buffer for the file
    tFileBrowse.lpstrFile = String(clMaxLen, " ")
    'set the maximum length of a returned file
    tFileBrowse.nMaxFile = clMaxLen + 1
    'Create a buffer for the file title
    tFileBrowse.lpstrFileTitle = Space$(clMaxLen)
    'Set the maximum length of a returned file title
    tFileBrowse.nMaxFileTitle = clMaxLen + 1
    'Set the initial directory
    tFileBrowse.lpstrInitialDir = sInitDir
    'Set the parent handle
    tFileBrowse.hWndOwner = lParentHwnd
    'Set the title
    tFileBrowse.lpstrTitle = sTitle

    'No flags
    tFileBrowse.flags = 0
    'Show the dialog
    If GetOpenFileName(tFileBrowse) Then
    BrowseForFile = Trim$(tFileBrowse.lpstrFile)
    If Right$(BrowseForFile, 1) = vbNullChar Then
    'Remove trailing null
    BrowseForFile = Left$(BrowseForFile, Len(BrowseForFile) - 1)
    End If
    End If
    End Function
    Sub Main()
    Dim mFleNm As String, Fle As Long, mInfo As String, mLongFle As Long
    Dim mFileName As String
    mFleNm = BrowseForFile("C:\")
    Fle = FreeFile
    mFileName = Right(mFleNm, Len(mFleNm) - InStrRev(mFleNm, "\"))
    mFileName = Left(mFileName, Len(mFileName) - 4)
    Open mFleNm For Input As #Fle
    mLongFle = LOF(Fle)
    mInfo = Input(mLongFle, #Fle)
    Close (Fle)
    mInfo = Replace(mInfo, "0test", mFileName)
    Fle = FreeFile
    Open mFleNm For Binary As #Fle
    Put #Fle, , mInfo
    Close #Fle
    End Sub
    [/vba]

    If you need explainations or further help do not hesitate to ask.

    EDIT: I forgot to fix for readability. (no scrolling to the right)

  3. #3

    Smile Great

    That's great, exactly what I was looking for.

    Now I'd like to make one more request:
    After it opens the file I'd like it to delete the last line of the file which is always the following:
    )

    then copy the following in the last line (it will copy this same thing everytime also):
    "
    Z:\0test\0test.EVT)"

    After copying the above line, then it will continue with the find/replace function to replace 0test with the filename

    Thanks for your help, I spent most of half a day scouring websites and tutorials, still unable to find anything of use. I really appreciate it.

    Rolly

  4. #4
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    So you want to replace the last
    ")"
    with
    "Z:\0test\0test.EVT)"
    [VBA]Sub Main()
    Dim mFleNm As String, Fle As Long, mInfo As String, mLongFle As Long
    Dim mFileName As String
    mFleNm = BrowseForFile("C:\")
    Fle = FreeFile
    mFileName = Right(mFleNm, Len(mFleNm) - InStrRev(mFleNm, "\"))
    mFileName = Left(mFileName, Len(mFileName) - 4)
    Open mFleNm For Input As #Fle
    mLongFle = LOF(Fle)
    mInfo = Input(mLongFle, #Fle)
    Close (Fle)
    mInfo = Replace(mInfo, "0test", mFileName)
    mInfo = Left(mInfo, Len(mInfo) - 2)
    'if the file has one line too many delete one of the "vbCrLf &"
    mInfo = mInfo & vbCrLf & vbCrLf & "Z:\0test\0test.EVT)"
    Fle = FreeFile
    Open mFleNm For Binary As #Fle
    Put #Fle, , mInfo
    Close #Fle
    End Sub[/VBA]

  5. #5
    Thanks for your help, works exactly as needed. I appreciate you help and this forum.

    Rolly

  6. #6
    VBAX Newbie
    Joined
    Jul 2011
    Posts
    1
    Location
    Hi,

    Can anyone tell me how to change the above code so that multiple files can be selected (and have text replaced in) at the same time?

    Thanks!

  7. #7
    Hi I need similar VBA code for find and replace if more then 2 comma symbol in csv file.

    1) i have to open CSv file using open with notepad
    2) find if comma symbol is more then 2 then remove after 2 commas
    3) find space underscore / Underscore space then replace with only underscore

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •