path Variable captured using a batch file needs to also be utilized in Excel VBS to open excel file.
Posted on 2016-07-18
Here is a snipit of the batch file code which captures the directory and file name. I had help on this before but wish to take it a little further.
Set Varname=Name of file.xlsx
for /f "delims=" %%a in ('dir /b /a:d /o:n "%Dir%\WR*"') do set LatestDir=%Dir%\%%a
The above works great.. I now have a xlsm file which runs a macro when a 2nd xlsm file is opened, code below. My question is how can I pull in the above variable for %LatestDir% and apply it to this script below for the path since it changes each week?
Public Function TestExcelMacro()
Dim objXLApp As Object
Dim XLApp As Object
Set XLApp = CreateObject("Excel.Application")
.Application.Visible = True
.UserControl = True
.Workbooks.Open "c:\temp\RAMCOMacos.xlsm", True
.Workbooks.Open "c:\temp\wr20160718\New_ End-Dated_Revised.xlsm", False
Set XLApp = Nothing
SO this line below would look something like this in excel.. Pulling in the "LatestDir". Reason for this is because each week this directory name changes so the code in my XLSM file needs to also reflect what is being capture by the Batch file.
.Workbooks.Open "%LatestDir%\New_ End-Dated_Revised.xlsm", False