The import.excel.vbs file demonstrates how to use Visual Basic Script to transfer data from a SAS IOM Server to a Microsoft Excel spreadsheet. To simplify the sample and focus on the code necessary to move the data, the following assumptions are made:

• The Microsoft Excel spreadsheet exists at "C:\myexisting.xls".
• This sample on your machine (a local server) will start the SAS IOM Server.
• The imported data set is named "sashelp.adomsg".

You can change any of these assumptions. Refer to Configuration in this section.

Constants

In the Constants section, return codes, flags, Active X Data Objects (ADO) constants, and Excel constants are defined for use later in the code.

'-----------------------------------------------------------------------------
'--- Constants

const ERR_NOBOOK = -1
const ERR_NOSHEET = -2
const ERR_NORECORDSET = -3
const adOpenForwardOnly = 0
const adLockReadOnly = 1
const adCmdTableDirect = 512
const adCmdText = 1
const xlThin = 2
const xlEdgeBottom = 9
  

Configuration

Configuration values tell the rest of the code where to get data and where to send data. You can change any of these values to match the setup on your machine.

'-----------------------------------------------------------------------------
'--- Configuration vars

Dim Filename, SASConnectionString, DatasetName, DatasetType

'--- Full path to the .xls file
Filename = "C:\myexisting.xls"

'--- Connection string for SAS OLE DB provider
SASConnectionString = "Provider=SAS.IOMProvider.1;Data Source=_LOCAL_"

'--- libref.member or SELECT SQL query
DatasetName = "sashelp.adomsg"

'--- If DatasetName is libref.member then DatasetType should be adCmdTableDirect.
'--- If DatasetName is SELECT SQL statement then DatasetType should be adCmdText.
DatasetType = adCmdTableDirect
  

Get Data Set on SAS IOM Server

The GetRecordset function is used to open an ADO Recordset object on the data set and server named in the Configuration section.

'-----------------------------------------------------------------------------
'--- GetRecordset 	

Private Function GetRecordset()
  Dim Connection
  Set Connection = WScript.CreateObject("ADODB.Connection")
  Set GetRecordset = WScript.CreateObject("ADODB.Recordset")

  Connection.Open SASConnectionString
  GetRecordset.Open DatasetName, Connection, _
      adOpenForwardOnly, adLockReadOnly, DatasetType
End Function
  

Create Sheet in Microsoft Excel

This sample first calls the GetBook function to open the Excel file specified in the Configuration section. If an empty string was specified, then the user is prompted to pick a file to open. This opened file is then passed to the GetSheet function which attempts to create a new sheet in the file named after the data set that is being imported. If a sheet with that name already exists, then the user is prompted to overwrite the sheet or quit the program.

'-----------------------------------------------------------------------------
'--- GetBook 	

Private Function GetBook( appXL )
  If Filename = "" Then
    Set GetBook = appXL.Workbooks.Open( appXL.GetOpenFilename() )
  Else
    Set GetBook = appXL.Workbooks.Open( Filename )
  End If
End Function

Private Function GetSheet( Book )
  Dim res
  on error resume next
  Set GetSheet = Nothing
  Set GetSheet = Book.Sheets( DatasetName & " data" )
  on error goto 0
  If not GetSheet Is Nothing Then
    res = MsgBox( DatasetName & " worksheet found." & vbNewLine & _
                  "Click ""Ok"" to continue and overwrite data on this sheet.", _
                  vbOKCancel Or vbExclamation, "SAS Import Data Wizard" )
    If res = vbCancel Then
        Set GetSheet = Nothing
    end if
  else
    Set GetSheet = Book.Sheets.Add()
    GetSheet.Name = DatasetName & " data"
  End If
End Function
  

Copy Data from SAS IOM Server to Microsoft Excel

