vbscript to replace a text with a windows environment variable

use the following:

Text1 = Environ("USERNAME")

Hi sblanken,

In vbscript, you reference environment variables by using the following:

Set objShell = CreateObject("WScript.Shell")
strUserName = objShell.ExpandEnvironmentStrings("%USERNAME%")
strLogonServer = objShell.ExpandEnvironmentStrings("%LOGONSERVER%")

or any other environment variable..  So to use that in your script, see the attached snippet.

Regards,

Rob.

Const ForReading = 1
Const ForWriting = 2
Set objShell = CreateObject("WScript.Shell")
 
' Set your variables here
strTextFile = "C:\text.txt"
strUserName = objShell.ExpandEnvironmentStrings("%USERNAME%")
strReplaceWith = "text2"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strTextFile, ForReading)
 
strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, strUserName, strReplaceWith)
 
Set objFile = objFSO.OpenTextFile(strTextFile, ForWriting)
objFile.WriteLine strNewText
objFile.Close

Select allOpen in new window

Rob, your script works great. One more question: if I want to modify 6 variables on the fly, how should I do it? I tried multiplying your script, but it wrote the text back 6 times, each time modifying the needed variable, so it didn't modify all 6 at a time, but 6 times each variable. I assume my approach was naive. I appreciate your help.

Const ForReading = 1
Const ForWriting = 2
Set objShell = CreateObject("WScript.Shell")
 
' Set your variables here
strTextFile = "C:\IDEConfig.pm"
strInstanceName = objShell.ExpandEnvironmentStrings("%INSTANCE%")
strReplaceInstanceFrom = "INSTANCE"
 
strUserName = objShell.ExpandEnvironmentStrings("%USER%")
strReplaceUserFrom = "USER"
 
strWksName = objShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
strReplaceWksFrom = "COMPUTERNAME"
 
strOracleName = objShell.ExpandEnvironmentStrings("%ORACLE%")
strReplaceOracleFrom = "ORACLE"
 
strP4PathName = objShell.ExpandEnvironmentStrings("%P4PATH%")
strReplaceP4PathFrom = "P4PATH"
 
strP4ServerName = objShell.ExpandEnvironmentStrings("%P4SERVER%")
strReplaceP4ServerFrom = "P4SERVER"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strTextFile, ForReading)
 
strText = objFile.ReadAll
objFile.Close
strNewTextInstance = Replace(strText, strReplaceInstanceFrom, strInstanceName)
strNewTextUser = Replace(strText, strReplaceUserFrom, strUserName)
strNewTextWks = Replace(strText, strReplaceWksFrom, strWksName)
strNewTextOracle = Replace(strText, strReplaceOracleFrom, strOracleName)
strNewTextP4Path = Replace(strText, strReplaceP4PathFrom, strP4PathName)
strNewTextP4Server = Replace(strText, strReplaceP4ServerFrom, strP4ServerName)
 
Set objFile = objFSO.OpenTextFile(strTextFile, ForWriting)
objFile.WriteLine strNewTextInstance
objFile.WriteLine strNewTextUser
objFile.WriteLine strNewTextWks
objFile.WriteLine strNewTextOracle
objFile.WriteLine strNewTextP4Path
objFile.WriteLine strNewTextP4Server
objFile.Close

Select allOpen in new window

Hi, we'll try things a little differently.....with the replacement, you should be able to just use the Replace function straight on the strText string, replacing what you need with what you want.

Back up your original IDEConfig.pm file, and try this code.  If you have trouble understanding it, let me know.

Regards,

Rob.

Const ForReading = 1
Const ForWriting = 2
Set objShell = CreateObject("WScript.Shell")
 
' Get the text out of the file
strTextFile = "C:\IDEConfig.pm"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strTextFile, ForReading)
strText = objFile.ReadAll
objFile.Close
Set objFile = Nothing
 
' Set your variables here in the array to replace multiple values.
' The first element of each line is the Environment variable
' value to replace, and the second element will be the text
' to replace that with.  Elements are separated by the semi-solon
' and the last line must not have the comma on the end.
arrReplacements = Array(_
	objShell.ExpandEnvironmentStrings("%INSTANCE%") & ";INSTANCE",_
	objShell.ExpandEnvironmentStrings("%USER%") & ";USER",_
	objShell.ExpandEnvironmentStrings("%COMPUTER%") & ";COMPUTER",_
	objShell.ExpandEnvironmentStrings("%ORACLE%") & ";ORACLE",_
	objShell.ExpandEnvironmentStrings("%P4PATH%") & ";P4PATH",_
	objShell.ExpandEnvironmentStrings("%P4SERVER%") & ";P4SERVER"_
	)
 
' Now go through the array to replace each value in the strText
For Each strReplacement In arrReplacements
	strReplaceWhat = Split(strReplacement, ";")(0)
	strReplaceWith = Split(strReplacement, ";")(1)
	strText = Replace(strText, strReplaceWhat, strReplaceWith)
Next
 
Set objFile = objFSO.OpenTextFile(strTextFile, ForWriting)
objFile.Write strText
objFile.Close
Set objFile = Nothing
Set objFSO = Nothing
Set objShell = Nothing

Select allOpen in new window

