VBScript read from relative path location

[hazel4832]

I have a script that reads information from an excel file and otputs the information to a text file. Other people will be using this script, so I just tell them to put the script and the input file in the same folder and it should run. How can I get the Script to find the excel file in the same folder that the script is in, but not necessarily the same location every time? I've tried
Set objWorkbook = objExcel1.Workbooks.Open ("/budget_input.xls")
Set objWorkbook = objExcel1.Workbooks.Open ("..\budget_input.xls")
Set objWorkbook = objExcel1.Workbooks.Open ("\budget_input.xls")
Set objWorkbook = objExcel1.Workbooks.Open
(dir &"\budget_input.xls")
Set objWorkbook = objExcel1.Workbooks.Open
(directory &"\budget_input.xls")
But I get the error that it cannot find the file every time.

 

[BillDoor]

Try this:

Set WshShell = WScript.CreateObject("WScript.Shell")
xlsFile = WshShell.CurrentDirectory & "\budget_input.xls

 

[PHV]

What about this ?
Set objWorkbook = objExcel1.Workbooks.Open(Left(WScript.ScriptFullName, InStrRev(WScript.ScriptFullName, "\")) & "budget_input.xls"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hazel4832]

Neither of those work. It's weird because if I put
Set objWorkbook = objExcel1.Workbooks.Open("C:\Documents and Settings\lrobinson\My Documents\Scripts\budget_input.xls") in the script it runs fine, but when I try the two solutions above I get the error
'C:\Documents and Settings\lrobinson\My Documents\Scripts\' could not be found.

 

[JPJeffery]

What do you get if you add in the following:

Msg = "The file path is"
strPath = Left(WScript.ScriptFullName, InStrRev(WScript.ScriptFullName, "\")) & "budget_input.xls"
strResult = InputBox(Msg,strTitle & " Path",strPath)

This should produce a windows dialogue with the full path of the file in a copy and paste-able form. If it does, does it look right? Can you browse to it or do a DIR in a command prompt with the text the dialogue provides?

My line of attack is that it's not dealing with spaces as it should. In which case try the code I suggested above like this:

Msg = "The file path is"
strPath = """" & Left(WScript.ScriptFullName, InStrRev(WScript.ScriptFullName, "\")) & "Filename.xls"""
strFirstName = InputBox(Msg,strTitle & " Path",strPath)

JJ
[small][purple]Variables won't. Constants aren't[/purple][/small]