Now that both the data set and the Excel sheet are open, the sample transfers data from the data set into the sheet. Starting at the cell "A1", the sample copies the names of the columns into the first row. A border is then placed under these names, and the values from the data set are copied. One sheet in Excel can hold only 256 columns and 65,536 rows. Any data in either dimension that will not fit on one sheet is ignored. The method AutoFit is then called on the columns collection of the Sheet object to make sure that all the data are visible.

'-----------------------------------------------------------------------------
'--- CopyData		  

Private Sub CopyData( Sheet, Recordset )
  Dim maxRows, maxCols
  Dim i, j
  Dim Rng
  maxRows = 65536
  maxCols = 256
  Set Rng = Sheet.Range("a1")
  j = 0
  While j < Recordset.Fields.Count and j < maxCols
    Rng.Offset(0, j).value = Recordset.Fields(j).name
    Rng.Offset(0, j).Borders.Item(xlEdgeBottom).Weight = xlThin
    j = j + 1
  WEnd
  i = 1
  While Not Recordset.EOF and i < maxRows
    Set Rng = Rng.Offset(1)
    j = 0
    While j < Recordset.Fields.Count and j < maxCols
      Rng.Offset(, j).Value = Recordset.Fields(j).Value
      j = j + 1
    WEnd
    Recordset.MoveNext
    i = i + 1
  WEnd
  Sheet.Columns.AutoFit
End Sub
  

The export.excel.vbs file demonstrates how to use Visual Basic Script to transfer data from a Microsoft Excel spreadsheet to a SAS IOM Server. To simplify the sample and focus on the code necessary to move the data, the following assumptions are made:

• The Microsoft Excel spreadsheet exists at "C:\myexisting.xls".
• The Microsoft Excel spreadsheet is named "Sheet1".
• The selection "A1:D20" is exported from "Sheet1".
• This sample on your machine (a local server) will start the SAS IOM Server.
• The created data set is named "sasuser.mydata".

You can change any of these assumptions. Refer to Configuration in this section.

Constants

In the Constants section, return codes, flags, ADO constants, and Excel constants are defined for use later in the code.

'-----------------------------------------------------------------------------
'--- Constants

const ERR_NOBOOK = -1
const ERR_NOSHEET = -2
const ERR_NORECORDSET = -3
const ERR_NORANGE = -4
const adOpenForwardOnly = 0
const adOpenDynamic = 2
const adLockReadOnly = 1
const adLockOptimistic = 3
const adCmdTableDirect = 512
const adCmdText = 1
const xlThin = 2
const xlEdgeBottom = 9
  

Configuration

Configuration values tell the rest of the code where to get data and where to send data. You can change any of these values to match the setup on your machine.

'-----------------------------------------------------------------------------
'--- Configuration vars

Dim Filename, SheetName, Selection, SASConnectionString, DatasetName, DatasetType

'--- Full path to the .xls file
Filename = "C:\myexisting.xls"

'--- Name of the sheet to export
SheetName = "Sheet1"

'--- Name of the selection to export
Selection = "A1:D20"

'--- Connection string for SAS OLE DB provider
SASConnectionString = "Provider=SAS.IOMProvider.1;Data Source=_LOCAL_"

'--- libref.member
DatasetName = "sasuser.mydata"

'--- DatasetType should be adCmdTableDirect.
DatasetType = adCmdTableDirect
  

Get Selection in Excel

This sample first calls the GetBook function to open the Excel file specified in the Configuration section. This opened file is then passed to the GetSheet function which attempts to find the sheet specified in the Configuration section. If the sheet cannot be found, then the user is notified, and the sample exits. A Range object is then obtained for the cells specified in the Configuration section.

'-----------------------------------------------------------------------------
'--- GetBook			  

Private Function GetBook( appXL )
  Set GetBook = appXL.Workbooks.Open( Filename )
End Function