Hi Rob, thanks very much for your reply. I tried the new script but for some reason nothing happened. That is the file didn't get update at all, except the timestamp, so I know the script read and write the file, but it didn't find something? Anyways, this is not too important now as since this morning I have now 6 vbs scripts that I run in a batch file, and works fine, except is not elegant like your solution. I have another question now: I want to modify a SQL script and when I run your original script for only one term replacement, the output is giberish and I don't understand why. I'll attach the files now: the script that I use, the original SQL script, and the output. Thanks very much for your help.

Script I use:
 
Const ForReading = 1
Const ForWriting = 2
Set objShell = CreateObject("WScript.Shell")
 
' Set your variables here
strTextFile = "C:\DB\REPL\ConfigureDistribution.sql"
 
strProjName = objShell.ExpandEnvironmentStrings("%PROJ%")
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strTextFile, ForReading)
 
strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, "PROJ", strProjName)
 
Set objFile = objFSO.OpenTextFile(strTextFile, ForWriting)
objFile.WriteLine strNewText
objFile.Close
 
 
Original SQL script
 
use master
exec sp_adddistributor @distributor = N'WKSNAME\DR_PROJ', @password = N'SAPASS'
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'E:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data', @log_folder = N'E:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
 
use [distribution] 
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) 
	create table UIProperties(id int) 
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) 
	EXEC sp_updateextendedproperty N'SnapshotFolder', N'E:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties' 
else 
	EXEC sp_addextendedproperty N'SnapshotFolder', 'E:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
GO
 
exec sp_adddistpublisher @publisher = N'WKSNAME\DR_PROJ', @distribution_db = N'distribution', @security_mode = 0, @login = N'sa', @password = N'SAPASS', @working_directory = N'E:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
 
The command that I run
 
@C:\DB\Repl\Vbs\proj.vbs C:\DB\Repl\ConfigureDistribution.sql
 
 
Output after I run the vbs script agains the sql script
 
Alot of uuuuuuuuuuuuuuuuuuuuuuuuuuuuuu
followed by some understandable text
publisher = N'WKSNAME\DR_PROJ', @distribution_db = N'distribution', @security_mode = 0, @login = N'sa', @password = N'SAPASS', @working_directory = N'E:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

Select allOpen in new window

Rob,

The SQL script works fine now as Unicode. Thanks a million. I also ran the echo command on the other script and I know now why it didn't change anything. Probably because I wasn't explicit, you thought that I want to replace a variable with a constant (string), but it was the other way around. I have a script with 6 constants and I want to run it on several different machines, and these machine should update the script based on their own environment variables. That is on machine 1 DBINSTANCE (the constant from the script) will become ideesx01, and on machine 2 DBINSTANCE will be ideesx05, on machine 3 it will be ideora12 etc., on machine 1 the webserver is web01, on machine 2 is web02, etc. The scripts work great and you helped me a lot. The script that changes more strings on the fly is very elegant and I will definitely want to use it, so let me know if you can update it, if it's not too much hassle, you've already been very helpful. Thank you and I appreciate all your help. I'll give you the points now along with my gratitude. I'll call it a day for now, I worked on my project the entire day, and thanks to you I'm almost done. Thanks and have a good night.

Update:

Rob, I figured it out and now I have a fully functional script. I want to thank you for all your help. This is really very helpful. Thanks and have a nice day.

Thanks for the update......glad you got it working....just to clarify, with the comments I added above the array, you should have been able to just swap the constant with the variable on each line in the array, so that it used different sides of the semi-colon for the strReplaceWhat and strReplaceWith values.

This includes that, and also the intUniCode adjustment....

Regards,

Rob.

Const intUnicode = -1
Const ForReading = 1
Const ForWriting = 2
Set objShell = CreateObject("WScript.Shell")
 
' Get the text out of the file
strTextFile = "C:\IDEConfig.pm"
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.OpenTextFile(strTextFile, ForReading, False, intUnicode)
strText = objFile.ReadAll
objFile.Close
Set objFile = Nothing
 
' Set your variables here in the array to replace multiple values.
' The first element of each line is the Environment variable
' value to replace, and the second element will be the text
' to replace that with.  Elements are separated by the semi-solon
' and the last line must not have the comma on the end.
arrReplacements = Array(_
	"INSTANCE;" & objShell.ExpandEnvironmentStrings("%INSTANCE%"),_
	"USER;" & objShell.ExpandEnvironmentStrings("%USER%"),_
	"COMPUTER;" & objShell.ExpandEnvironmentStrings("%COMPUTER%"),_
	"ORACLE;" & objShell.ExpandEnvironmentStrings("%ORACLE%"),_
	"P4PATH;" & objShell.ExpandEnvironmentStrings("%P4PATH%"),_
	"P4SERVER;" & objShell.ExpandEnvironmentStrings("%P4SERVER%")_
	)
 
' Now go through the array to replace each value in the strText
For Each strReplacement In arrReplacements
	strReplaceWhat = Split(strReplacement, ";")(0)
	strReplaceWith = Split(strReplacement, ";")(1)
	strText = Replace(strText, strReplaceWhat, strReplaceWith)
Next
 
objFSO.OpenTextFile(strTextFile, ForWriting, True, intUnicode)
objFile.Write strText
objFile.Close
Set objFile = Nothing
Set objFSO = Nothing
Set objShell = Nothing

Select allOpen in new window