Trouble Executing SAS Stored Process in Excel VBA

[vibeznstuff]

Hi there,

I'm trying to execute a stored process (doesn't take any parameters) from Excel VBA but it doesn't seem to be working. It works when I run it in SAS (simply creating a table that filters on a column, this is all for testing purposes) but when I run it in Excel it doesn't seem to execute (and it doesn't crash either). I'll run the macro but when I check the output dataset it remains unchanged as if the process didn't run. When I then run it in SAS it works as expected. I'll paste the structure of my code below if anyone might be able to spot any issues that I'm missing. Also, connecting to the workspace hasn't been an issue. I've been able to pull data fine and it even seems to be finding the procedure as well.

*************************************************************************************************

    Dim obSAS As SAS.Workspace

    Dim obWorkspaceManager As New SASWorkspaceManager.WorkspaceManager

    Dim obServerDef As New SASWorkspaceManager.ServerDef

    Dim xmlString As String

    obServerDef.Port = 8591

    obServerDef.Protocol = ProtocolBridge ' Multiple hostNames can be provided

                                          ' for failover; order is unreliable.

    obServerDef.MachineDNSName = "abc.domain.com"

    Set obSAS = obWorkspaceManager.Workspaces.CreateWorkspaceByServer("", VisibilityProcess, obServerDef, "user", "pwd", xmlString)

    Dim SASproc As SAS.StoredProcessService

    Set SASproc = obSAS.LanguageService.StoredProcessService

    SASproc.Repository = "file:/path"

    SASproc.Execute "testSP.sas", ""

*************************************************************************************************

Thanks for any help in advance. I'm pretty new to this.

Best,

Richard

[vibeznstuff · Accepted solution]

Nevermind, I found what the error was after checking the log output (should have done so from the start). I was getting a "lock is not available" error because I had the dataset/proc open on the actual SAS session and I think it was creating a conflict with my call from Excel.

source: 8130 - "Lock is not available" error message appears when running multiple SASjobs with data files p...

[LineMoon]

@vibeznstuff

Hello,

I am inerested by your subject.

Can we use  VBA and Excel to call a SAS stored processes, if we do not have a  SAS Add-In for Microsoft Office ?