Private Function GetSheet( Book )
  Dim res
  On Error Resume Next
  Set GetSheet = Nothing
  Set GetSheet = Book.Sheets( SheetName )
  On Error GoTo 0
  If GetSheet Is Nothing Then
    MsgBox SheetName & " worksheet not found.", vbOK Or vbExclamation, _
           "SAS Export Data Wizard"
  End If
End Function

Private Function GetRange( Sheet )
  Set GetRange = Sheet.Range(Selection)
End Function
  

Create Data Set on SAS IOM Server

The sample then attempts to create a new data set on the SAS IOM Server. The name of the new data set and the server on which the data set will be created are specified in the Configuration section. The structure of the data set is determined by the first row found in the selected range. This row must indicate the name, type, and width of each column. The format is "name:type:width".

For example, a column named "Col1" that is numeric and has a width of 4 must have "Col1:num:4" in the first cell of that column. Character columns are specified as type "chr". Other methods to obtain column information are beyond the scope of this sample. If the data set already exists, then the user is prompted to overwrite it or exit.

'-----------------------------------------------------------------------------
'--- GetSASRecordset

Private Function GetSASRecordset( Rng, Types )
  Dim res, i, j, bFirst, elm, typename, colname, width, pos, sql
  Dim Connection
  ReDim Types( Rng.Columns.Count - 1 )
  Set Connection = WScript.CreateObject("ADODB.Connection")
  Set GetSASRecordset = WScript.CreateObject("ADODB.Recordset")

  Connection.Open SASConnectionString
  On Error Resume Next
  GetSASRecordset.Open DatasetName, Connection, adOpenForwardOnly, _
                       adLockReadOnly, adCmdTableDirect
  If Err.Number = 0 Then
    GetSASRecordset.Close
    res = MsgBox( DatasetName & " table found." & vbNewLine & _
                  "Click ""Ok"" to continue and overwrite this table.", _
                  vbOKCancel Or vbExclamation, "SAS Export Data Wizard")
    If res = vbCancel Then
      Set GetSASRecordset = Nothing
      Exit Function
    End If
  ElseIf Err.Number <> -2147217865 Then
    MsgBox "Unexpected error: " & Err.Description & ": " & Err.Number
    Set GetSASRecordset = Nothing
    Exit Function
  End If
  On Error GoTo 0
  sql = "create table " & DatasetName & " ("
  bFirst = True
  i = 1
  j = 1
  For j = 1 to Rng.Columns.Count
    Set elm = Rng.Cells(i, j)
    If IsEmpty(elm) Then Exit For
    typename = ""
    colname = elm.Value
    width = "8"
    pos = InStr(elm.Value, ":")
    If pos <> 0 Then
      typename = Mid(elm.Value, pos + 1)
      colname = Left(elm.Value, pos - 1)
      pos = InStr(typename, ":")
      If pos <> 0 Then
        width = Mid(typename, pos + 1)
        typename = Left(typename, pos - 1)
      End If
    End If
    Types(j - 1) = typename
    If Not bFirst Then sql = sql & ", "
    bFirst = False
    If typename = "chr" Then
      sql = sql & colname & " char(" & width & ")"
    ElseIf typename = "num" Or typename = "" Then
      sql = sql & colname & " num(" & width & ")"
    End If
  Next
  sql = sql & ")"
  Connection.Execute sql

  GetSASRecordset.Open DatasetName, Connection, adOpenDynamic, _
                       adLockOptimistic, adCmdTableDirect
End Function
  

Copy Data from Excel to SAS IOM Server

Now that both the data set and the Excel sheet are open, the sample transfers data from the sheet into data set. Starting at the second row in the specified range (because the first one contains column information), the sample copies the data from each cell.

'-----------------------------------------------------------------------------
'--- CopyData		

