Replace function using varaibles

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,887
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,887
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,350

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,887
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,154

ADVERTISEMENT

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

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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,887
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,887
Office Version
  1. 2013
Platform
  1. Windows
All working now. Thank you both for your responses.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,951
Members
414,351
Latest member
james27

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