{"id":11290,"date":"2022-09-26T20:48:25","date_gmt":"2022-09-26T15:18:25","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/"},"modified":"2022-09-26T20:48:25","modified_gmt":"2022-09-26T15:18:25","slug":"solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/","title":{"rendered":"[Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-40965328\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"40965328\" data-parentid=\"40931431\" data-score=\"0\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>Here, this will do what you want.<\/p>\n<pre><code>Option Explicit\nSub FileListingAllFolder()\n\nDim pPath As String\nDim FlNm As Variant\nDim ListFNm As New Collection ' create a collection of filenames\n\nDim OWb As Workbook\nDim ShtCnt As Integer\nDim Sht As Integer\n\nDim MWb As Workbook\nDim MWs As Worksheet\nDim i As Integer\n\n' Open folder selection\nWith Application.FileDialog(msoFileDialogFolderPicker)\n    .Title = \"Select a Folder\"\n    .AllowMultiSelect = False\n    If .Show &lt;&gt; -1 Then GoTo NextCode\n    pPath = .SelectedItems(1)\nEnd With\n\nApplication.WindowState = xlMinimized\nApplication.ScreenUpdating = False\n\n' Create master workbook with single sheets\nSet MWb = Workbooks.Add(1)\nMWb.Sheets(1).Name = \"Result\"\nSet MWs = MWb.Sheets(\"Result\")\nCells(1, 1) = \"No.\"\nCells(1, 2) = \"Sheet Name\"\nCells(1, 3) = \"File Name\"\nCells(1, 4) = \"Link\"\ni = 2\n\n' Filling a collection of filenames (search Excel files including subdirectories)\nCall FlSrch(ListFNm, pPath, \"*.xls\", True)\n\n' Print list to immediate debug window and as a message window\nFor Each FlNm In ListFNm ' cycle for list(collection) processing\n\n    'Start Processing here\n    Set OWb = Workbooks.Open(FlNm)\n    ShtCnt = ActiveWorkbook.Sheets.Count\n    For Sht = 1 To ShtCnt\n        MWs.Cells(i, 1) = i - 1\n        MWs.Cells(i, 2) = Sheets(Sht).Name\n        MWs.Cells(i, 3) = OWb.Name\n        MWs.Cells(i, 4).Formula = \"=HYPERLINK(\"\"\" &amp; FlNm &amp; \"\"\",\"\"Click Here\"\")\"\n        i = i + 1\n    Next Sht\n    'End file processing file\n    OWb.Close False\nNext FlNm\n\n' Print to immediate debug window and message if no file was found\nIf ListFNm.Count = 0 Then\n    Debug.Print \"No file was found !\"\n    MsgBox \"No file was found !\"\n    MWb.Close False\n    End\nEnd If\n\nMWb.Activate\nMWs.Activate\nCells.Select\nSelection.EntireColumn.AutoFit\nRange(\"A1\").Select\nApplication.ScreenUpdating = True\nApplication.WindowState = xlMaximized\n\nEnd\n\nNextCode:\nMsgBox \"You Click Cancel, and no folder selected!\"\n\nEnd Sub\n\nPrivate Sub FlSrch(pFnd As Collection, pPath As String, pMask As String, pSbDir As Boolean)\n\nDim flDir As String\nDim CldItm As Variant\nDim sCldItm As New Collection\n\n' Add backslash at the end of path if not present\npPath = Trim(pPath)\nIf Right(pPath, 1) &lt;&gt; \"\\\" Then pPath = pPath &amp; \"\\\"\n\n' Searching files accordant with mask\nflDir = Dir(pPath &amp; pMask)\n    Do While flDir &lt;&gt; \"\"\n        pFnd.Add pPath &amp; flDir 'add file name to list(collection)\n        flDir = Dir ' next file\n    Loop\n\n' Procedure exiting if searching in subdirectories isn't enabled\nIf Not pSbDir Then Exit Sub\n\n' Searching for subdirectories in path\nflDir = Dir(pPath &amp; \"*\", vbDirectory)\n    Do While flDir &lt;&gt; \"\"\n\n        ' Add subdirectory to local list(collection) of subdirectories in path\n        If flDir &lt;&gt; \".\" And flDir &lt;&gt; \"..\" Then If ((GetAttr(pPath &amp; flDir) And _\n        vbDirectory) = 16) Then sCldItm.Add pPath &amp; flDir\n        flDir = Dir 'next file\n    Loop\n\n' Subdirectories list(collection) processing\nFor Each CldItm In sCldItm\n    Call FlSrch(pFnd, CStr(CldItm), pMask, pSbDir) ' Recursive procedure call\nNext\n\nEnd Sub\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\"><\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Here, this will do what you want. Option Explicit Sub FileListingAllFolder() Dim pPath As String Dim FlNm As Variant Dim ListFNm As New Collection &#8216; create a collection of filenames Dim OWb As Workbook Dim ShtCnt As Integer Dim Sht As Integer Dim MWb As Workbook Dim MWs As Worksheet Dim i As Integer &#8230; <a title=\"[Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/\" aria-label=\"More on [Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[400,1046,1971,401],"class_list":["post-11290","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-excel-formula","tag-macros","tag-vba"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Here, this will do what you want. Option Explicit Sub FileListingAllFolder() Dim pPath As String Dim FlNm As Variant Dim ListFNm As New Collection &#039; create a collection of filenames Dim OWb As Workbook Dim ShtCnt As Integer Dim Sht As Integer Dim MWb As Workbook Dim MWs As Worksheet Dim i As Integer ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-26T15:18:25+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet\",\"datePublished\":\"2022-09-26T15:18:25+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/\"},\"wordCount\":39,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"excel\",\"excel-formula\",\"macros\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/\",\"name\":\"[Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-09-26T15:18:25+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet - JassWeb","og_description":"[ad_1] Here, this will do what you want. Option Explicit Sub FileListingAllFolder() Dim pPath As String Dim FlNm As Variant Dim ListFNm As New Collection ' create a collection of filenames Dim OWb As Workbook Dim ShtCnt As Integer Dim Sht As Integer Dim MWb As Workbook Dim MWs As Worksheet Dim i As Integer ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/","og_site_name":"JassWeb","article_published_time":"2022-09-26T15:18:25+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet","datePublished":"2022-09-26T15:18:25+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/"},"wordCount":39,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","excel-formula","macros","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/","url":"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/","name":"[Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-26T15:18:25+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-comparing-sheetnames-of-different-excel-workbooks-and-storing-the-result-in-the-third-sheet\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Comparing Sheetnames of different excel workbooks and Storing the result in the third sheet"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/11290","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=11290"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/11290\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=11290"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=11290"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=11290"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}