Private Sub CopyData( Rng, Types, Recordset )
  Dim i, j, elm, vt

  For i = 2 To Rng.Rows.Count
    Recordset.AddNew
    For j = 1 To Rng.Columns.Count
      Set elm = Rng.Cells(i, j)
      If Not IsEmpty(elm) Then
        vt = VarType(elm.Value)
        If vt = 8 Then ' elm.Value is character data
          If Types(j - 1) = "chr" Then ' column is character data
            Recordset.Fields(j - 1) = elm.Value
          Else ' column is numeric data
            Recordset.Fields(j - 1) = Null ' CDbl(elm.Value)
          End If
        ElseIf vt = 5 Then ' elm.Value is numeric data
          If Types(j - 1) = "chr" Then ' column is character data
            Recordset.Fields(j - 1) = CStr(elm.Value)
          Else ' column is numeric data
            Recordset.Fields(j - 1) = elm.Value
          End If
        End If
      End If
    Next
    Recordset.Update
  Next
End Sub
  

The import.access.vbs file demonstrates how to use Visual Basic Script to transfer data from a SAS IOM Server to a Microsoft Access database. To simplify the sample and focus on the code necessary to move the data, the following assumptions are made:

• The Microsoft Access database exists at "C:\myexisting.mdb".
• This sample on your machine (a local server) will start the SAS IOM Server.
• The imported data set is named "sashelp.adomsg".

You can change any of these assumptions. Refer to Configuration in this section.

Constants

In the constants section, return codes, flags, ADO constants, and Excel constants are defined for use later in the code.

'-----------------------------------------------------------------------------
'--- Constants

const ERR_NOSASRECORDSET = -1
const ERR_NOACCESSRECORDSET = -2
const adOpenForwardOnly = 0
const adOpenDynamic = 2
const adLockReadOnly = 1
const adLockBatchOptimistic = 4
const adCmdTableDirect = 512
const adCmdText = 1
const adSchemaTables = 20
const adChar = 129
const adVarChar = 200
const adWChar = 130
const adVarWChar = 202
const adDouble = 5
  

Configuration

Configuration values tell the rest of the code where to get data and where to send data. You can change any of these values to match the setup on your machine.

'-----------------------------------------------------------------------------
'--- Configuration vars

Dim Filename, SASConnectionString, DatasetName, DatasetType

'--- Full path to the .mdb file
Filename = "c:\myexisting.mdb"

'--- Connection string for SAS OLE DB provider
SASConnectionString = "Provider=SAS.IOMProvider.1;Data Source=_LOCAL_"

'--- libref.member or SELECT SQL query
DatasetName = "sashelp.adomsg"

'--- If DatasetName is libref.member then DatasetType should be adCmdTableDirect.
'--- If DatasetName is SELECT SQL statement then DatasetType should be adCmdText.
DatasetType = adCmdTableDirect

'-----------------------------------------------------------------------------
'--- Global vars

Dim AccessConnectionString
AccessConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Filename
  

Get Data Set on SAS IOM Server

The GetSASRecordset function is used to open an ADO Recordset object on the data set and server named in the Configuration section.

'-----------------------------------------------------------------------------
'--- GetSASRecordset	

Private Function GetSASRecordset()
  Dim Connection
  Set Connection = WScript.CreateObject("ADODB.Connection")
  Set GetSASRecordset = WScript.CreateObject("ADODB.Recordset")

  Connection.Open SASConnectionString
  GetSASRecordset.ActiveConnection = Connection
  GetSASRecordset.CacheSize = 55
  GetSASRecordset.Properties("Maximum Open Rows") = 110
  GetSASRecordset.Open DatasetName, , adOpenForwardOnly, _
                       adLockReadOnly, DatasetType
End Function
  

Create Table in Microsoft Access

The GetAccessRecordset method is used to create a new table in the specified Access database named after the data set that is being imported. If a table with this name already exists, then the user is prompted to overwrite or exit the sample. The structure of this new table is based on that of the SAS data set opened in the GetSASRecordset method.

'-----------------------------------------------------------------------------
'--- GetAccessRecordset	

