Replace function using varaibles

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,908
Office Version
  1. 2013
Platform
  1. Windows
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

Code:
' 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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
What's the full code, in particular what's the value of NME?
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,908
Office Version
  1. 2013
Platform
  1. Windows
The value of NME is the string for the new filename as in the post above: "TestUserAccount00005"
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019

ADVERTISEMENT

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

Well-known Member
Joined
Jul 9, 2009
Messages
1,908
Office Version
  1. 2013
Platform
  1. Windows
That was missed on the 'cut'.
I checked the file it has NME written in it instead of its value.
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,155

ADVERTISEMENT

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

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,155
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,908
Office Version
  1. 2013
Platform
  1. Windows
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.

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

strNewText = Replace(strText, OrigFileNme, NME)
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,908
Office Version
  1. 2013
Platform
  1. Windows
All working now. Thank you both for your responses.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,706
Messages
5,833,234
Members
430,198
Latest member
KitaYama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top