Syntax for running a .vbs with arguments

[patstarks]

Hello,
I have a .vbs file called Replace.vbs that:

1. opens a text file, test.txt
2. finds a string, orig_name
3. replaces string with new_name

The file runs and works fine if I go to Start-->run-->cmd

and type

cscript replace.vbs ".\test.txt" "orig_name" "new_name"

However when I go try to run this using the shell command from Excel VB it does not work. I tried with the following code.

Sub TrailFile_Editer()
ChDir "C:\Documents and Settings\motaza\My Documents\ProEBats"
Shell cscript replace.vbs ".\test.txt" "orig_name" "new_name"
End

I tried a variation using & and commas but no luck.
Does someone the vb shell command syntax for passing arguments through a .vbs file?

Note Replace.vbs and test.txt are in the same directory.

thanks,
Radwan

[cguan_77]

hi patstarks, try to put your excel file which has the vba plus the vbs file and the text file in same directory. don't forget to change the path of your shell command...
and one more thing copy cscript.exe to that directory also in which your files are placed together
check if it will help.. :)

[patstarks]

Thanks for the response.
Ok so I am going to try the same thing from home and I am using this directory
C:\Documents and Settings\tops\My Documents
In this directory I put the

1. Replace.vbs
2. test.txt (text file that I want to perform the find and replace)

I probably should have stated the error that I am getting as it is:

"Compile Error: Argument not Optional"

for this code.

Sub TextEditor_1()
ChDir "C:\Documents and Settings\tops\My Documents"
Shell cscript(Replace.vbs)
End Sub

So it does recognize the Replace.vbs but it knows that it needs three arguments:

1. name of text file
2. find string
3. replace string

However I can't seem to figure out the syntax as I have used many combinations of quotations, commas, etc.

[cguan_77]

hi, try something like this..
copy all your files to this directory C:\Documents and Settings\tops\My Documents
copy also cscript to that directory..no need to include chdir in your code

try this:

Dim PID

PID = Shell(" C:\Documents and Settings\tops\My Documents\cscript replace.vbs", 1)

[patstarks]

Ok,
I am not sure what is meant when you say copy cscript to that directory.
I ran a search for cscript in my Progam Files directory but did not find anything.

Because now when I try to run it, it gives me an error saying

Run time error: File not found.

Pat

[patstarks]

ok actually i found cscript in the windows\system32 directory and copied it to my documents. However I am still getting the same
File not found error.

[cguan_77]

However when I go try to run this using the shell command from Excel VB it does not work. hi, can post your code in excel vba and your code in vbs that you have right now...

what you want right now is what, run the code in excel vba or just run it using cscript only? :)

[patstarks]

Here are the contents of the Replace.vbs file. I pasted the contents into notepad and then saved as a vbs file.
This script finds a string and replaces it with another string for text files.

Const ForReading = 1
Const ForWriting = 2

strFileName = Wscript.Arguments(0)
strOldText = Wscript.Arguments(1)
strNewText = Wscript.Arguments(2)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFileName, ForReading)

strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, strOldText, strNewText)

Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)
objFile.WriteLine strNewText
objFile.Close

Then from the command line I type:
cscript "replace.vbs" "test.txt" "orig_name" "new_name"

And the text file, test.txt, is changed as I would like it to be.

Since I would like to do this to many files and incorporate it into an existing for loop that I have written in VBA. Ultimately I would like to vary the input arguments for the replace.vbs shell command. In other words,
for i = 1
replace orig_name with new_name_1
then i = 2
replace orig_name with new_name_2
then i = 3
replace orig_name with new_name_3
.....

new_name_1, new_name_2, new_name_3 would be in cells of my excel file that the VBA code would use as input arguments as text replacement strings.

So in short it runs fine from the cmd line but I need to automate the process from excel VBA as I need to do this operation numerous times.
Here is the link of the website that I found the replace.vbs routine:
http://www.microsoft.com/technet/scriptcenter/resources/qanda/feb05/hey0208.mspx

Pat Starks

[cguan_77]

hi, don't know what you're trying to achieve..rename the file name or what? because the replace vbs that you have it will open the file and add contents to that file.. it also stated on the link where you get that vbs..
anyway, here's a vba code to get cells value in excel..maybe it can give you an idea to get started.. :)
data should be placed in column 1 and rows 1 to 10
the vba code works fine..but the shell command i had put i never tested it

Sub x()
Dim New_Name As String
Dim i As Integer

For i = 1 To 10
New_Name = Cells(i, 1).Value 'get the value from cells
msgbox x 'just for demo to show cells value
'then to call your vbs you can do something like this
'Shell(" C:\Documents and Settings\tops\My Documents\cscript replace.vbs test.txt orig_name  New_Name", 1)
Next i

End Sub

Goodluck!