Help with script to change text file

Hi everyone, I am still very much a novice with scripts but I have just worked on a very simple one to change the line in a text file.

This works perfectly for what I am trying to do, however I have to write the exact file name every time I want it to change the words, which is obviously slower than me going in and altering it by hand which is what I do now.

I am running this from excel by the way using a button.

Firstly I would like to know if what I want is possible. Secondly what I want is for me to be able to click a button in excel and it will make the required change to all the dmo files in a single folder without me having to change the file name.

So if I say this is the file path "S:\DMO Files from CMM" and it will search the folder and make the changes to all the files. Is this possible?

Basically I have no idea how to go about this at the moment so any help would be great.

Sub Button1_Click()

Const ForReading = 1

Const ForWriting = 2


Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("S:\DMO Files from CMM\LH Anodised Silver 02.02.2018 - 1.dmo", ForReading)


strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, "FILNAM/'31862383.dmo'", "FILNAM/'31862383'")


Set objFile = objFSO.OpenTextFile("S:\DMO Files from CMM\LH Anodised Silver 02.02.2018 - 1.dmo", ForWriting)

objFile.WriteLine strNewText

objFile.Close


End Sub

vpt.se over 6 years ago

Try the suggested solution here:

https://social.technet.microsoft.com/Forums/exchange/en-US/ef89306a-e153-4a0b-abf6-8c89c153e5d3/replace-text-in-multiple-files?forum=ITCG
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
AlliumPorrum over 6 years ago
I think this is what you're asking for, but written in Python since I suck at VB.
Be very careful if you run this. It will run the replace function on every file in the srcfolder as well as in any subfolders.
I always make a backup of the folder before I attempt any mass changes like this.

import os srcfolder = r"S:\DMO Files from CMM" for root,dirs,files in os.walk(srcfolder): for filename in files: with open(os.path.join(root, filename), "r+") as file: filecontents = file.read().replace("FILNAM/'31862383.dmo'","FILNAM/'31862383'") file.seek(0) file.truncate() file.write(filecontents)

To run it:
Download and install Python 3.6.4 from here (Make sure you check the "Add Python 3.6 to PATH" checkbox when you install it.) I think you have to restart as well.
Save the above code to a text file with a '.py' extension instead of '.txt'
Double click it.
Profit.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel

NinjaBadger over 6 years ago

Something like this?


Sub Button1_Click()

Const ForReading = 1

Const ForWriting = 2

'declare the object as a the type it is, this gives intelisence (predictive text) in the VB environment
'makes find out what properties and methods exist for an object so much easier

Dim objFSO As Scripting.FileSystemObject

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objDir = objFSO.GetFolder("C:\temp\test folder")

Dim objFile As Scripting.File

For Each objFile In objDir.Files

Dim ts As Scripting.TextStream

Set ts = objFile.OpenAsTextStream(ForReading, TristateUseDefault)

strText = ts.ReadAll

strNewText = Replace(strText, "hello", "goodbye")

Set ts = objFile.OpenAsTextStream(ForWriting, TristateUseDefault)

ts.Write strNewText


Next objFile



End Sub

bigtallanddopey over 6 years ago

I have tried both your suggestions vpt.se which comes up with an error Invalid inside procedure and NinjaBadger which comes up with a user-defined type not defined error.

Maybe something I have done wrong or maybe it just cannot be executed from excel.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
NinjaBadger over 6 years ago

I tested mine in Excel on a couple of text files containing 'Hello World'.

Oh, you may need to add a reference for mine to work due to using early binding.

In the VBA environment within Excel click Tool> References and then tick Microsoft Scripting Runtime
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
bigtallanddopey over 6 years ago

OK, I have both working now.

Thanks NinjaBadger that got your script working perfectly.

And on the one posted by Vpt.se I had to delete the Line "Option Explicit" and it worked perfectly after that.

Two different methods which achieved the same thing, no wonder I'm struggling to learn this ha.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
NinjaBadger over 6 years ago

There's usually numerous ways to accomplish something in coding - it's just about finding the one that works best (or easiest) in each instance.

Once you've figured out a particular technique for doing something however, (i.e. navigate files and folders / read write from a text file / connect and read-write to a database etc) you have a reference to go back to and copy.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
InspectorJester over 6 years ago

I will steal this and attempt to make good use of each.
+1's all around
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
InspectorJester over 6 years ago in reply to AlliumPorrum

Interesting approach!
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel