![]() |
MS-DEFCON 2:
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it.
|
-
2000008: Microsoft Knowledge Base Article Lister (Microsoft Excel macros)
Home › Forums › Knowledge Base › 2000008: Microsoft Knowledge Base Article Lister (Microsoft Excel macros)
- This topic has 26 replies, 4 voices, and was last updated 2 years, 8 months ago.
Viewing 19 reply threads-
AuthorPosts
-
-
January 23, 2018 at 7:50 am #161420
MrBrian
AskWoody_MVPAKB2000008 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.
-
January 23, 2018 at 7:52 am #161421
MrBrian
AskWoody_MVPPublic 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
-
January 30, 2018 at 12:27 pm #163221
-
-
January 23, 2018 at 8:01 am #161426
MrBrian
AskWoody_MVPConstruction 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.
-
January 23, 2018 at 8:10 am #161430
MrBrian
AskWoody_MVPUsage:
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.
-
January 23, 2018 at 8:14 am #161433
-
January 23, 2018 at 9:00 am #161450
MrBrian
AskWoody_MVPIf 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.
-
January 23, 2018 at 9:12 am #161455
MrBrian
AskWoody_MVPThere is one sitemap that the code purposely doesn’t retrieve articles from: https://support.microsoft.com/sitemap_portal_0.
-
-
January 23, 2018 at 10:13 am #161479
-
January 23, 2018 at 11:39 am #161513
MrBrian
AskWoody_MVPRelated topic: How to list new or updated Microsoft Knowledge Base articles.
-
January 23, 2018 at 12:26 pm #161527
-
January 24, 2018 at 7:44 am #161789
MrBrian
AskWoody_MVPAn 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.
-
January 25, 2018 at 7:51 pm #162238
abbodi86
AskWoody_MVPI 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 dailythank you
-
January 26, 2018 at 12:41 pm #162410
-
January 28, 2018 at 8:58 am #162800
MrBrian
AskWoody_MVP -
January 28, 2018 at 9:27 am #162806
-
January 30, 2018 at 11:00 am #163185
MrBrian
AskWoody_MVPHere 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.
-
January 30, 2018 at 11:22 am #163193
-
March 16, 2018 at 12:34 pm #176209
MrBrian
AskWoody_MVPSome “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.
-
March 17, 2018 at 9:31 am #176467
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.     🙂
-
-
January 30, 2018 at 12:00 pm #163209
MrBrian
AskWoody_MVPHere 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.
-
January 30, 2018 at 1:18 pm #163235
-
February 23, 2018 at 4:12 pm #169858
MrBrian
AskWoody_MVPThe 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.
-
March 3, 2018 at 4:38 am #171916
MrBrian
AskWoody_MVP -
April 14, 2018 at 11:57 pm #184734
MrBrian
AskWoody_MVP
-
-
AuthorPosts
Viewing 19 reply threads -
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments. Click here for details and to sign up.
Search The Lounge
Recent Replies
anonymous on Windows 10 Latest Patch: KB 4598242
1 minute agoPaul T on New Computer – Windows 10 1903
8 minutes agoRick Corbett on New Computer – Windows 10 1903
35 minutes agoMele20 on Giving you the choice
38 minutes agoMele20 on Giving you the choice
43 minutes agoanonymous on Hard Drive at 100% usage
45 minutes agoanonymous on Hard Drive at 100% usage
45 minutes agoRick Corbett on Hard Drive at 100% usage
1 hour, 17 minutes agoBundaburra on Windows 10 Latest Patch: KB 4598242
1 hour, 21 minutes agoSusan Bradley on Giving you the choice
1 hour, 22 minutes agoanonymous on Giving you the choice
1 hour, 23 minutes agoanonymous on Giving you the choice
1 hour, 23 minutes agoKirsty on Giving you the choice
2 hours, 21 minutes agoECWS on New Computer – Windows 10 1903
2 hours, 52 minutes agoanonymous on Giving you the choice
3 hours, Just nowOscarCP on Apple to block sideloading iOS apps on M1
3 hours, 3 minutes agoBob99 on New Computer – Windows 10 1903
3 hours, 17 minutes agoOscarCP on Giving you the choice
3 hours, 26 minutes agoECWS on New Computer – Windows 10 1903
3 hours, 28 minutes agoECWS on New Computer – Windows 10 1903
3 hours, 37 minutes agoBob99 on New Computer – Windows 10 1903
3 hours, 37 minutes agoOscarCP on Giving you the choice
3 hours, 38 minutes agoBob99 on New Computer – Windows 10 1903
3 hours, 42 minutes agobratkinson on Hard Drive at 100% usage
3 hours, 42 minutes agoNathan Parker on Apple to block sideloading iOS apps on M1
3 hours, 42 minutes agoECWS on New Computer – Windows 10 1903
3 hours, 55 minutes agoBob99 on New Computer – Windows 10 1903
3 hours, 57 minutes agoanonymous on Hard Drive at 100% usage
4 hours, Just nowECWS on New Computer – Windows 10 1903
4 hours, 4 minutes agoBob99 on New Computer – Windows 10 1903
4 hours, 13 minutes ago
Recent Topics
-
Hard Drive at 100% usage
46 minutes ago
-
Checking e-mail attachments with VirusTotal
10 hours, 19 minutes ago
-
Giving you the choice
39 minutes ago
-
outlook 365 emails not queueing
14 hours, 30 minutes ago
-
Need a Great Rules Add-in for Outlook 2019
16 hours, 46 minutes ago
-
Mouse settings NOT preserved after reboot
11 hours, 53 minutes ago
-
Apple News Wrap Up: January 17, 2021
14 hours, 11 minutes ago
-
Tasks for the weekend – January 16, 2021
4 hours, 53 minutes ago
-
Use Word to Create Awesome Signs
1 day, 4 hours ago
-
Zero day Windows 10 bug
7 hours, 45 minutes ago
-
KB4598242 fails to install, in rollback loop 2021-01 cum upate
10 hours, 24 minutes ago
-
Updates paused but not?
12 hours, 17 minutes ago
-
Security update for Secure Boot DBX can be skipped (KB4535680)
15 hours, 4 minutes ago
-
Copying Folder Names Into Excel
9 hours, 17 minutes ago
-
The iPhone Companion
1 day, 19 hours ago
-
Apple Tips : 9 tips to take control of your privacy on iPhone
1 day, 20 hours ago
-
Internet drops briefly
1 day, 20 hours ago
-
A Zero-day Windows 10 bug corrupts your hard drive on seeing this file’s icon
1 day, 2 hours ago
-
Windows 7 ESU year two oddities
13 hours, 25 minutes ago
-
Color Rendering in PowerPoint 2019 Export to Video Function
2 days, 8 hours ago
-
WiFi Security Alerts after moving from 1909 to 2004
1 day, 13 hours ago
-
The MacBook Pro pre-2016 is back
1 day, 8 hours ago
-
What Is the Latest Stable Version of Windows 10?
1 day, 13 hours ago
-
OOMA vs MagicJack
1 day, 8 hours ago
-
Want 7GB of extra disk space?
2 days, 17 hours ago
-
Windows 10X and its purpose
2 days, 20 hours ago
-
Neither Exchange nor GMail will connect
2 days, 12 hours ago
-
Tweaking spreadsheet data
16 hours, 23 minutes ago
-
Windows 10 Latest Patch: KB 4598242
1 minute ago
-
Firefox Browser Replacement – Opera
3 days, 11 hours ago
Search for Topics
Recent blog posts
- Giving you the choice
- Tasks for the weekend – January 16, 2021
- Zero day Windows 10 bug
- Security update for Secure Boot DBX can be skipped (KB4535680)
- Windows 7 ESU year two oddities
- Attention partners: Microsoft really is coming for your clients this time
- January 2021 updates are here
- MS-DEFCON 2 – Get ready for January updates
Key Links
Copyright © 2004 – 2021 AskWoody Tech LLC. All rights reserved.