• 2000008: Microsoft Knowledge Base Article Lister (Microsoft Excel macros)

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

    Author
    Topic
    #161420

    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.
    Viewing 18 reply threads
    Author
    Replies
    • #161421
      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.
    • #161426

      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

      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

      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

      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

      This is going in my top drawer.

    • #161513
    • #161527

      Thank you very much

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

    • #161789

      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

      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

      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
    • #162806

      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

      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.
    • #163209

      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

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

      2 users thanked author for this post.
    • #169858

      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
    • #184734

      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.
    Viewing 18 reply threads
    Reply To: 2000008: Microsoft Knowledge Base Article Lister (Microsoft Excel macros)

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

    Your information: