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
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.
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.
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 ?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.