10-24-2016 04:15 AM - edited 10-24-2016 04:21 AM
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
set strLast_BD= .Range ("C4")
"" & ExcelFile.Path & "" & _
"\" & Left(Left(ExcelFile.Name, (InStrRev(ExcelFile.Name, ".", -1, vbTextCompare) - 1)), (InStrRev(ExcelFile.Name, "_", -1, vbTextCompare) - 9)) & "" & _
"_" & strLast_BD & ".xlsx", 51
.DisplayAlerts = False
In case you need SAS code or something else I would be glad to share it with you!
10-24-2016 04:38 AM
For communication between separate processes, either write the data to a text file, or store it in an environment variable, if the processes share the same environment.
External files are usually the more robust method, and you can easily read the value after the fact.
10-24-2016 04:59 AM
Can you not pass the variable information through as paratemeters to the VBS script, this post shows some examples:
10-25-2016 03:56 AM
KurtBremser, RW9, thank you for the help!
I tried to pass the date to .txt file from SAS. Works good but this methond is pretty the same as mine, so I should keep it for now.