SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Doubt about running SAS Code in VBA

Reply
Occasional Contributor
Posts: 9

Doubt about running SAS Code in VBA

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

I've question for you, specialists Smiley Wink.

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.

Occasional Contributor
Posts: 9

Doubt about running SAS Code in VBA

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

Occasional Contributor
Posts: 9

Doubt about running SAS Code in VBA

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

Contributor
Posts: 24

Doubt about running SAS Code in VBA

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    

Ask a Question
Discussion stats
  • 3 replies
  • 899 views
  • 0 likes
  • 2 in conversation