Need VBScript to import BAS file to Excel with relative directory or alternative

Question

I have a VBA module and a VBScript that imports this module into a specific Excel Workbook. The problem I'm facing is when using a different machine, the path to the module will change. Is there a way to package the BAS file with the Script file? Or to have the BAS within the actual VBScript file?

Ideally the final product would be a single file, it is possible to send both the Vbscript and the BAS file together (possibly zipped) but I would prefer a one-file deliverable. Unfortunately converting the VBA module to VBS is not really an option, as I specifically need some of the VBA functionality.

Current functional but not ideal code:

'Target Excel file to import BAS file to

Filepath = "C:\targetExcelFile.xlsx"

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(filepath)

objExcel.Visible = True

'Imports BAS module, but using a filepath

objExcel.VBE.ActiveVBProject.VBComponents.Import pathToBASfile

objExcel.Run "MySub"

Alex Marin Alex Marin · Accepted Answer · 2019-08-30T19:25:14

Is the VBS file always placed near the xlxs? If so you can do something like this:

'Target Excel file to import BAS file to
Set Fso = WScript.CreateObject("Scripting.FileSystemObject")
ParentFolder  = FSO.GetParentFolderName(WScript.ScriptFullName)
sParentFolder = ParentFolder & "\"

Filepath = sParentFolder & "targetExcelFile.xlsx"

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(filepath)

objExcel.Visible = True

'Imports BAS module, but using a filepath

objExcel.VBE.ActiveVBProject.VBComponents.Import pathToBASfile

objExcel.Run "MySub"

This gets the current folder of where the script is executed, and if the file is present near it, it will always run.

Need VBScript to import BAS file to Excel with relative directory or alternative

1 Answers