09-23-2011 11:55 AM
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.
09-23-2011 05:00 PM
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"
'Finish and Close SAS WorkSpace, This code isn't working
Public Sub Finish_Workspace()
Set WorkSAS = Nothing