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/[EMAIL PROTECTED]
Internet e-mail: [EMAIL PROTECTED]
The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.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
