News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • 2000008: Microsoft Knowledge Base Article Lister (Microsoft Excel macros)

    Posted on MrBrian Comment on the AskWoody Lounge

    Home Forums Knowledge Base 2000008: Microsoft Knowledge Base Article Lister (Microsoft Excel macros)

    Viewing 19 reply threads
    • Author
      Posts
      • #161420 Reply
        MrBrian
        AskWoody_MVP

        AKB2000008 Microsoft Knowledge Base Article Lister (Microsoft Excel macros)

        By @MrBrian

        Published 23 February 2018 rev 1.3

        This topic presents Microsoft Excel macros that list all Microsoft Knowledge Base articles.

        A subset of the full list is available at https://www.askwoody.com/kbnew/.

        License:

        MIT License

        Copyright (c) 2018 MrBrian

        Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

        The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

        THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

        5 users thanked author for this post.
      • #161421 Reply
        MrBrian
        AskWoody_MVP
        Public Sub GetURLs()
            Dim URLSheet As Worksheet
            Dim URLSheetRow As Long
            Dim URLSheetRange As Range
            Dim SitemapSheet As Worksheet
            Dim SitemapSheetLastRow As Long
            Dim SitemapSheetRow As Long
            Dim TempXMLSheet As Worksheet
            Dim TempXMLSheetLastRow As Long
            Dim TempXMLSheetRow As Long
            Dim TempXMLSheetCellItemValue As Variant
            
           
            GetSitemaps
            
            Set URLSheet = Sheets("URLs")
            Set SitemapSheet = Sheets("Sitemaps")
            Set TempXMLSheet = Sheets("TempXML")
            
            While ActiveWorkbook.XmlMaps.Count > 0
                ActiveWorkbook.XmlMaps(1).Delete
            Wend
            
            URLSheet.Columns(1).Clear
            URLSheet.Columns(2).Clear
            URLSheet.Columns(3).Clear
            TempXMLSheet.Columns(1).Delete
            TempXMLSheet.Columns(1).Delete
            URLSheetRow = 1
            SitemapSheetLastRow = SitemapSheet.Range("A" & Rows.Count).End(xlUp).Row
            
            For SitemapRow = 1 To SitemapSheetLastRow
                ActiveWorkbook.XmlImport SitemapSheet.Cells.Item(SitemapRow, 1).Value, Nothing, True, TempXMLSheet.Range("A1")
                TempXMLSheetLastRow = TempXMLSheet.Range("A" & Rows.Count).End(xlUp).Row
                
                For TempXMLSheetRow = 1 To TempXMLSheetLastRow
                    TempXMLSheetCellItemValue = TempXMLSheet.Cells.Item(TempXMLSheetRow, 1).Value
                    
                    If LCase(Left(CStr(TempXMLSheetCellItemValue), 4)) = "http" Then
                        URLSheet.Cells.Item(URLSheetRow, 1).Value = TempXMLSheetCellItemValue
                        URLSheet.Cells.Item(URLSheetRow, 2).Value = TempXMLSheet.Cells.Item(TempXMLSheetRow, 2).Value
                        URLSheetRow = URLSheetRow + 1
                    End If
                Next TempXMLSheetRow
            
                While ActiveWorkbook.XmlMaps.Count > 0
                    ActiveWorkbook.XmlMaps(1).Delete
                Wend
            
                TempXMLSheet.Columns(1).Delete
                TempXMLSheet.Columns(1).Delete
            Next SitemapRow
            
            Set URLSheetRange = URLSheet.Range("A1", "B" & CStr(URLSheetRow - 1))
            URLSheetRange.Sort URLSheet.Range("B1")
        End Sub
        
        Public Sub GetSitemaps()
            Dim SitemapSheet As Worksheet
            Dim SitemapSheetRow As Long
            Dim TempXMLSheet As Worksheet
            Dim TempXMLSheetLastRow As Long
            Dim TempXMLSheetRow As Long
            
            
            Set SitemapSheet = Sheets("Sitemaps")
            Set TempXMLSheet = Sheets("TempXML")
            
            While ActiveWorkbook.XmlMaps.Count > 0
                ActiveWorkbook.XmlMaps(1).Delete
            Wend
        
            TempXMLSheet.Columns(1).Delete
            TempXMLSheet.Columns(1).Delete
            SitemapSheet.Columns(1).Clear
            
            ActiveWorkbook.XmlImport "https://support.microsoft.com/sitemap_index", Nothing, True, TempXMLSheet.Range("A1")
            TempXMLSheetLastRow = TempXMLSheet.Range("A" & Rows.Count).End(xlUp).Row
            SitemapSheetRow = 1
            
            For TempXMLSheetRow = 2 To TempXMLSheetLastRow
                If InStr(TempXMLSheet.Cells.Item(TempXMLSheetRow, 1).Value, "en-us_help") Then
                    SitemapSheet.Cells.Item(SitemapSheetRow, 1).Value = TempXMLSheet.Cells.Item(TempXMLSheetRow, 1).Value
                    SitemapSheetRow = SitemapSheetRow + 1
                End If
            Next TempXMLSheetRow
            
            While ActiveWorkbook.XmlMaps.Count > 0
                ActiveWorkbook.XmlMaps(1).Delete
            Wend
        
            TempXMLSheet.Columns(1).Delete
            TempXMLSheet.Columns(1).Delete
        End Sub
        
        Public Sub GetTitlesOfSelectedURLs()
            Dim URLSheet As Worksheet
            Dim XMLHttpRequest As Variant
            Dim PosLeft As Long
            Dim PosRight As Long
            Dim Pos As Long
            Dim Title As String
            Dim ArticleNumber As String
            Dim URLSheetRow As Long
            
            
            Set URLSheet = Sheets("URLs")
            URLSheet.Activate
            If Selection Is Nothing Then Exit Sub
            If TypeName(Selection) <> "Range" Then Exit Sub
            Set XMLHttpRequest = CreateObject("MSXML2.XMLHTTP")
            
            For URLSheetRow = Selection.Row To Selection.Row + Selection.Rows.Count - 1
                Pos = InStr(1, URLSheet.Cells.Item(URLSheetRow, 1), "/help/", vbTextCompare)
                
                If Pos > 0 Then
                    Pos = Pos + 6
                    ArticleNumber = Mid(URLSheet.Cells.Item(URLSheetRow, 1), Pos)
                    Pos = InStr(ArticleNumber, "/")
                    If Pos > 0 Then ArticleNumber = Mid(ArticleNumber, 1, Pos - 1)
                End If
                
                PosRight = 0
                PosLeft = 0
                Title = ""
                XMLHttpRequest.Open "GET", "https://support.microsoft.com/app/content/api/content/help/en-us/" & ArticleNumber, False
                XMLHttpRequest.send
                
                While XMLHttpRequest.readyState <> 4
                    DoEvents
                Wend
            
                PosRight = InStr(1, XMLHttpRequest.responseText, Chr(34) & "titlelower" & Chr(34) & ":", vbTextCompare)
                
                If PosRight > 0 Then
                    PosLeft = InStrRev(XMLHttpRequest.responseText, Chr(34) & "title" & Chr(34) & ":", PosRight, vbTextCompare)
                    If PosLeft > 0 Then PosLeft = PosLeft + 8
                End If
                
                If PosLeft > 0 Then
                    Title = Mid(XMLHttpRequest.responseText, PosLeft, PosRight - PosLeft)
                    Pos = InStr(Title, Chr(34))
                    If Pos > 0 Then Title = Mid(Title, Pos + 1)
                    Pos = InStrRev(Title, Chr(34))
                    If Pos > 0 Then Title = Mid(Title, 1, Pos - 1)
                    Title = Replace(Title, "\" & Chr(34), Chr(34))
                End If
                
                URLSheet.Cells.Item(URLSheetRow, 3) = Title
            Next URLSheetRow
        End Sub
        
        Public Sub MakeHyperlinksOfSelectedURLs()
            Dim URLSheet As Worksheet
            
            
            Set URLSheet = Sheets("URLs")
            URLSheet.Activate
            If Selection Is Nothing Then Exit Sub
            If TypeName(Selection) <> "Range" Then Exit Sub
            
            For URLSheetRow = Selection.Row To Selection.Row + Selection.Rows.Count - 1
                On Error Resume Next
                URLSheet.Hyperlinks.Add URLSheet.Cells.Item(URLSheetRow, 1), URLSheet.Cells.Item(URLSheetRow, 1).Value
                On Error GoTo 0
            Next URLSheetRow
        End Sub
        
        
        4 users thanked author for this post.
        • #163221 Reply
          MrBrian
          AskWoody_MVP

          Note: this isn’t the latest version of the code.

      • #161426 Reply
        MrBrian
        AskWoody_MVP

        Construction of Excel workbook:

        1. Create a new Excel workbook. Save it as file type Excel Macro-Enabled Workbook, which has a .xlsm extension.

        2. Make sure the Developer tab is present. See this video for details: https://www.youtube.com/watch?v=58Ud6Ej5QSw.

        3. Create worksheets named URLs, Sitemaps, and TempXML.

        4. Create a code module. See video in step 2 for details.

        5. Copy the code from my last post to the code module.

        6. Save the Excel workbook.

        4 users thanked author for this post.
      • #161430 Reply
        MrBrian
        AskWoody_MVP

        Usage:

        Macro GetURLs gets the URLs of all English-language Microsoft Knowledge Base articles and puts them into worksheet URLs. It takes about a minute to run on my computer.

        After you have used macro GetURLs, optionally you can select row(s) in worksheet URLs (which columns doesn’t matter), and then use macro GetTitlesOfSelectedURLs to get the article titles of the selected rows, or macro MakeHyperlinksOfSelectedURLs to make clickable hyperlinks in the selected rows.

        To run a macro, see video https://www.youtube.com/watch?v=58Ud6Ej5QSw.

        5 users thanked author for this post.
      • #161433 Reply
        MrBrian
        AskWoody_MVP

        Suggestion:

        For convenience, in a separate worksheet, create three buttons (using Insert on Developer tab), one for each of the macros mentioned in my last post. Assign a macro to each of the buttons.

        1 user thanked author for this post.
      • #161450 Reply
        MrBrian
        AskWoody_MVP

        If anyone is curious how the list of articles is retrieved, the starting point is the sitemaps listed at https://support.microsoft.com/sitemap_index. Article info is then retrieved from each sitemap with “en-us_help” in the URL. Example of two sitemaps: https://support.microsoft.com/sitemap_en-us_help_57 and https://support.microsoft.com/sitemap_en-us_help_60. Lastly, the retrieved article info is sorted by the date (“lastmod”) column.

        1 user thanked author for this post.
      • #161479 Reply
        woody
        Da Boss

        This is going in my top drawer.

      • #161513 Reply
        MrBrian
        AskWoody_MVP
      • #161527 Reply
        abbodi86
        AskWoody_MVP

        Thank you very much

        this is seems way over my basic Excel experience, it will take me some time to absorb 🙂

      • #161789 Reply
        MrBrian
        AskWoody_MVP

        An interesting tidbit: Within the past few days, Microsoft made a change that resulted in oldest article “lastmod” dates of 1999. Before a few days ago, the oldest “lastmod” date had been January 1, 2017. Also within the past few days, some “lastmod” dates now are “N/A”, whereas before that did not happen at all.

        2 users thanked author for this post.
      • #162238 Reply
        abbodi86
        AskWoody_MVP

        I created a few .CSV files with KB lists that can be opened with Excel or other programs. I will upload them to a file hosting site if anybody is interested in them.

        Anything you see helpuful, will be helpful 🙂

        for me, “Data > Import from web” is vey simple and helpful, i just update the content whevever i have time to check
        index 80 & 81 seems the currently active daily

        thank you

      • #162410 Reply
        MrBrian
        AskWoody_MVP

        Here is a .csv file (zipped): Microsoft Knowledge Base Articles as of January 25, 2018.csv. All KB articles – including titles – as of January 25, 2018 are listed.

        3 users thanked author for this post.
      • #162800 Reply
        MrBrian
        AskWoody_MVP
      • #162806 Reply
        MrBrian
        AskWoody_MVP

        I noticed that the article title returned by my macro sometimes doesn’t exactly match the article’s title (example). I know what the issue is, and will try to fix it sometime soon.

        3 users thanked author for this post.
      • #163185 Reply
        MrBrian
        AskWoody_MVP

        Here is a .csv file (zipped): Microsoft Knowledge Base Articles as of January 30, 2018.csv. All KB articles – including titles – as of January 30, 2018 are listed.

        There were two changes to the macro that gets the titles:

        1. Previously, the titles came from field Details.Title. Now, the titles come from field Details.Heading if it’s not blank, with a fallback to using field Details.Title otherwise. Reason for change: Details.Title sometimes isn’t the same as the actual title.

        2. Microsoft’s “FAST PUBLISH” articles now are noted with ***FAST PUBLISH*** added to the beginning of the title. “FAST PUBLISH” articles seem to almost always be associated with Microsoft’s Dynamics products, and therefore this can help you avoid Microsoft’s Dynamics articles if you aren’t interested in them.

        If anyone wants me to post the updated macro, please reply to this post.

         

        2 users thanked author for this post.
        • #163193 Reply
          woody
          Da Boss

          Please do post the changes!

          I wonder if you should start a Github account?

        • #176209 Reply
          MrBrian
          AskWoody_MVP

          Some “FAST PUBLISH” articles don’t involve Microsoft Dynamics – see https://www.askwoody.com/forums/topic/kbnew-updated/#post-163756.

          1 user thanked author for this post.
        • #176467 Reply
          walker
          AskWoody Lounger

          @Mr.Brian:   All of this is waaaaay over my head, however I try to read everything anyway, hoping that at “some point”, I will begin to understand more.   Thank you, as always, for everything you post on these topics.      Your expertise, knowledge, and sharing of the tremendous wealth of knowledge which you possess is most gratefully appreciated.      🙂

          2 users thanked author for this post.
      • #163209 Reply
        MrBrian
        AskWoody_MVP

        Here is a link to the latest version of the macros: https://pastebin.com/sJPKT3U0. If I recall correctly, the only macro that changed from the first version is GetTitlesOfSelectedURLs. See https://www.askwoody.com/forums/topic/2000008-microsoft-knowledge-base-article-lister-microsoft-excel-macros/#post-163185 for a list of the changes made to macro GetTitlesOfSelectedURLs.

        1 user thanked author for this post.
      • #163235 Reply
        MrBrian
        AskWoody_MVP

        Microsoft has deleted at least approximately 1,200 articles in the past 5 days!

        2 users thanked author for this post.
      • #169858 Reply
        MrBrian
        AskWoody_MVP

        The initial post was modified to add a link to https://www.askwoody.com/kbnew/, and remove the link to Woody’s online spreadsheet. Announcement: KBNew – the list of recently updated MS KB articles – is now on the AskWoody site.

        2 users thanked author for this post.
      • #171916 Reply
        MrBrian
        AskWoody_MVP
      • #184734 Reply
        MrBrian
        AskWoody_MVP

        Microsoft has been deleting a lot of articles lately! There are now approximately 59,000 articles. At the beginning of 2018, there were approximately 72,100 articles.

        2 users thanked author for this post.
        • #185451 Reply
          abbodi86
          AskWoody_MVP

          They ran out of datacenters space or what?

          at least they could bring back the old KB links for those articles

          2 users thanked author for this post.
        • #187466 Reply
          MrBrian
          AskWoody_MVP

          Now there are 65,661 articles.

    Viewing 19 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: 2000008: Microsoft Knowledge Base Article Lister (Microsoft Excel macros)

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.