[RESOLVED] Write, run VBS within VBA macro?

[Ed_from_AZ]

(Not sure this is exactly the right forum; if not, a nudge in the right direction is appreciated.)

Wondering if there's a better way to do this:

In Word 2007 VBA (running on Vista Pro), I wrote a macro that wrote a VB Script file and then executed it. It worked fine. I used the following code:

Sub TestWriteScript()

myFileName = "C:\Documents and Settings\user.name\Documents\TestMe.vbs"
myText = "MsgBox " & Chr(34) & "Got it!" & Chr(34)
bolFileGood = WriteFile(myFileName, myText)

Set wshShell = CreateObject("WScript.Shell")
wshShell.Run ("cscript ""C:\Documents and Settings\user.name\Documents\TestMe.vbs""")
End Sub

Public Function WriteFile(ByVal myFileName As String, ByVal myText As String) As Boolean
On Error GoTo OhWell
    hFile = FreeFile
    Open myFileName For Output As #hFile
    Print #hFile, myText;
    Close #hFile
OhWell:
    WriteFile = Not CBool(Err.Number)
    On Error GoTo 0
End Function

(Note: this was tagged on to another procedure; all variables not Dim'd in the above were declared at the module level.)

I ran into problems at first with the file path in the wshShell.Run line because of the spaces in "Documents and Settings". Then I found out I needed the pairs of double quotes. And so it works.

I also tried setting all the file path and doubled quotes into a string, but couldn't figure that one out?? If I'm going to be able to use this in a distributed code, I'll need to pull the username for the file path and create the string. Or perhaps set an object to the VBS file and run that?

So is there a way to use Run or another method to execute the VB Script from within a macro using a string or object when the file path to the script has spaces in it?

Ed

[MarkT]

Not sure I understand what all you are asking. To get the path of the user directory you can do something like this.

Const Quote = """"
Dim objNetworkSet 
Dim strUserName, strFilePath

	Set objNetwork = CreateObject("WScript.Network")
	strUserName = objNetwork.UserName
	Set objNetwork = Nothing
	
	strFilePath = "C:\Documents and Settings\" & strUserName & "\Documents\TestMe.vbs"
	
	MsgBox Quote & strFilePath & Quote

As for the vbscript, is there a reason that can't be added to the VBA code instead of calling an external file?

[Ed_from_AZ]

Hi, Mark. Thanks for the reply.

The part I really had in question was:

wshShell.Run ("cscript ""C:\Documents and Settings\user.name\Documents\TestMe.vbs""")

When I tried to sustitue a variable for any part of what's insode the parentheses, it wouldn't run.

Ed

PS - For the "why", it's because I need the application to close. So I write the script and execute it from the VBA. The script does what it needs to do which requires the app to close, then reopens the app and calls a different macro. At least, that's the concept .....

[MarkT]

Just winging it here but does something like this work

strVBS = Chr(34) & "C:\Documents and Settings\user.name\Documents\TestMe.vbs" & Chr(34)
wshShell.Run ("cscript " & strVBS)

[Ed_from_AZ]

Dang it!! I coulda swore it didn't work yesterday when it was _my_ idea!! And that's why I was asking!!

Sure enough, though, it fired right off 'n makes me feel stoopud! 8>{

Thanks, Mark. Guess it just needed a kick in the right place! (Kinda like fixing an old TV, y'know?)

Ed

[MarkT]

I have had many of those moments.