running SAS macro from Excel VBA

[yanivtz]

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

[Astounding]

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.

[yanivtz]

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".

[yanivtz]

Still looking for a solution... Thanks