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
Problem 1
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
Problem 2
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.
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.
Why are you trying VBA when the SAS Add-in to MS Office is so much easier?
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.