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

[rolly]

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



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


In my case I need to change this to get the file name in stead of using an input box:

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



Any help is appreciated,

Thanks,
Rolly

[Tommy]

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.


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


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

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

[rolly]

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

[Tommy]

So you want to replace the last
")"
with
"Z:\0test\0test.EVT)"
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

[rolly]

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

Rolly

[Wezzo]

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!

[gourishjoshi]

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