CODEWITHSUNDEEP
excelvbscriptvba
Ben
Ben

Reputation: 35

Relative paths for VBScript file

I have a VBScript file to open an Excel file and run a macro. This Excel file is located in the same folder as the VBScript file. I would like to use relative paths to call it, so that I can move that folder around without rewriting the paths in the script. Right now my VBScript looks like this:

    Option Explicit

    On Error Resume Next

    ExcelMacroExample

    Sub ExcelMacroExample() 

      Dim xlApp 
      Dim xlBook

      Set xlApp = CreateObject("Excel.Application") 
      Set xlBook = xlApp.Workbooks.Open("C:\Users\Ben\Desktop\GeocodingBatchFile\Files\GeocodingStart.xlsm") 
      xlApp.Run "Export"
      xlApp.Quit 

      Set xlBook = Nothing 
      Set xlApp = Nothing 

    End Sub

Rather than using the full file path, it would be great if I could do something like this: 

    Set xlBook = xlApp.Workbooks.Open(".\GeocodingStart.xlsm")

Upvotes: 0

Views: 5245

Answers (2)

Siva
Siva

Reputation: 1149

Try this...

   Dim oFSO
  Set oFSO = CreateObject("Scripting.FileSystemObject")
  sScriptDir =     oFSO.GetParentFolderName(WScript.ScriptFullName)               
   Set xlBook = xlApp.Workbooks.Open( sScriptDir &  "\GeocodingStart.xlsm")

Upvotes: 3

chris neilsen
chris neilsen

Reputation: 53166

You can use the FileSystemObject to get the path to the script file, like this

Sub ExcelMacroExample()

   Dim xlApp
   Dim xlBook
   Dim fso
   Set fso = CreateObject("Scripting.FileSystemObject")
   Dim fullpath
   fullpath = fso.GetAbsolutePathName(".")
   Set fso = Nothing
   Set xlApp = CreateObject("Excel.Application")
   Set xlBook = xlApp.Workbooks.Open(fullpath & "\GeocodingStart.xlsm")
   xlApp.Run "Export"
   xlApp.Quit

   Set xlBook = Nothing
   Set xlApp = Nothing

End Sub

Upvotes: 0

Related Questions