Trouble Executing SAS Stored Process in Excel VBA

vibeznstuff — Tue, 10 Feb 2015 21:51:35 GMT

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

Re: Trouble Executing SAS Stored Process in Excel VBA

vibeznstuff — Wed, 11 Feb 2015 15:37:27 GMT

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 pointing to the same directory

Re: Trouble Executing SAS Stored Process in Excel VBA

LineMoon — Mon, 16 May 2016 16:15:53 GMT

@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 ?

topic Re: Trouble Executing SAS Stored Process in Excel VBA in SAS Programming

Trouble Executing SAS Stored Process in Excel VBA

Re: Trouble Executing SAS Stored Process in Excel VBA

Re: Trouble Executing SAS Stored Process in Excel VBA