h******n
发帖数: 232 | 1 举个例子。
在c盘根目录下有个写好的test.sas。请问能不能在excel VBA里面运行一遍这个test.
sas, 不需要返回任何值。
拜谢！ | g*********r
发帖数: 2847 | 2 Yes. But perhaps you need to have a specific SAS add-on installed. don't ask
me which add-on, I have no idea. Our IT guy did it for us. | h******n
发帖数: 232 | 3 我想add on我是有的。但是不知道VBA code怎么写。能麻烦你给我列个sample VBA
code么？
多谢！ | h******n
发帖数: 232 | | w********m
发帖数: 1137 | 5 Sub iomtest()
Dim swsSAS As SAS.Workspace
Dim rsSAS As New ADODB.Recordset
Dim obObjectFactory As New SASObjectManager.ObjectFactory
Dim obServer As New SASObjectManager.ServerDef
Dim cnnIOM As New ADODB.Connection
Dim xmlInfo As String
Dim fld As Field
Dim row As Long
Dim col As Long
obServer.MachineDNSName = "localhost"
'We Create a local SAS workspace
Set swsSAS = obObjectFactory.CreateObjectByServer("Workspace", True,
obServer, "myUserName", "myPassword")
'Run the SAS program at c:\ saspg\sasglforum.sas my
Dim obStoredProcessService As SAS.StoredProcessService
Set obStoredProcessService = swsSAS.LanguageService.StoredProcessService
obStoredProcessService.Repository = "file:C:\mysaspg"
obStoredProcessService.Execute "sasglforum", Parameters
‘Export the SAS data set to Excel
cnnIOM.Provider = "sas.LocalProvider.1"
cnnIOM.Properties("Data Source") = "c:\temp"
cnnIOM.Open
rsSAS.Open "sasgldata", cnnIOM, adOpenDynamic, adLockPessimistic,
ADODB.adCmdTableDirect
'Select the sheet sasglf and freeze the panes on the 2ndline
Worksheets("sasglf").Activate
Range("A2").Select
ActiveWindow.FreezePanes = True
If Not (rsSAS.BOF And rsSAS.EOF) Then
Worksheets("sasglf").Activate
Range("A1").Select
rsSAS.MoveFirst
row = 2
Do While Not rsSAS.EOF
ActiveSheet.Cells(row, 1).Select
For Each fld In rsSAS.Fields
ActiveCell.Value = fld.Value
ActiveCell.Next.Select
Next
row = row + 1
rsSAS.MoveNext
Loop
Range("A2").Select
End If
rsSAS.Close
Set rsSAS = Nothing
cnnIOM.Close
Set cnnIOM = Nothing
swsSAS.Close
Set swsSAS = Nothing
Set swmWM = Nothing
End Sub | g*********r
发帖数: 2847 | 6 Thanks LS.
I'm sorry I have been very busy these days and hopefully LS's codes work for
you.
I did such stuff about a year ago, using some help of google. I created a .
bat file and used SHELL function. I probably cannot write ready-to-run codes
as LS right now and I don't have time to test the codes out. Sorry about
that. |
|
