Windows update

  • Thread starter Thread starter AltaEgo
  • Start date Start date
A

AltaEgo

Hi all

I plan to fix the code at the link below and use it to audit and analyze
windows updates on a large fleet of laptops (around 1000).

http://www.vbaexpress.com/kb/getarticle.php?kb_id=806

My plan is to store everything in a table but it would be nice to retain raw
data each time a laptop is audited (replace if one exists). How will Excel
handle that many sheets (Atom processor; 2ghz Ram; Windows 7)?
 
I just ran the code from your link and it ran without a hitch.
It wrote six columns with 700 rows.
It looks like the Description column can be omitted. It has the most text and provides no real info.
That would lighten the load.
I have one personal code workbook with 200 sheets and it performs without problems.
However, a 1000 sheet workbook would not be my choice.
Separate workbooks for each department or location might be the way to go. (with backups)
For what it is worth, some experienced Excel users have posted that an Excel workbook will crap out at about 5000 blank
sheets.

Also, I would guess another issue is going to be the addition of a linked table of contents in each workbook and/or
sorting the sheets. (see the link below my signature.)
--
Jim Cone
Portland, Oregon USA
Extras for Excel (add-in) - 3 week trial...
http://www.humyo.com/10358029/ExtrasForXLRelease130Trial.zip?a=yEaB5gsTRiY




"AltaEgo" <Somewhere@NotHere>
wrote in message Hi all
I plan to fix the code at the link below and use it to audit and analyze
windows updates on a large fleet of laptops (around 1000).

http://www.vbaexpress.com/kb/getarticle.php?kb_id=806

My plan is to store everything in a table but it would be nice to retain raw
data each time a laptop is audited (replace if one exists). How will Excel
handle that many sheets (Atom processor; 2ghz Ram; Windows 7)?
 
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
 
Thank you. I did some work already on the original code to clean up the poor
sheet formatting section and run results to array for paste to sheet. This
sped things up a lot. Also, if anyone has that code, as a matter of urgency,
add Activesheet.Cells.Clear! While I was more interested in Excel's sheet
capacity than help with the code, I will have a look at your code to see how
you improved it.

Thank you Jim

- I had already made changes to extract KB number and categorize updates by
type rather than include all of Microsoft's detail.
- Your comments helped me decide to use a table rather than sheets.
Splitting by department is feasible but I like to remove as much of the
potential for incorrect human input as possible. Adding the serial number
will take care of unit identification.
 
And to confirm your decision, this excerpt from a May 16, 2010 post in microsoft.public.excel.programming...
"We have an app which creates large Excel workbooks.
For example, one workbook has 1,000 worksheets. In other cases there are fewer worksheets but
the Excel file can be 80 MB or larger. Since this sometimes crashes Excel 2007 and 2003..."
--
Jim Cone
Portland, Oregon USA
(Linked table of contents, sheet sorting & more... http://tinyurl.com/ExtrasForXL )




"AltaEgo" <Somewhere@NotHere>
wrote in message Thank you. I did some work already on the original code to clean up the poor
sheet formatting section and run results to array for paste to sheet. This
sped things up a lot. Also, if anyone has that code, as a matter of urgency,
add Activesheet.Cells.Clear! While I was more interested in Excel's sheet
capacity than help with the code, I will have a look at your code to see how
you improved it.

Thank you Jim

- I had already made changes to extract KB number and categorize updates by
type rather than include all of Microsoft's detail.
- Your comments helped me decide to use a table rather than sheets.
Splitting by department is feasible but I like to remove as much of the
potential for incorrect human input as possible. Adding the serial number
will take care of unit identification.
 
Back
Top