SASRanges.Add not working in VBA

[gantonaci]

I have the following code that is not working. The streams variable is not receiving the values I'm trying to pass to it.

1 Sub INSERTSTOREDPROCESSWITHINPUTSTREAMS()
2   Dim sas As SASExcelAddIn
3   Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
4   Dim streams As SASRanges
5   Set streams = sas.CreateSASRangesObject
6   streams.Add "ENTSAIDA", Worksheets("EntradaSaida").Range("A1:B13")
7   streams.Add "VARIAVEIS", Worksheets("Variaveis").Range("A1", "I7")
8   sas.InsertStoredProcess "/User Folders/antonaci(1)/My Folder/Credibilidade", Worksheets("Plan1").Range("A1"), , , streams
9 End Sub

First, the StoredProcesss is working properly. If I call it from the SAS Menu and select those ranges as input it works without a problem.

If I run the above code I get the following error from SAS:

136       +LIBNAME ENTSAIDA XML;
NOTE: Libref ENTSAIDA was successfully assigned as follows: 
      Engine:        XML 
      Physical Name: ENTSAIDA
137       +DATA WORK.ENTRADASAIDA;
138       +	SET ENTSAIDA.&_WEBIN_SASNAME;
ERROR: File ENTSAIDA.EXCEL_TABLE.DATA does not exist.
139       +RUN;

If I mark the VBA code to pause right before line 8 and check the streams variable, it is empty.

I also tried to use the following code in line 5, but I get the same results.

5 Set streams = New SASRanges

Also,

Worksheets("EntradaSaida").Range("A1:B13").Select

selects the range I want, so I'm sure I didn't misspelled the sheet name.

Does anyone have any idea what may be happening?

I'm using:

SAS Add-In for Microsoft Office 7.15 HF7 (7.100.5.6182)

Microsoft Excel 2013 (15.0.5059.1000)

[TimBeese · Accepted solution]

I took a look at the VBA that you're using and at first glance everything looks correct.  I attempted to replicate your issue and created my own stored process that used two input streams, and I was able to get it to work correctly.  The only thing that I changed from your example was the path of the stored process (pointed to my own), and the names of the input streams.

I used Enterprise Guide to create my stored process and in doing so, I was restricted to 8 characters for the name of my input stream in the stored process wizard.  Your 2nd input stream is defined as "VARIAVEIS", which is 9 characters.  Perhaps there is a limit on the length of the stream name?

I would suggest using stream names that are 8 or less characters to see if that makes a difference.  I used stream1 and stream2.  If there is some sort of limit being enforced, perhaps going through the UI handles that for you, but when automating, it does not.  Maybe the longer stream name is causing some sort of problem on the server that makes both streams invalid?

Other than the stream length, it looks correct to me.  The streams variable is a SAS Add-In scripting variable that stores the name/range internally, so from the VBA debugger it may look empty but not necessarily be empty.  When I looked at that field in my VBA debugger it didn't show anything, but my stored process executed cleanly.

Hope this helps,

Tim Beese

[gantonaci]

The problem was just the name of the stream.

I had noticed it while debbuging but I also saw that the stream object was empty and I was misled by that.

I never tried to run the process again, I was just trying to get the stream object to get the parameters. 

Thanks for the help.