topic Doubt about running SAS Code in VBA in Microsoft Integration with SAS

Doubt about running SAS Code in VBA

AllanN — Fri, 23 Sep 2011 15:55:31 GMT

Hi everybody, my first post here. First of all, sorry about my english.

I've question for you, specialists .

I wanna make (now i started to make) a VBA application in Microsoft Excel to catch data from SAS and put it into a Worksheet. My question is, what's the best way to do this?

I'm thinking about using ADODB connections, and get data from a recordset. Im studying about how to do this in SAS, and learned about which library i have to make reference, and what object i have to use. But, in my case, the documentation don't help me to understand some questions. I have to create a workspace? How can i make sas run the code? (i dont wanna put it in a array or something part of vba code, the code is extensive)

If someone can give me a help about "do this way" or "no.. u're wrong use that"... i'll appreciate.

Thanks before any answer.

Doubt about running SAS Code in VBA

AllanN — Fri, 23 Sep 2011 21:00:33 GMT

Just for information, Here's My code, please welcome for any suggestion:

'Reference to Objects and Libraries

Dim Connect_ADO As New ADODB.Connection
Dim WorkSAS As SAS.Workspace
Dim WorkManager As New SASWorkspaceManager.WorkspaceManager
Dim DefineServer As New SASWorkspaceManager.ServerDef
Dim AddObj As New SASObjectManager.ObjectKeeper

'Create SAS WorkSpace

Public Sub Create_WorkSpace()
    Dim Inf As String
    DefineServer.Protocol = ProtocolBridge
    DefineServer.MachineDNSName = "myremoteserver.dns.com"
    DefineServer.Port = 8591

    Set WorkSAS = WorkManager.Workspaces.CreateWorkspaceByServer("SAS", VisibilityProcess, DefineServer, "mylogin", "mypass", Inf)

    'Run code from my machine, acessing server in network
    Dim SASproc As SAS.StoredProcessService
    Set SASproc = WorkSAS.LanguageService.StoredProcessService
    SASproc.Repository = "file:/networkpc/mycodes"
    SASproc.Execute "MyCode.sas", "argument=123"

End Sub

'Finish and Close SAS WorkSpace, This code isn't working

Public Sub Finish_Workspace()

    WorkManager.Workspaces.RemoveWorkspaceByUUID WorkSAS.UniqueIdentifier
    WorkSAS.Close
    Set WorkSAS = Nothing

End Sub

Doubt about running SAS Code in VBA

AllanN — Wed, 19 Oct 2011 01:36:22 GMT

Just for cooperation, i have success doing this using filename command and language services objects, if someone have interest, i post the code.

Doubt about running SAS Code in VBA

Raghs_Newbee — Thu, 12 Jan 2012 10:38:44 GMT

Hi Allan, Looks like you have done something interesting..in which I am trying to learn.

Can you please share the code of what you have done.

Thanks,

Raghuraman Ramesh