CODEWITHSUNDEEP
excelbatch-filevbscript
kmfdm
kmfdm

Reputation: 79

apply VBS to file in 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?

Upvotes: 1

Views: 7255

Answers (3)

Rahul k
Rahul k

Reputation: 69

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

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

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.

Upvotes: 4

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38745

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

Upvotes: 3

Related Questions