locked
Import html document into excel RRS feed

  • Question

  • 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

    Monday, July 3, 2017 10:04 AM

All replies

  • Sorry but this is not an Excel forum.  You want Excel VBA forum.


    \_(ツ)_/

    Monday, July 3, 2017 11:16 AM