apply VBS to file in current directory

Question

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?

Siddharth Rout · Accepted Answer · 2013-11-28T11:59:56.493

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.

@kmfdm: You might want to reconsider accepting a more appropriate answer :) — Siddharth Rout, Nov 28 '13 at 10:02

score 3 · Answer 2 · answered Nov 27 '13 at 18:40

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

+1 for a) understanding what the OP states that he wants and b) for digging out the FSO calls required to get the correct paths. Even though I hate +1ing 14k'ers ;) — TheBlastOne, Nov 28 '13 at 09:13

score 0 · Answer 3 · edited Apr 27 '18 at 18:03

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

apply VBS to file in current directory

3 Answers3

Linked