06-22-2017 01:28 AM
I am trying to connect SAS to Excel through VBA and I found out that one way of doing this is through Excel Add in but i encountered several problems in this method and I can't figure out the solution.
My VBA code looks like this >
Sub testconnectiontosas() Dim sas As SASExcelAddIn Set sas = Application.COMAddIns.Item("Sas.ExcelAddIn").Object Dim prompts As SASPrompts Set prompts = New SASPrompts Dim rng As Range Set rng = Worksheets(1).Range("A1") Dim z As String z = InputBox("GIVE THE DATA TABLE NAME SAVED IN ACTSHARE LIBRARY") prompts.Add "BASELEVEL1", z sas.InsertStoredProcess "/User Folders/SAS Guest/My Folder/Stored Process for Program 3", rng, prompts
It is not calling the prompt dialog box as the addin does when I run a SAS program through Excel Addin directly without using VBA
It allows me to assign value to only one prompt but what if My program has more than one prompt like 2,3,4... prompts
I would be very glad if someone can help me with this problem.
06-22-2017 04:03 AM
Seems a bit overcomplicated. You can generate Excel files from SAS. Then post them to a secure portal for people to look at. Doing it this way you need to maintain 3 separate apps, the VBA code, which of course would involve QC, validating entriy and such like. Plus you would also need some sort of security model so not everyone who has that file can access your data.
If you really need something like this there is software out there already, SAS has web reporting and such like, plus there are others.
06-23-2017 12:13 AM
Actually I ab building a model in Excel where I want SAS to perform its function in the backend. So the process will be fully automatic, running through VBA macros and the user doesn't have to click SAS add - in.
But the only problem lies there is I have prompt of multiple values and I am not able to figure out how can I pass multiple values to SAS prompts.
I am using this code
Dim sas As SASExcelAddIn Set sas = Application.COMAddIns.Item("Sas.ExcelAddIn").Object Dim prompts As SASPrompts Set prompts = New SASPrompts prompts.Add "RF_SEL1", Range("H2:H5").Value Dim rng As Range Set rng = Worksheets(1).Range("A1") sas.InsertStoredProcess "/User Folders/SAS Guest/My Folder/Stored Process for Program 2", rng, prompts
But this code is not woring with the RANGE("H2:H5").
How can I provide it with the range of values.