Private Function GetAccessRecordset( SASRecordSet )
  Dim Connection, TablesRS, Tbl, res, bFirst, bContinue, fld, sql, Name
  Name = ConvertToAccessName( DatasetName )
  Set Connection = WScript.CreateObject("ADODB.Connection")
  Set GetAccessRecordset = WScript.CreateObject("ADODB.Recordset")
  Set TablesRS = WScript.CreateObject("ADODB.Recordset")

  Connection.Open AccessConnectionString
  Set TablesRS = Connection.OpenSchema( adSchemaTables )

  bContinue = True
  While bContinue And Not TablesRS.EOF
    If TablesRS.Fields("TABLE_NAME").Value = Name Then
      res = MsgBox( Name & " table found." & vbNewLine & _
                    "Click ""Ok"" to continue and overwrite this table.", _
                    vbOKCancel Or vbExclamation, "SAS Import Data Wizard" )
      If res = vbCancel Then
        Set GetAccessRecordset = Nothing
        Exit Function
      End If
      Connection.Execute "DROP TABLE [" & Name & "]"
      bContinue = False
     End If
    TablesRS.MoveNext
  WEnd

  bFirst = True
  sql = "CREATE TABLE [" & Name & "] ("
  For Each fld In SASRecordSet.Fields
    If Not bFirst Then sql = sql & ", "
    sql = sql & "[" & fld.name & "]"
    If fld.Type = adChar Or _
       fld.Type = adVarChar Or _
       fld.Type = adWChar Or _
       fld.Type = adVarWChar Then
      sql = sql & " TEXT(" & fld.DefinedSize & ")"
    ElseIf fld.Type = adDouble Then
      sql = sql & " FLOAT"
    End If
    bFirst = False
  Next
  sql = sql & ")"

  Connection.Execute sql

  GetAccessRecordset.Open Name, Connection, adOpenDynamic, _
                          adLockBatchOptimistic, adCmdTableDirect

End Function
  

Copy Data from SAS IOM Server to Microsoft Access

Now that a Recordset is open on both the SAS data set and the Access table, the actual data are copied. The src and dst parameters both must be open Recordset objects.

'-----------------------------------------------------------------------------
'--- CopyData			

Private Sub CopyData( src, dst )
  Dim col
  While Not src.EOF
    dst.AddNew
    For col = 0 To src.Fields.Count - 1
      dst.Fields(col).Value = src.Fields(col).Value
    Next
    dst.UpdateBatch
    src.MoveNext
  Wend
End Sub
  

The export.access.vbs file demonstrates how to use Visual Basic Script to transfer data from a Microsoft Access database to a SAS IOM Server. To simplify the sample and focus on the code necessary to move the data, the following assumptions are made:

• The Microsoft Access database exists at "C:\myexisting.mdb".
• The Microsoft Access database table is named "mytable".
• This sample on your machine (a local server) will start the SAS IOM Server.
• The created data set is named "sasuser.mydata".

You can change any of these assumptions. Refer to Configuration in this section.

Constants

In the constants section return codes, flags, ADO constants, and Excel constants are defined for use later in the code.

'-----------------------------------------------------------------------------
'--- Constants

const ERR_NOSASRECORDSET = -1
const ERR_NOACCESSRECORDSET = -2
const adOpenForwardOnly = 0
const adOpenDynamic = 2
const adLockReadOnly = 1
const adLockBatchOptimistic = 4
const adCmdTableDirect = 512
const adCmdText = 1
const adSchemaTables = 20
const adChar = 129
const adVarChar = 200
const adWChar = 130
const adVarWChar = 202
const adDouble = 5
  

Configuration

Configuration values tell the rest of the code where to get data and where to send data. You can change any of these values to match the setup on your machine.

'-----------------------------------------------------------------------------
'--- Configuration vars

Dim Filename, SASConnectionString, TableName, DatasetName, DatasetType

'--- Full path to the .mdb file
Filename = "c:\myexisting.mdb"

