Hi Jim,
I agree with you about how this code displays the data. Also, I think
it could run better and so I revised it to this:
Sub ListWindowsUpdates()
Dim objUpdateSession As Object, objUpdateEntry As Object,
objUpdateSearcher As Object
Dim lRow As Long, iHistoryCount As Integer
Dim UpdateHistory
lRow = 2 '//row to start displaying data on
Set objUpdateSession = CreateObject("Microsoft.Update.Session")
Set objUpdateSearcher = objUpdateSession.CreateUpdateSearcher
iHistoryCount = objUpdateSearcher.GetTotalHistoryCount
Set UpdateHistory = objUpdateSearcher.QueryHistory(0, iHistoryCount)
With Application: .ScreenUpdating = False: .Calculation =
xlCalculationManual: End With
For Each objUpdateEntry In UpdateHistory '//loop through all Windows
updates
Range(Cells(lRow, 1), Cells(lRow, 3)) = Array(objUpdateEntry.Title,
objUpdateEntry.Description, objUpdateEntry.Date)
Select Case objUpdateEntry.Operation '//returns a number 1 or 2
Case 1: Cells(lRow, 4) = "Installation"
Case 2: Cells(lRow, 4) = "Uninstallation"
Case Else: Cells(lRow, 4) = "Operation type could not be
determined."
End Select
Select Case objUpdateEntry.ResultCode '//returns a number 0 to 5
Case 0: Cells(lRow, 5) = "Operation has not started."
Case 1: Cells(lRow, 5) = "Operation is in progress."
Case 2: Cells(lRow, 5) = "Operation completed successfully."
Case 3: Cells(lRow, 5) = "Operation completed, but errors
occurred and the results are potentially incomplete."
Case 4: Cells(lRow, 5) = "Operation failed to complete."
Case 5: Cells(lRow, 5) = "Operation was aborted."
Case Else: Cells(lRow, 5) = "Operation result could not be
determined."
End Select
Cells(lRow, 6) = objUpdateEntry.UpdateIdentity.UpdateID
lRow = lRow + 1
Next
With Range("A2:F2") 'Write titles of columns
.FormulaR1C1 = Array("Title:", "Description:", "Update Application
Date:", "Operation Type:", "Operation Result:", "Update ID:")
.EntireRow.Font.Bold = True: .EntireColumn.AutoFit: With .Cells(1):
..Offset(1).Select: .ColumnWidth = 60: End With
End With
ActiveWindow.FreezePanes = True
With Columns("B:B"): .WrapText = False: .ShrinkToFit = False:
..ColumnWidth = 60: .OutlineLevel = 2: End With
With ActiveSheet: .Outline.ShowLevels ColumnLevels:=1: Cells(1,
1).Select: End With
With Rows(1).EntireColumn.Font: .Name = "Arial": .Size = 8: End With
'Clean up
Set objUpdateSession = Nothing: Set objUpdateEntry = Nothing: Set
objUpdateSearcher = Nothing: Set UpdateHistory = Nothing
With Application: .ScreenUpdating = True: .Calculation =
xlCalculationAutomatic: End With
End Sub
The code is written as viewed in a wide screen, so watch out for the
line wraps. Note that I've included adding an outline for the
Description column so it's collapsed by default, and a fixed width for
Columns("A:B")
regards,
Garry
