SAS VBA Office Analytics

[DipeshGupta]

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.

[RW9]

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.

[SASKiwi]

Why are you trying VBA when the SAS Add-in to MS Office is so much easier?

[DipeshGupta]

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.