'--- Connection string for SAS OLE DB provider
SASConnectionString = "Provider=SAS.IOMProvider.1;Data Source=_LOCAL_"

'--- Access tablename
TableName = "sashelp_adomsg"

'--- libref.member
DatasetName = "sasuser.mydata"

'--- DatasetType should be adCmdTableDirect.
DatasetType = adCmdTableDirect

'-----------------------------------------------------------------------------
'--- Global vars

Dim AccessConnectionString
AccessConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Filename
  

Get Table in Access

The sample then attempts to open a Recordset on the Access table specified in the Configuration section.

'-----------------------------------------------------------------------------
'--- GetAccessRecord	

Private Function GetAccessRecordset()
  Dim Connection
  Set Connection = WScript.CreateObject("ADODB.Connection")
  Set GetAccessRecordset = WScript.CreateObject("ADODB.Recordset")

  Connection.Open AccessConnectionString
  GetAccessRecordset.Open TableName, Connection, adOpenForwardOnly, _
                          adLockReadOnly, DatasetType
End Function
  

Create Data Set on SAS IOM Server

The sample then attempts to create a new data set on the SAS IOM Server. The name of the new data set and the server on which the data set will be created are specified in the Configuration section. If the table exists, then the user is prompted to overwrite it or exit the program.

'-----------------------------------------------------------------------------
'--- GetSASRecordset 

Private Function GetSASRecordset( AccessRecordset )
  Dim Connection, TablesRS, Tbl, res, bFirst, bContinue, fld, sql, Name
  Set Connection = WScript.CreateObject("ADODB.Connection")
  Set GetSASRecordset = WScript.CreateObject("ADODB.Recordset")
  Set TablesRS = WScript.CreateObject("ADODB.Recordset")

  Connection.Open SASConnectionString
  Set TablesRS = Connection.Execute( _
      "select libname, memname from dictionary.tables")

  bContinue = True
  While bContinue And Not TablesRS.EOF
    If lcase(TablesRS.Fields("libname").Value & _
             "." & _
             TablesRS.Fields("memname")) = lcase(DatasetName) Then
      res = MsgBox( DatasetName & " table found." & vbNewLine & _
                    "Click ""Ok"" to continue and overwrite this table.", _
                    vbOKCancel Or vbExclamation, "SAS Import Data Wizard")
      If res = vbCancel Then
        Set GetSASRecordset = Nothing
        Exit Function
      End If
      Connection.Execute "DROP TABLE " & DatasetName
      bContinue = False
     End If
    TablesRS.MoveNext
  WEnd

  bFirst = True
  sql = "CREATE TABLE " & DatasetName & " ("
  For Each fld In AccessRecordset.Fields
    If Not bFirst Then sql = sql & ", "
    sql = sql & fld.name
    If fld.Type = adChar Or _
       fld.Type = adVarChar Or _
       fld.Type = adWChar Or _
       fld.Type = adVarWChar Then
      sql = sql & " CHAR(" & fld.DefinedSize & ")"
    ElseIf fld.Type = adDouble Then
      sql = sql & " NUM"
    End If
    bFirst = False
  Next
  sql = sql & ")"

  Connection.Execute sql

  GetSASRecordset.Open DatasetName, Connection, adOpenDynamic, _
                       adLockBatchOptimistic, adCmdTableDirect

End Function
  

Copy Data from Access to SAS IOM Server

Now that both the data set and the Access table are open, the sample transfers data from the table into data set. The src and dst parameters both must be open Recordset objects.

'-----------------------------------------------------------------------------
'--- CopyData	

Private Sub CopyData( src, dst )
  Dim col
  While Not src.EOF
    dst.AddNew
    For col = 0 To src.Fields.Count - 1
      dst.Fields(col).Value = src.Fields(col).Value
    Next
    dst.UpdateBatch
    src.MoveNext
  Wend
End Sub