replace text between single quotes in a text file

[supmktg]

I'm trying to replace a password in a text file that changes from time to time. I need to find the current password so I can use the replace function to change it. The text file looks something like this:

$Site = 'https://mysite.com'
$User = 'someuser@mysite.com'
$Password = 'currentpassword1'

My experience with manipulating text files in vba is limited. I'd appreciate any help I can get.

Thanks,
Sup

 

[arnelgp]

you must set a Reference in VBE in Microsoft Scripting Runtime:


Dim objFSO As Scripting.FileSystemObject
Dim objText As Scripting.TextStream
Dim strContent As String

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objText = objFSO.OpenTextFile("e:\my.txt", ForReading)
strContent = objText.ReadAll
objText.Close
strContent = Replace(strContent, "currentpassword1", "newpassword2")

Set objText = objFSO.OpenTextFile("e:\my.txt", forWriting)

objText.Write strContent
objText.Close
Set objFSO = Nothing

 

[Gasman]

Arnelgp,

I believe the actual password will be unknown each time?, so I would be looking to find the password line and then find the single quote characters (Instr & InstrRev) and build the new line with left and midstr functions.

Is there a tidier way if that is the case.?

 

[arnelgp]

dim strPassWord As String
const consPass As String = "$Password = '"
dim nPos As Integer

nPos = InStr(strContent, consPass)
' this is the current password
strPassWord = Mid(strContent, nPos + Len(consPass))
strPassWord = Left(strPassWord, InstrRev(strPassWord, "'")-1)

'replace
strContent = Replace(strContent, strPassword, strNewPassword)

 

[supmktg]

arnelgp,

Between you 2 pieces of code I was able to accomplish what I was trying to do!

Thank you very much,
Sup