Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run VBS from VBA?
I've looked in this forum and others, but unfurtunately can't find an answer
as to why my code is not running, although I suspect it may have to do with the path... Here's the code that runs as part of a larger routine: ChDir _ "\\Server01\Directory1\Directory WithSpaceinName\Directory withSpaces and Ampersand inName\FinalDirectory" Dim Retval Retval = Shell("WScript.exe MoveReports.vbs") I can run "MoveReports.vbs" with no problem. It simply takes a number of reports from their default location and moves them to various archive directories. Unfortunately, I do not have control over the names of folders on the network (such as those with multiple spaces and control charecters in their names...) and fear that may be at least part of my issue. When I run the code, I get an error that states, "Can not find script file C:\Documents and Settings\my.name\MyDocuments\MoveReports.vbs" which is NOT the location of nor is it specified in my code! What am I doing wrong?! Thanks, Ray |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run VBS from VBA?
Hi Ray,
The problem may be due to the directory you are trying to locate the VBS file being on a different server. I am not totally sure as I am unable to test on a different server right now. However, you can try putting the path to the script inside the Shell command, that way the current directory is irrelevant: Retval = Shell("WScript.exe " & Chr(34) & "\\Server01\Directory1\Directory WithSpaceinName\Directory withSpaces and Ampersand inName\FinalDirectory\MoveReports.vbs" & Chr(34)) Including the Chr(34) will send the path to Wscript in quotes, so spaces will be accepted. Doing it this way means you know that Wscript is getting the path you want, rather than relying on the Path Excel is using. If you still get errors then I would try to run the script from a command prompt so that you can see what Shell requires. I hope this helps, Sean. -- (please remember to click yes if replies you receive are helpful to you) "RayportingMonkey" wrote: I've looked in this forum and others, but unfurtunately can't find an answer as to why my code is not running, although I suspect it may have to do with the path... Here's the code that runs as part of a larger routine: ChDir _ "\\Server01\Directory1\Directory WithSpaceinName\Directory withSpaces and Ampersand inName\FinalDirectory" Dim Retval Retval = Shell("WScript.exe MoveReports.vbs") I can run "MoveReports.vbs" with no problem. It simply takes a number of reports from their default location and moves them to various archive directories. Unfortunately, I do not have control over the names of folders on the network (such as those with multiple spaces and control charecters in their names...) and fear that may be at least part of my issue. When I run the code, I get an error that states, "Can not find script file C:\Documents and Settings\my.name\MyDocuments\MoveReports.vbs" which is NOT the location of nor is it specified in my code! What am I doing wrong?! Thanks, Ray |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hi Ray,The problem may be due to the directory you are trying tolocate the
Hi,
I tried using the following code and it worked with spaces Dim Path As String Path = "C:\installer automation\test1.vbs" Shell ("wscript.exe " & Chr(34) & Path & Chr(34)) On Monday, October 29, 2007 3:50 PM RayportingMonke wrote: I've looked in this forum and others, but unfurtunately can't find an answer as to why my code is not running, although I suspect it may have to do with the path... Here's the code that runs as part of a larger routine: ChDir _ "\\Server01\Directory1\Directory WithSpaceinName\Directory withSpaces and Ampersand inName\FinalDirectory" Dim Retval Retval = Shell("WScript.exe MoveReports.vbs") I can run "MoveReports.vbs" with no problem. It simply takes a number of reports from their default location and moves them to various archive directories. Unfortunately, I do not have control over the names of folders on the network (such as those with multiple spaces and control charecters in their names...) and fear that may be at least part of my issue. When I run the code, I get an error that states, "Can not find script file C:\Documents and Settings\my.name\MyDocuments\MoveReports.vbs" which is NOT the location of nor is it specified in my code! What am I doing wrong?! Thanks, Ray On Monday, October 29, 2007 4:56 PM SeanCU wrote: Hi Ray, The problem may be due to the directory you are trying to locate the VBS file being on a different server. I am not totally sure as I am unable to test on a different server right now. However, you can try putting the path to the script inside the Shell command, that way the current directory is irrelevant: Retval = Shell("WScript.exe " & Chr(34) & "\\Server01\Directory1\Directory WithSpaceinName\Directory withSpaces and Ampersand inName\FinalDirectory\MoveReports.vbs" & Chr(34)) Including the Chr(34) will send the path to Wscript in quotes, so spaces will be accepted. Doing it this way means you know that Wscript is getting the path you want, rather than relying on the Path Excel is using. If you still get errors then I would try to run the script from a command prompt so that you can see what Shell requires. I hope this helps, Sean. -- (please remember to click yes if replies you receive are helpful to you) "RayportingMonkey" wrote: Submitted via EggHeadCafe Microsoft Silverlight For Beginners http://www.eggheadcafe.com/training-...lverlight.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|