topic Re: SAS Prompt in Excel in SAS Enterprise Guide

SAS Prompt in Excel

DipeshGupta — Tue, 04 Jul 2017 05:26:01 GMT

I have facing some problem in defining SAS prompts in excel by using VBA-Excel addin.

My Program was running perfectly but recently it started showing an error of out of memory on the line where SAS prompt is defined.

Dim sas As SASExcelAddIn

Set sas = Application.COMAddIns.Item("Sas.ExcelAddIn").Object

Dim prompts As SASPrompts

Set prompts = New SASPrompts ( Showing an error of out of memory in this line)

Can't understand the problem.

Can anyone help me with this..??

Re: SAS Prompt in Excel

CaseySmith — Wed, 05 Jul 2017 14:26:39 GMT

This paper by @TimBeese might help if you haven't already seen:

http://support.sas.com/resources/papers/proceedings11/012-2011.pdf

Re: SAS Prompt in Excel

DipeshGupta — Thu, 06 Jul 2017 07:05:05 GMT

Ya I have seen this and followed this PDF right from the starting but facing some problem on the line

"Set prompts = New SasPrompts"

The Code is working perfectly on my PC but whenever I run the Excel-VBA program on someone's else PC it shows me an error on the line "Set prompts = New SasPrompts" .

The Error is "Activex can't create object"

I have made sure that all references are added which were added in My PC.

I have repaired the office application in the second PC.

I have also Reinstalled the office application.

But the error still persists.

In summary the program is working only on My PC and can't understand why so.

can you please guide me how to solve the issue because maybe that can solve the problem of Out of Memory also.

I have even tried a small code in the 2nd PC i.e.

Sub check()

Dim SAS As SASExcelAddIn

Set SAS = Application.COMAddIns.Item("Sas.ExcelAddIn").Object

Dim prompts As SASPrompts

Set prompts = New SASPrompts

SAS.HelloWorld

End Sub

But it gives the same error on the same line.

I have even tried this thing in 4 other PC's but everytime the same error except My PC.

How to solve this issue..??

Re: SAS Prompt in Excel

CaseySmith — Fri, 07 Jul 2017 02:40:32 GMT

I suggest checking to see if the SAS Add-in for Microsoft Office (AMO) is installed and activated in Excel on those other machines. For example, when you open Excel (on the other machines), do you see the SAS ribbon (and able to view its Help->About dialog)? If not, you'll need to install (if not already) and enable it (SAS Add-in for Microsoft Office) in Excel (which you can do with the SwitcherUtility.exe utility in the AMO installation folder... ex. C:\Program Files (x86)\SASHome\x86\SASAddinforMicrosoftOffice\7.1\).