Windows update
in [Programming]
|
Prev: Call random UserForm from list
Next: Insert a custom function in a cell by runnig a sub or through a macro.
From: AltaEgo on 14 May 2010 20:31 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)? -- Steve
From: GS on 15 May 2010 02:01 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
From: Jim Cone on 15 May 2010 13:47 This link may work better... http://www.humyo.com/10358029/ExtrasForExcelRelease130Trial.zip?a=08qTQN_M2UA Jim Cone
From: AltaEgo on 15 May 2010 20:18
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. -- Steve "GS" <GS(a)discussions.microsoft.com> wrote in message news:#JsNPQ$8KHA.4600(a)TK2MSFTNGP02.phx.gbl... > 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 > > |