SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

VBS using SAS variables

Reply
New Contributor
Posts: 2

VBS using SAS variables

[ Edited ]

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!

Super User
Posts: 7,405

Re: VBS using SAS variables

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,695

Re: VBS using SAS variables

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-wi...

 

New Contributor
Posts: 2

Re: VBS using SAS variables

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.

Ask a Question
Discussion stats
  • 3 replies
  • 280 views
  • 0 likes
  • 3 in conversation