apply VBS to file in current directory

vbscript run exe from current directory
vbscript current directory
wscript scriptfullname
vbscript change directory command prompt
vbscript get current folder name
vbscript to copy file from current directory

I have some VBS code that I use to apply Excel formatting to a spreadsheet:

Set xlObj = CreateObject("Excel.Application")
Set xlFile = xlObj.WorkBooks.Open("C:\Documents and Settings\user\forms.xlsx")
xlObj.Application.DisplayAlerts = False
For Each Worksheet In xlFile.Worksheets
  dPriorWorkday = xlObj.Application.WorksheetFunction.WorkDay(Now, -1)
  Worksheet.Name = "Forms received " & Year(dPriorWorkday) & "-" & Right("0" & Month(dPriorWorkday),2) & "-" & Right("0" & Day(dPriorWorkday),2)
With Worksheet.Cells.Font
   .Name = "Arial"
   .Size = 8
End With
Next 
xlFile.Close True
xlObj.Quit

I use a .bat file to run this VBS on a .xlsx worksheet, with all 3 files being in the same directory.

What I am trying to do is change the VBS to apply the formatting to a .xlsx file that is in the same directory as the .bat and the .vbs file, so I can remove the direct file path "C:\Documents and Settings\user\"

I have tried:

Workbooks.Open Filename:=ThisWorkbook.Path & "\forms.xlsx"

but it does not work, I assume because that is for .vbs called from the workbook itself.

is there something similar I can use to apply the .vbs to the file in the same directory as the .vbs, without using the full file path?


What I am trying to do is change the VBS to apply the formatting to a .xlsx file that is in the same directory as the .bat and the .vbs file, so I can remove the direct file path "C:\Documents and Settings\user\"

If all the files are in the same directory then use this

Set WshShell = CreateObject("WScript.Shell")
strCurDir = WshShell.CurrentDirectory

Set xlObj = CreateObject("Excel.Application")
Set xlFile = xlObj.Workbooks.Open(strCurDir & "\forms.xlsx")

More about CurrentDirectoy HERE

EDIT:

Ekkehard.Horner's reply is more apt and can handle situations when the vba is called from a different directory. In that case the CurrentDirectoy obviously won't give the expected path.

Getting current directory in VBScript, You can use WScript.ScriptFullName which will return the full path of the executing script. You can then use string manipulation (jscript  set objShell = WScript.CreateObject ("WScript.Shell") WScript.Echo (objShell.CurrentDirectory) “There are many terrorist states in the world, but the United States is unusual in that it is officially committed to international terrorism, and on a scale that puts it's rivals to shame” ~ Noam Chomsky . Related: Equivalent Windows command: CD - Change Directory - move to a specific Folder


If you want to pick "the same directory as the .bat and the .vbs file" reliably (regardless of the folder you call the script from or (accidential) changes to .CurrentDirectory), you should use WScript.ScriptFullName:

Option Explicit
Dim goFS : Set goFS = CreateObject("Scripting.FileSystemObject")
Dim goWS : Set goWS = CreateObject("WScript.Shell")
Wscript.Echo "CD", goWS.CurrentDirectory
Wscript.Echo "FS", goFS.GetAbsolutePathName(".")
goWS.CurrentDirectory = "c:\temp"
Wscript.Echo "CD", goWS.CurrentDirectory
Wscript.Echo "FS", goFS.GetAbsolutePathName(".")
Wscript.Echo "SD", goFS.GetParentFolderName(WScript.ScriptFullName)

output:

DNV35 C:\Documents and Settings\eh
cscript 31.vbs
CD C:\Documents and Settings\eh
FS C:\Documents and Settings\eh
CD c:\temp
FS C:\Temp
SD C:\Documents and Settings\eh

DNV35 C:\Documents and Settings\eh
cd ..

DNV35 C:\Documents and Settings
cscript eh\31.vbs
CD C:\Documents and Settings
FS C:\Documents and Settings
CD c:\temp
FS C:\Temp
SD C:\Documents and Settings\eh

open file in current dir - VBScript, HI, I have the following code inside my HTML file that opens a zip file in a given dir: objShell.Run( %ProgramFiles%\WinZip\winzip32.exe -e -o  VBScript & Batch Files No Comments Here is another odd problem I had to tackle and which VBScripting was up to the task of easily handling. I had the need of changing the extension of all the files in a folder from *.wdseml to *.eml.


My solution, which always works for me. I store the current path into variable called cur, then append it to the name of my excel:

Set objShell = CreateObject("WScript.Shell")
Dim cur
cur = objShell.CurrentDirectory
WScript.Echo cur

ExcelMacroExample

Sub ExcelMacroExample() 

Dim xlApp 
Dim xlBook 
Dim xlsFile
xlsFile = cur & "\myExcel.xlsm"

Set xlApp = CreateObject("Excel.Application") 
Set xlBook = xlApp.Workbooks.Open(xlsFile) 
xlApp.Run "sheetname"
xlApp.Save
xlApp.Quit 

End Sub

How Can I Change the Working Folder of a Script?, My script needs to have the same working folder as the application that Wscript​.Echo objShell.CurrentDirectory. As you might expect, in the  Sometimes you might require to get a list of all the files in a folder, I was thinking how it can be implemented, the solution is to used the WSH interface and write a script in VBScript. Below is a sample script which will get the list of files on a directory, say c:\windows and save it to the file c:\windows\FileList.txt.


Run file from current directory - Software Development, Run file from current directory - posted in Software Development: HI i'm new to programming, i have the Shell") Set WinScriptHost = WScript. If you don’t fully understand what’s happening here, don’t worry about it; it takes awhile to fully grasp how recursive functions work. In the meantime, just copy the preceding script and use it any time you need to do something to all the files in a folder and its subfolders.


How to get the current path using VBScript, To get the current path using VBScript you can use the snippet below. Samples. VBScript Sample. VBA code to loop through files in a folder (and sub folders) Listing filenames in a worksheet, printing all the files to PDF, or making changes to every file in a folder, there are many reasons why we may want to loop through each file in a folder. VBA provides us with a few ways to achieve it (1) Dir function (2) File System Object.


VBScript Current Directory (or Folder), Since my post on VBScript command line parameters is by far the most popular, here's some useful script we use to get the current directory  VB Script to Copy File from Current Folder to %Appdata% fiqa. I need to copy an ini file to %appdata% the msi will be instaled on the system accound.