Import an HTML file into excel

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Simon B Simon B 3 weeks, 4 days ago.

  • Author
    Posts
  • #74119
    Profile photo of Simon B
    Simon B
    Participant

    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

  • #74123
    Profile photo of Evgeny Fedorov
    Evgeny Fedorov
    Participant

    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.

  • #74126
    Profile photo of Simon B
    Simon B
    Participant

    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.

  • #74129
    Profile photo of Simon B
    Simon B
    Participant

    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
    + $Range.AutoFilter(0,$filter, 1,$filter)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

    $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)

  • #74143
    Profile photo of Simon B
    Simon B
    Participant

    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])

You must be logged in to reply to this topic.