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
... View more