How to get the location of a .vbs file?

I have a vb script which performs certian manipulations to an excel file and creates an xml file. Right now, the input and output directories are hard coated. Can I get the location of the source file and then manipulate it for the output and input directory paths?
ReedsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mvidasCommented:
Hi Reeds,

Are you looking for the path of the VBS file or the excel file?

Based on the question title, use the following to get the VBS path
Matt
 Dim vPath
 vPath =  Left(WScript.ScriptFullName, Len(WScript.ScriptFullName) - Len(WScript.ScriptName))
 MsgBox vPath

Open in new window

0
ReedsAuthor Commented:
This works fine, thx. Yes, I am looking for the input excel file which would be in the same directory and create a new file again in the same directory. I thought that the best way would be to find the directory of the source script and then concatenate it with the file name. Any suggestion would be appreciated
0
mvidasCommented:
How is the file named, or how are you getting it?

Do you click/drag the excel file "into" the .vbs file? If so, you can use WScript.Arguments(0) to refer to the "sent" file.

Otherwise, I'll need a little more information on your process and what exactly you want to accomplish in order to help.
Matt
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

ReedsAuthor Commented:
The excel file is of the name abcd_ddmmyyhhmmss (processed once every day). I am planning to keep my script in the same directory as the excel file, pick up the excel file for that date and create a new file in the same directory for that date.
0
ReedsAuthor Commented:
Hey mvidas, thanks so much. I did not even know that I can just drag and drop the file into the script. It works perfectly. Thanks again.
0
mvidasCommented:
Happy to help! Once you use the wscript.arguments method, you can actually put the .vbs file into your    C:\Documents and Settings\<username>\SendTo     folder, and then just right-click the excel file, go to "Send To...", and select your vbs file to do the same processing. That way you don't even have to keep the vbs file in that directory if you don't want it.  Just a thought
Let me know if you need anything else
Matt
0
ReedsAuthor Commented:
Hi mvidas, I am really sorry for so many questions. If I want to call my script from the macro of an excel file. e.g. Filename below is the name of the input excel file and I want to pass this to the script, can I do that?
ActiveWorkbook.SaveAs Filename:="abcd_" & Format(ws2.Range("Date"), "yyyymmddhhmmss")& ".csv", _
    FileFormat:=xlCSV, CreateBackup:=False

tahnks.
0
mvidasCommented:
You can call the script from an excel macro, and if you really want to I will show you how. Before I do, I have to ask; why not just incorporate whatever is in the vbs file into the excel macro ?
0
ReedsAuthor Commented:
Won't I need to change the whole syntax, for e.g. variables need not be declared in vb script, the syntax would be different etc. That's why I thought that if I can call the script from macro and pass the file name as argument
0
mvidasCommented:
You wont NEED to change any of it (with the exception of "WScript.Arguments" as WScript isn't defined in VBA) though the rest of it will work (and likely faster). You don't need to declare variables in VBA either (unless you have Option Explicit in your module declarations) but its a better idea as far as memory is concerned.  Everything else would be the same; vbscript is a lesser version of VB designed to run in a specific way (as is VBA, though VBA has access to more than vbs does).

I just suggested moving it into VBA since you're already using VBA, why involve VBS? I'd highly recommend keeping it all in VBA if you're gonna be using vba (vbscript is great for things like scheduling or if you dont want to use another programming environment).

But, if you really want to know how to run it, I'll attach a snippet for you. You have to first call "wscript.exe", then the vbsfile, then any arguments (if any). wscript.exe is what is called when you "run" a .vbs file. WScript is "windows scripting" when using vbscript syntax, there is also jscript.exe (java) and cscript.exe (c) scripting as well (FYI--likely too much info :))
 'since you dont like to declare variables, you can leave this out'
 Dim XLSFile As String, VBSFile As String
 
 'CurDir just returns the current directory, since youre not specifying the path'
 ' the VBS file will need the path'
 XLSFile = CurDir & "abcd_" & Format(ws2.Range("Date"), "yyyymmddhhmmss") & ".csv"
 
 VBSFile = "C:\vbscript file.vbs"
 
 ActiveWorkbook.SaveAs Filename:=XLSFile, FileFormat:=xlCSV, CreateBackup:=False
 
 'I put the quotes around the vbs and xls file paths in case there are spaces'
 Shell "wscript.exe """ & VBSFile & """ """ & XLSFile & """"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.