topic VBS using SAS variables in Microsoft Integration with SAS

VBS using SAS variables

SoLuD — Mon, 24 Oct 2016 08:21:01 GMT

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!

Re: VBS using SAS variables

Kurt_Bremser — Mon, 24 Oct 2016 08:38:50 GMT

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.

Re: VBS using SAS variables

RW9 — Mon, 24 Oct 2016 08:59:15 GMT

Can you not pass the variable information through as paratemeters to the VBS script, this post shows some examples:

http://stackoverflow.com/questions/2806713/can-i-pass-an-argument-to-a-vbscript-vbs-file-launched-with-cscript

Re: VBS using SAS variables

SoLuD — Tue, 25 Oct 2016 07:56:43 GMT

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.