I try to connect Excel to SAS using ADO but encountered a problem - SAS returns recordset that contain DATE columns only in SAS format eg. 19236 (instead 2012-08-31)
I went through whole http://www.technion.ac.il/docs/sas9/book_files/58657.pdf and tried to set :
rs.Properties("SAS Formats") = "_ALL_"
rs.Properties("SAS Informats") = "_ALL_"
which should enforce SAS to use data type defined in table (all date formats and informats are YYMMDD10.) .
Itried also
rs.Properties("SAS Formats") = "date=YYMMDD10."
rs.Properties("SAS Informats") = "date=YYMMDD10."
NO SUCCESS!!!
My code is following:
Sub get_ado_table_from_sas()
On Error GoTo err:
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim errorstring As String
Path = "C:\dokumenty\!sas_biblioteki\TESTOWE_OBLICZENIA\PR_NARZEDZIE"
Set obWS = obWSM.Workspaces.CreateWorkspaceByServer("Local", _
VisibilityProcess, Nothing, "", "", errorstring)
db.Open "provider=sas.iomprovider.1; SAS Workspace ID=" + obWS.UniqueIdentifier
rs.ActiveConnection = db
rs.Properties("SAS Formats") = "_ALL_"
rs.Properties("SAS Informats") = "_ALL_"
rs.Open "pr_arch.cal_repl_port_str", db, adOpenDynamic, adLockReadOnly, adCmdTableDirect
Cells(6, 1).CopyFromRecordset rs
rs.Close
db.Close
Exit Sub
err:
MsgBox err.Description
End Sub