Replace function using varaibles

[daverunt]

Hi all,

I am trying to use the Replace function (with FileSystemObject code) with a variable but it just sees adds the variable name and not the content.

NME is just a string "TestUserAccount00005"

But 'NME' appears in the written file not 'TestUserAccount00005'

Any ideas

' Open the file and read
Set objFile = fs.OpenTextFile(f, ForReading)
        strText = objFile.ReadAll
        objFile.Close

strNewText = Replace(strText, "TestUserAccount - 10-07-09 115823", NME)

Write the new file
Set objFile = fs.OpenTextFile(f, ForWriting)
        objFile.WriteLine strNewText
        objFile.Close

 

[Special-K99]

What's the full code, in particular what's the value of NME?

 

[daverunt]

The value of NME is the string for the new filename as in the post above: "TestUserAccount00005"

 

[Special-K99]

Oh yep, sorry.

 

[Special-K99]

Just spotted there's no apostrophe before

Write the new file

Cant see why that would cause the problem but insert apostrophe and see if it solves the problem.

 

[daverunt]

That was missed on the 'cut'.
I checked the file it has NME written in it instead of its value.

 

[GTO]

What does "f" represent? Could you show the whole procedure?

 

[GTO]

Off to bed, but in a quick test, this seems to work for me...

With the text file originally containing:

Mary had a little lamb its fleece was white as snow;
And everywhere that Mary went, the lamb was sure to go.
It followed her to school one day, which was against the rule;
It made the children laugh and play, to see a lamb at school.
And so the teacher turned it out TestUserAccount - 10-07-09 115823, but still it lingered near,
And waited patiently about till Mary did appear.
"Why does the lamb love Mary so?" the eager children cry;
"Why, Mary loves the lamb, you know" the teacher did reply.

In a Standard Module:

(Early bound...)
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Sub</SPAN> example()<br><SPAN style="color:#00007F">Dim</SPAN> fs <SPAN style="color:#00007F">As</SPAN> Scripting.FileSystemObject<br><SPAN style="color:#00007F">Dim</SPAN> objFile <SPAN style="color:#00007F">As</SPAN> Scripting.TextStream<br><SPAN style="color:#00007F">Dim</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> strText <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>  <br><SPAN style="color:#00007F">Const</SPAN> NME = "TestUserAccount00005"<br>  <br>  f = ThisWorkbook.Path & "\New Text Document - Copy.txt"<br>  <SPAN style="color:#00007F">Set</SPAN> fs = CreateObject("Scripting.FileSystemObject")<br>  <SPAN style="color:#00007F">Set</SPAN> objFile = fs.OpenTextFile(f, ForReading)<br>  strText = objFile.ReadAll<br>  objFile.Close<br>  <br>  strText = Replace(strText, "TestUserAccount - 10-07-09 115823", NME)<br>  <br>  <SPAN style="color:#00007F">Set</SPAN> objFile = fs.OpenTextFile(f, ForWriting)<br>  objFile.Write strText<br>  objFile.Close<br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

...results in the text file now containing:

Mary had a little lamb its fleece was white as snow;
And everywhere that Mary went, the lamb was sure to go.
It followed her to school one day, which was against the rule;
It made the children laugh and play, to see a lamb at school.
And so the teacher turned it out TestUserAccount00005, but still it lingered near,
And waited patiently about till Mary did appear.
"Why does the lamb love Mary so?" the eager children cry;
"Why, Mary loves the lamb, you know" the teacher did reply.

Mark

 

[daverunt]

Hi GTO,

thanks for that.
f is the file and path.

I have changed the Replace function to use variables for both values and it appears to be working ok.
I will try adding the constant and see if it works for me.

OrigFileNme="TestUserAccount - 10-07-09 115823", 

strNewText = Replace(strText, OrigFileNme, NME)

 

[daverunt]

All working now. Thank you both for your responses.