Enumerate Hot Fixes [to Excel]

[stanl]

UPDATED: Adds Hyperlinks to Column A

Code (WINBATCH) Select Expand



;Winbatch 2018B - Enumerate Hotfixes
;Stan Littlefield 2/24/2019
;============================================================================================
IntControl(73,1,0,0,0)
Gosub udfs
oXL=0
oWMi=0
oHotfix=0
strComputer = "."
oWMI = GetObject( "winmgmts:\\" : strComputer : "\root\cimv2")
WQL = 'SELECT * FROM Win32_QuickFixEngineering'
oHotfix = oWMI.ExecQuery(WQL)


Output ="Caption,Description,InstallDate,Name,Status,CSName,FixComments,HotFixID,InstalledBy,InstalledOn,ServicePackInEffect":@CRLF
Output=StrReplace(Output,",",@TAB)




ForEach f in oHotfix
   Output=Output:f.Caption:@TAB
   Output=Output:f.Description:@TAB
   Output=Output:f.InstallDate:@TAB
   Output=Output:f.Name:@TAB
   Output=Output:f.Status:@TAB
   Output=Output:f.CSName:@TAB
   Output=Output:f.FixComments:@TAB
   Output=Output:f.HotFixID:@TAB
   Output=Output:f.InstalledBy:@TAB
   Output=Output:f.InstalledOn:@TAB
   Output=Output:f.ServicePackInEffect:@CRLF
Next


;Output could be (1) written to file (2) WB Reportview
;This script is using Excel
BoxOpen("Please Wait","Sending Hotfix Info To Excel")
ToExcel()


oHotfix=0
oWMI=0


Exit


:WBERRORHANDLER
oHotfix=0
oWMI=0
ErrorProcessing(0,1,0,0)
Exit


:udfs
#DefineSubRoutine ToExcel()
IntControl(73,1,0,0,0)
ClipPut(Output)
oXL = CreateObject("Excel.Application")
oXL.WorkBooks.Add()
oXL.Visible          = 1
oXL.ScreenUpdating   = 1   
oXL.UserControl      = 1
oXL.DisplayAlerts    = 0
BoxShut()
oWS=oXL.ActiveWorkbook.WorkSheets("Sheet1")
oWS.Activate()
oWS.Range("A1").Select()
oXL.Selection.PasteSpecial(::Paste=-4104,Operation=-4142)
oXL.CutCopyMode = @False
oWS.Cells.EntireColumn.AutoFit
oWS.Cells(1,1).Select()
LastRow = oWS.UsedRange.Rows(oWS.UsedRange.Rows.Count).Row


;Create Hyperlinks
For i = 2 to LastRow
   rng="A":i
   v=oWS.Range("%rng%").Value
   oWS.Range("%rng%").Select()
   oWS.Hyperlinks.Add(::Anchor=oXL.Selection,Address="%v%",TextToDisplay="%v%")
Next




oWS=0
oXL=0
Return(1)


:WBERRORHANDLER
oWS=0
oXL=0
oHotfix=0
oWMI=0
ErrorProcessing(0,1,0,0)
Exit


#EndSubRoutine


#DefineSubroutine ErrorProcessing(deleteIni,showerr,logfile,Err_Array)
If Vartype(Err_Array) ==256
   WbError = Err_Array[0]
   wberrorhandlerline = Err_Array[1] 
   wberrorhandleroffset = Err_Array[2]   
   wberrorhandlerassignment = Err_Array[3] 
   wberrorhandlerfile = Err_Array[4]
   wberrortextstring = Err_Array[5] 
   wberroradditionalinfo = Err_Array[6] 
   wberrorinsegment = Err_Array[7]   
Else
   WbError = LastError()
Endif
WbTextcode = WbError
If WbError==1668||WbError==2669||WbError==3670
   WbError = ItemExtract(1,IntControl(34,-1,0,0,0),":")
   WbTextcode = -1
EndIf
WbErrorString = IntControl(34,WbTextcode,0,0,0)
WbErrorDateTime = TimeYmdHms()
If deleteIni
   WbErrorFile = StrCat(ShortCutDir( 'AppData', 0, 0 ),'\WinBatch\Settings\')
   If ! DirExist(WbErrorFile) Then WbErrorFile = DirWindows(0)
   WbErrorFile = StrCat(WbErrorFile,"WWWBATCH.INI")
   FileDelete(WbErrorFile)
   ;IniWritePvt(WbErrorDateTime,"CurrentScript",WbErrorHandlerFile      ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ErrorValue"   ,WbError                 ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ErrorString"  ,WbErrorString           ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ScriptLine"   ,WbErrorHandlerLine      ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ScriptOffset" ,WbErrorHandlerOffset    ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"VarAssignment",WbErrorHandlerAssignment,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"VarInSegment" ,WbErrorInSegment,WbErrorFile)
   IniWritePvt("","","",WbErrorFile)
Endif 
WbErrorMsgText = StrCat(WbErrorDateTime,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Current Script: ",WbErrorHandlerFile,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error# [",WbError,"]",@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error Text: ",wberrortextstring,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"[Extended Information] ",wberroradditionalinfo,@CRLF,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"On Line:",@CRLF,WbErrorHandlerLine,@CRLF)
;WbErrorMsgText = StrCat(WbErrorMsgText,"Offset: ",WbErrorHandlerOffset,@CRLF)
If (WbErrorHandlerAssignment>"") Then %WbErrorHandlerAssignment% = "UNKNOWN"
WbErrorMsgText = StrCat(WbErrorMsgText,"Assignment/Variable: ",WbErrorHandlerAssignment,@CRLF)
If (WbErrorInSegment>"") Then WbErrorMsgText = StrCat(WbErrorMsgText,"In UDF/UDS: ",WbErrorInSegment,@CRLF)
If logfile
   cSep = StrCat(StrFill("=",50),@CRLF)
   cLogFile = StrCat(dirscript(),"log.err")
   If ! FileExist(cLogFile) Then FilePut(cLogFile,StrCat("Error Log",@CRLF,cSep))
   FilePut(cLogFile,StrCat(FileGet(cLogFile),WbErrorMsgText,cSep))
   display(2,"An Error Occured",StrCat("written to ",cLogFile))
Else
   If showerr
      WbErrorMsgText = StrCat(WbErrorMsgText,"[THIS ERROR NOT WRITTEN TO LOG FILE]",@CRLF)
      Message("An Error Was Encountered",WbErrorMsgText)
   Endif
Endif
Return(1)
#EndSubroutine


Return



[stanl]

Oh, and if anyone has looked into the code the hyperlink portion may suggest a terrible use of %% substitution as there are frequent reminders as to how it goes awry. But, it works in this case suggesting only laziness.

[td]

Maybe you deserve the "selective use in a knowledgable hand" exception?

[kdmoyers]

  :)