Trouble Executing SAS Stored Process in Excel VBA

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Trouble Executing SAS Stored Process in Excel VBA

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


Accepted Solutions
Solution
‎02-11-2015 10:37 AM
Occasional Contributor
Posts: 8

Re: Trouble Executing SAS Stored Process in Excel VBA

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

View solution in original post


All Replies
Solution
‎02-11-2015 10:37 AM
Occasional Contributor
Posts: 8

Re: Trouble Executing SAS Stored Process in Excel VBA

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

Super Contributor
Posts: 371

Re: Trouble Executing SAS Stored Process in Excel VBA

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 486 views
  • 1 like
  • 2 in conversation