Hi since Microsoft now provide update information that you can script to download using Get-MsrcSecurityBulletinHtml and this gets you a large html document of the updates. I want to be able to import this html into excel and then perform some filtering so only the relevant operating systems or applications are in the excel document. I know I can do this manually in excel but would rather automate it.
This is the macro I recorded in excel but I am struggling to translate this to powershell. I can open a new excel document but not import the data. Any ideas ??
Sub html_import()
’
’ html_import Macro
’
’
With ActiveSheet.QueryTables.Add(Connection:= _
“URL;file:///C:/Temp/MSRC_CVEs2017-Jun.html”, Destination:=Range(“$A$1”))
.CommandType = 0
.Name = “MSRC_CVEs2017-Jun”
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Cells.Select
Selection.AutoFilter
ActiveSheet.Range(“$A$1:$G$10419”).AutoFilter Field:=1, Criteria1:= _
“=Microsoft Office 2016 (32-bit edition)”, Operator:=xlOr, Criteria2:= _
“=Windows 10 for x64-based Systems”
ActiveWindow.LargeScroll ToRight:=1
Range(“D1:D614”).Select
ActiveSheet.Range(“$A$1:$G$10419”).AutoFilter Field:=3, Criteria1:= _
“Critical”
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub
Hi Simon,
Did you consider filtering out the HTML file before importing it into Excel? In this case you would not need any Excel-macros and all filtering could be done in PowerShell script.
Hi and thanks for your quick reply. I have made a bit more progress by opening the html file directly in excel using
$SheetName= “test”
$oExcel = New-Object -com Excel.Application
$oExcel.Visible = $True
$Workbook = $oExcel.Workbooks.Open(“c:\temp\test.html”)
$worksheet = $Workbook.worksheets.item($SheetName)
I just need to select all cells that have data on them so I can apply the filter. As for applying the filter before using excel the html file is very long and would require a lot of parsing. Also this will be called from a form with check boxes on so the user can select the products that they are interesting in i.e. Windows 10, office 2013, windows server 2016 etc and those vales sored in a variable that can be passed to the filter.
ok further progress. I can select all of the cells and apply a filter but it is filtering out everything except Adobe Flash Player ?? If I swap Adobe flash player and windows server round in the $filter it gives me Windows Server 2016 so it would appear it is only using the second one. In the shell if I type $filter[0] it will return winddows server 2016 and $filter[1] Adobe flash
I have tried changing $Range.AutoFilter(1,$filter, 1,$filter) to $Range.AutoFilter(0,$filter, 1,$filter) but then it throws an error
Unable to get the AutoFilter property of the Range class
At line:13 char:1
$SheetName= “test”
$filter=@(“Windows Server 2016”,“Adobe flash player”)
$oExcel = New-Object -com Excel.Application
$oExcel.Visible = $True
$Workbook = $oExcel.Workbooks.Open(“c:\temp\test.html”)
$worksheet = $Workbook.worksheets.item($SheetName)
$last= $worksheet.UsedRange.specialCells(11).Address($false,$false)
$Range = $worksheet.Range(“A1:” + $last)
$Range.Select() | Out-Null
$worksheet.Application.ActiveWindow.SplitColumn = 0
$worksheet.Application.ActiveWindow.SplitRow = 1
$worksheet.Application.ActiveWindow.FreezePanes = $True
$Range.AutoFilter(1,$filter, 1,$filter)
Ok, I have something working 
It is amazing how the grey cells kick into action when you ask for help
This is what I have. I am still a bit confused on the auto filter as to why $Range.AutoFilter( 1,$filter[0],2,$filter[1]) puts a filter on column 1 where I would expect column 1 and 2 (but I wanted it on column 1 anyway so not going to worry about it too much. The second autofilter puts the filter on column 3 as required hence the confusion on the first one. all I need to do to finish this bit is build the filer up from check box selections on the form and pass the results to the $filter… Thanks again to anyone who read this post and I hope that my findings assist someone else.
$SheetName= “test”
$filter=@(“Windows Server 2016”,“Windows 10 for 32-bit Systems”,“Critical”)
$oExcel = New-Object -com Excel.Application
$oExcel.Visible = $True
$Workbook = $oExcel.Workbooks.Open(“c:\temp\test.html”)
$worksheet = $Workbook.worksheets.item($SheetName)
$last= $worksheet.UsedRange.specialCells(11).Address($false,$false)
$Range = $worksheet.Range(“A1:” + $last)
$Range.Select() | Out-Null
$worksheet.Application.ActiveWindow.SplitColumn = 0
$worksheet.Application.ActiveWindow.SplitRow = 1
$worksheet.Application.ActiveWindow.FreezePanes = $True
$Range.AutoFilter( 1,$filter[0],2,$filter[1])
$Range.AutoFilter( 3,$filter[2])