topic Re: running SAS macro from Excel VBA in SAS Programming

running SAS macro from Excel VBA

yanivtz — Tue, 01 Jan 2019 17:42:14 GMT

Hi,

I am trying to run SAS from Excel, using VBA.

Very basic.

I am attaching the code. The error I am getting is: "the object could not be found; make sure it was previously added to the object keeper"

sub Macro1 ()
    Dim obObjectFactory As New SASObjectManager.ObjectFactoryMulti2

    Dim obObjectKeeper As New SASObjectManager.ObjectKeeper

    Dim obConnection As New ADODB.Connection
    Dim obRecordSet As New ADODB.Recordset
    Dim errorString As String
    Dim sourcebuffer As String
    Dim height As Variant
    Dim weight As Variant
    height = 180
    weight = 180
    Dim obServer As New SASObjectManager.ServerDef

    obServer.MachineDNSName = [myserver]
    obServer.Protocol = SASObjectManager.Protocols.ProtocolBridge
    obServer.Port = 8591

   If (obSAS Is Nothing) Then
   Set obSAS = obObjectFactory.CreateObjectByServer("sas", True, obServer, [login],[pwd])

   End If

sourcebuffer = "options sasautos=(sasautos,'X:\Actuarya\USERS\tzafiry\Madadim\bmi.sas');%bmi(" & height & "," & weight & ");"
obSAS.LanguageService.Submit sourcebuffer

obConnection.Open "provider=sas.iomprovider.1; SAS workspace ID=" + obSAS.UniqueIdentifier
obRecordSet.Open "work.result", obConnection, adOpenStatic, adLockReadOnly, adCmdTableDirect
obRecordSet.MoveFirst
bmi = obRecordSet(0).Value

End Sub

Re: running SAS macro from Excel VBA

Astounding — Tue, 01 Jan 2019 17:46:28 GMT

When setting up SASAUTOS, the paths used should be to a folder not to an individual file. Where you have this:

'X:\Actuarya\USERS\tzafiry\Madadim\bmi.sas'

Try replacing that with:

'X:\Actuarya\USERS\tzafiry\Madadim'

When you try to use %bmi, SAS will automatically search for its definition in a file named bmi.sas.

Re: running SAS macro from Excel VBA

yanivtz — Wed, 02 Jan 2019 05:41:13 GMT

The code failed after executing:

obConnection.Open "provider=sas.iomprovider.1; SAS workspace ID=" + obSAS.UniqueIdentifier

I am getting an error that "the object could not be found; make sure it was previously added to the object keeper".

Re: running SAS macro from Excel VBA

yanivtz — Sun, 06 Jan 2019 13:50:46 GMT

Still looking for a solution... Thanks