VBA - loop through text files then find & replace

[HotLanta]

Hi,

I wanted to see if someone could help with this project. I have many text files that I am always manually editing.


I would like to have a macro that will loop through a folder on the desktop named "Text Files".
In the folder will be any number of text files. (.txt)
I want the code to find a certain piece of information and then use that to replace another piece of information.
Then save the file and loop through the rest.

The text files look like this:

FILNAM/'SAMPLE TEST PROTOTYPE'
DATE =2022/10/14
TIME =11:00:43

PR(1)='98765432'
LI(2)='EFG'
PL(3)='TUVWXY'
PN(4)='COMPANY'
PS(5)='12345678-001'
Q(6)='ABCD'

I would like for the PS(5) line to be found and then the first 8 characters copied that are in single quotes.

Then find the FILENAM/ line and replace whatever is between the single quotes with what is copied in the clipboard.

So the final result will look like this:

FILNAM/'12345678'
DATE =2022/10/14
TIME =11:00:43

PR(1)='98765432'
LI(2)='EFG'
PL(3)='TUVWXY'
PN(4)='COMPANY'
PS(5)='12345678-001'
Q(6)='ABCD'

Then loop through the rest.

Is this possible?

 

[Automatrix]

Maybe more of a job for Power Automate than for VBA?

 

[HotLanta]

I wasn't sure what Power Automate was, so I had to look it up. It seems much bigger than this project and I am sure my company won't go for it.

 

[Automatrix]

I'm no VBA expert, but here's something I pulled off Google.

The VBA Guide To Interacting With Text (.Txt) Files