I was playing around with this a bit and came up with the following. Keep
in mind what I said earlier about knowing little about Excel. There are
probably better ways to automate the management of the resulting
worksheet, but this code shows you how you can create an Excel VBA macro
to get the results of a SELECT statement and store the output in a *.xls
file. You will need to make sure that the Microsoft ActiveX Data Objects
Library is included (see the "Tools/References..." menu item in the Visual
Basic editor).

' Begin code
Sub QueryEvents()
   Dim conn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim app As Excel.Application
   Dim wb As Excel.Workbook
   Dim wbFile As String
   Dim sheet As Excel.Worksheet
   Dim row As Long
   Dim sql As String
   Dim today As String
   Dim yesterday As String
   Dim minTimeStamp As String
   Dim maxTimeStamp As String
   Dim dsn As String
   Dim uid As String
   Dim pwd As String
   Dim col As Integer

   row = 0

   '
==========================================================================
   ' These must be tailored for your environment
   '
==========================================================================
   dsn = "amr_ss2"   'Data source name
   uid = "storman"   'TSM Admin ID
   pwd = "xxxxx"     'TSM Admin password
   wbFile = "c:\QueryEvents.xls"   'Output file

   '
==========================================================================
   ' This example runs the following SQL SELECT statement against the
EVENTS
   ' table:
   '
   '    SELECT * FROM EVENTS
   '             WHERE SCHEDULED_START >= <x> AND SCHEDULED_START <= <y>
   '             ORDER BY STATUS, RESULT, REASON, DOMAIN_NAME,
SCHEDULE_NAME,
   '                      NODE_NAME
   '
   '    <x> is yesterday at 00:00:00 (midnight).
   '    <y> is today at 23:59:59 (just before midnight tomorrow).
   '
   ' You will need to tailor this for your environment.
   '
==========================================================================
   today = Date$
   yesterday = DateAdd("d", -1, today)

   '
==========================================================================
   ' Create SQL TIMESTAMP values of format 'YYYY-MM-DD HH:MM:SS'.
   '
==========================================================================
   minTimeStamp = Year(yesterday) & "-" & _
                  Right$("0" & Month(yesterday), 2) & "-" & _
                  Right$("0" & Day(yesterday), 2) & " " & _
                  "00:00:00"

   maxTimeStamp = Year(today) & "-" & _
                  Right$("0" & Month(today), 2) & "-" & _
                  Right$("0" & Day(today), 2) & " " & _
                  "23:59:59"

   sql = "select * from events where " & _
         "scheduled_start >= '" & minTimeStamp & "' and " & _
         "scheduled_start <= '" & maxTimeStamp & "' " & _
         "order by " & _
            "status, result, reason, domain_name, schedule_name,
node_name"

   '
==========================================================================
   ' Open a connection to the TSM server.
   '
==========================================================================
   conn.ConnectionString = "DSN=" & dsn & ";UID=" & uid & ";PWD=" & pwd
   conn.Open

   '
==========================================================================
   ' Run the query (SELECT).
   '
==========================================================================
   rs.ActiveConnection = conn
   rs.Source = sql
   rs.Open

   Set app = New Excel.Application
   Set wb = app.Workbooks.Add
   Set sheet = wb.Worksheets.Add

   '
==========================================================================
   ' The following code loops through the result set and stores each row
of
   ' data in the worksheet. Note that the output columns are those
returned by
   ' the above SQL statement. You will need to tailor the code below such
that
   ' it corresponds with the output columns in your SQL statement.
   '
==========================================================================
   Do Until rs.EOF
      row = row + 1
      sheet.Cells(row, 1).Value = Format(rs!scheduled_start, "yyyy/mm/dd
hh:mm:ss")
      sheet.Cells(row, 2).Value = Format(rs!actual_start, "yyyy/mm/dd
hh:mm:ss")
      sheet.Cells(row, 3).Value = rs!domain_name
      sheet.Cells(row, 4).Value = rs!schedule_name
      sheet.Cells(row, 5).Value = rs!node_name
      sheet.Cells(row, 6).Value = rs!Status
      sheet.Cells(row, 7).Value = rs!result
      sheet.Cells(row, 8).Value = rs!reason
      rs.MoveNext
   Loop

   '
==========================================================================
   ' This loop is cosmetic only. It sets the width of each column so that
the
   ' data fits nicely in the columns.
   '
==========================================================================
   For col = 1 To 8
      sheet.Columns("A:H").AutoFit
   Next

   '
==========================================================================
   ' The "SaveAs" method normally prompts you to overwrite an existing
file.
   ' Setting the "DisplayAlerts" property to "False" disables the prompt.
   '
==========================================================================
   app.DisplayAlerts = False
   sheet.SaveAs wbFile
   app.Quit

   rs.Close
   conn.Close
End Sub
' End code

Regards,

Andy



Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: storman AT us.ibm DOT com

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.

VBA_Example.txt
Description: Text document