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.

Microfix, abbodi86, Elly, woody

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.

Microfix, abbodi86, Elly, woody

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.

Microfix, AusJohn, abbodi86, Elly, woody

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.

woody

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.

woody

Related topic: How to list new or updated Microsoft Knowledge Base articles.

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.

HiFlyer, woody

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.

Elly, woody, abbodi86

KBNew: New articles in Microsoft’s Knowledge Base

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.

HiFlyer, woody, walker

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.

woody, abbodi86

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.

woody

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

2 users thanked author for this post.

walker, woody

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.

walker, Elly

Related: Microsoft makes web feeds available for the Microsoft Knowledge Base.

.

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.

walker, abbodi86