Dear all!
Could you, please, help me with the problem?
I have a VBscript that is automatically run via MS scheduler. It runs a .EGP project (SAS enterprise guide 5.1), than goes to Excel, refreshes it and save it as of a specific date like this: "C:\Users\USER123\Desktop\CAP_2014_10_20.xlsx". This date takes place in SAS as a variable that is set as "prev_bd".
So, what I need: can VBscript somehow take the value of this variable from SAS and use it further?
VBscript consists of 2 parts:
1) Standard code LAUNCHING, RUNNING and SAVING SAS. I shall not display it for it is too common;
2) LAUNCHING, REFRESHING AND SAVING EXCEL:
Public Excelapp , ExcelFile ,Sam_Refresh ,Sam_Refresh_Sheet ,ExcelSheet, strLast_BD
Set Excelapp = CreateObject("Excel.Application")
Set ExcelFile = Excelapp.Workbooks.Open("C:\Users\USER123\Desktop\CAP_Template.xlsx")
Set Sam_Refresh = Excelapp.Workbooks.Open("C:\Users\USER123\Desktop\SAM_refresh.xlsm") ' I have to go to this file and extract strLast_BD (same value as "prev_bd" from SAS).
Set Sam_Refresh_Sheet = Sam_Refresh.Sheets(1)
Set ExcelSheet = ExcelFile.Sheets(1)
Excelapp.DisplayAlerts = False
Sam_Refresh.RefreshAll
With Sam_Refresh_Sheet
set strLast_BD= .Range ("C4")
end with
With ExcelFile
.RefreshAll
.SaveAs _
"" & ExcelFile.Path & "" & _
"\" & Left(Left(ExcelFile.Name, (InStrRev(ExcelFile.Name, ".", -1, vbTextCompare) - 1)), (InStrRev(ExcelFile.Name, "_", -1, vbTextCompare) - 9)) & "" & _
"_" & strLast_BD & ".xlsx", 51
end with
with Excelapp
.DisplayAlerts = False
.save
.Quit
end with
Excelapp.quit
In case you need SAS code or something else I would be glad to share it with you!