Excel is taking over my GetAbsolutePathName

[Quintios]

I have this code in Excel:

Option Explicit
Sub Run_VBS_File()
Dim WshShell As Object
    Set WshShell = CreateObject("Wscript.Shell")
    WshShell.Run "C:\xref\inventory.vbs"
End Sub

The VBS file has in the first few lines:

	Set oFSO = CreateObject("Scripting.FileSystemObject")
	sFullPath = oFSO.GetAbsolutePathName("inventory.vbs")
	MsgBox sFullPath

When the message box comes up, Excel's 'Default File Location' has taken over the actual path of the script file.

How can I fix this? I realize the other option is to convert the script from VBS to VBA but it uses Regular Expressions pretty heavily and I'm using Excel 97...

Thanks!



Onwards,

Q-

 

[PHV]

Try this:

WScript.Echo WScript.ScriptFullName 'Script name
WScript.Echo WScript.Path 'should be C:\xref

Hope This Help
PH.

 

[tsuji]

Hello Quintios,

[1] The problem here is that the GetAbsolutePathName method has no searching function at all. It only return a string with strict compliance of what it understands as a complete path info. Hence if you echo from where you call run the .vbs, say within the inventory.vbs you add a line like:

msgbox fso.GetAbsolutePathName(".&quot

I bet you will get your excel.exe's home path.

[2] Not only it has no searching function, it does not question the validity of the file (or directory) in its argument. Say, you do not have "abc" as subdirectory or file name, if you put

fso.GetAbsolutePathName("abc&quot

you probably get something like as the return

c:\program files\microsoft office\office\abc

[3] Hence you have to provide info on the absolute path of the inventory.vbs, ie "c:\xref\inventory.vbs". I know it is inconvenient because one day you might decide to change the file to some other place. Then, you have no choice but to either provision a searching function.

[4] As an essential improvement, vbscript 5.6 have currentdirectory read-write property for the wscript.shell. For instance, if you add before the lines

Set WshShell=CreateObject("WScript.Shell&quot
WshShell.CurrentDirectory="c:\xref"
Set WshShell=Nothing

Then your GetAbsolutePathName will return the desired string. But, the ironic thing is that you have to provide the info c:\xref one way or the other.

regards - tsuji