{"id":257,"date":"2008-11-14T08:53:34","date_gmt":"2008-11-14T08:53:34","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?page_id=257"},"modified":"2008-11-14T08:53:34","modified_gmt":"2008-11-14T08:53:34","slug":"find_links_foldervbs","status":"publish","type":"page","link":"https:\/\/www.wapshere.com\/missmiis\/find_links_foldervbs","title":{"rendered":"find_links_folder.vbs"},"content":{"rendered":"<pre>' find_links_folder.vbs\r\n' Written by Carol Wapshere, 2008\r\n' Search for .xls files and find links to other documents.<\/pre>\n<pre>Const REPORT_DIR = \"C:\\ExcelLinks\\\"   'The folder where you want to save the lists\r\nConst START_FOLDER = \"X:\\\"  'The folder to search<\/pre>\n<pre>Dim SUFFIX, FILE_LIST, RESULTS_SOURCELINKS, RESULTS_HYPERLINKS\r\nDim objList, objFS, objFolder, objFile<\/pre>\n<pre>SUFFIX = Replace(Replace(START_FOLDER,\":\",\"\"),\"\\\",\"_\")\r\nFILE_LIST = DIR &amp; SUFFIX &amp; \"excel_list.txt\"\r\nRESULTS_SOURCELINKS = DIR &amp; SUFFIX &amp; \"found_sourcelinks.csv\"\r\nRESULTS_HYPERLINKS = DIR &amp; SUFFIX &amp; \"found_hyperlinks.csv\"\r\nPW_PROTECTED = DIR &amp; SUFFIX &amp; \"pw_protected.txt\"<\/pre>\n<pre>Dim arrExcel()<\/pre>\n<pre>'Log links containing these strings.\r\n'If you want to log all links, there is an If clause to comment out further down\r\nDim arrBadLinks(1)\r\narrBadLinks(0) = \"OLD_SERVER\"\r\narrBadLinks(1) = \"X:\\\"<\/pre>\n<pre>count = -1<\/pre>\n<pre>set objFS = CreateObject(\"Scripting.FileSystemObject\")<\/pre>\n<pre>'-- Scan from START_FOLDER saving all files with xls in the name<\/pre>\n<pre>If objFS.FileExists(FILE_LIST) Then\r\n\u00c2\u00a0 wscript.echo \"Rescan for documents? (y\/n)\"\r\n\u00c2\u00a0 answer = wscript.stdin.Readline\r\n\u00c2\u00a0 If answer = \"y\" Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 set objList = objFS.OpenTextFile(FILE_LIST,2,true)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 FindExcelDocs\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objList.Close\r\n\u00c2\u00a0 End If\r\nElse\r\n\u00c2\u00a0 set objList = objFS.OpenTextFile(FILE_LIST,2,true)\r\n\u00c2\u00a0 FindExcelDocs\r\n\u00c2\u00a0 objList.Close\r\nEnd If<\/pre>\n<pre>'Comment out the following lines if you want the script to prompt between finding and checking the documents\r\n'wscript.echo \"Start checking documents? (y\/n)\"\r\n'answer = wscript.stdin.Readline\r\n'If answer &lt;&gt; \"y\" Then\r\n'\u00c2\u00a0 wscript.Quit\r\n'End If<\/pre>\n<pre>'-- Initialise results files\r\nSet objSourceLinks = objFS.OpenTextFile(RESULTS_SOURCELINKS,2,true)\r\nobjSourceLinks.Writeline(\"Path;Target\")\r\nSet objHyperLinks = objFS.OpenTextFile(RESULTS_HYPERLINKS,2,true)\r\nobjHyperLinks.Writeline(\"Path;Worksheet;Link Text;Link Address\")\r\nSet objPW = objFS.OpenTextFile(PW_PROTECTED,8,true)<\/pre>\n<pre>'-- Open each Excel doc, looking for Source Links and Hyperlinks<\/pre>\n<pre>set objExcel = CreateObject(\"Excel.Application\")\r\nobjExcel.Visible = false\r\nobjExcel.DisplayAlerts = false<\/pre>\n<pre>On Error Resume Next<\/pre>\n<pre>set objList = objFS.OpenTextFile(FILE_LIST,1)\r\nDo Until objList.AtEndOfStream<\/pre>\n<pre>\u00c2\u00a0 path = objList.Readline\r\n\u00c2\u00a0 set objFile = objFS.GetFile(path)<\/pre>\n<pre>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 wscript.echo path\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 set objWorkbook = objExcel.Workbooks.Open(path,0,true,,\"password\")\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If objWorkbook.HasPassword Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objPW.Writeline(path)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If<\/pre>\n<pre>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 '-- Find Source Links\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 colLinks = objWorkbook.LinkSources()\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If Not IsEmpty(colLinks) Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 for i = Lbound(colLinks) to Ubound(colLinks)\r\n\r\n        'To find ALL links comment out next two lines and corresponding \"Next\" and \"End If\"\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 For j = Lbound(arrBadLinks) to Ubound(arrBadLinks)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If InStr(UCase(colLinks(i)), UCase(arrBadLinks(j))) Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 wscript.echo \"Link: \" &amp; colLinks(i)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objSourceLinks.Writeline(path &amp; \";\" &amp; colLinks(i))\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Exit For\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Next\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Next\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If<\/pre>\n<pre>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 '-- Check through each worksheet looking for Hyperlinks\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 For sheet = 1 to objWorkbook.Worksheets.Count\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 wscript.echo \"Sheet: \" &amp; objWorkbook.Worksheets(sheet).Name\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 For Each link in objWorkbook.Worksheets(sheet).Hyperlinks\r\n\r\n        'To find ALL links comment out next two lines and corresponding \"Next\" and \"End If\"\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 For j = Lbound(arrBadLinks) to Ubound(arrBadLinks)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If InStr(link.Address, UCase(arrBadLinks(j))) Then\r\n   \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 wscript.echo \"HyperLink: \" &amp; link.Address\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objHyperLinks.Writeline(path &amp; \";\" &amp; _\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objWorkbook.Worksheets(sheet).Name &amp; \";\" &amp; _\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 link.TextToDisplay &amp; \";\" &amp; _\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 link.Address &amp; \";\" &amp; _\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 fileDate)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Next\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Next\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Next<\/pre>\n<pre>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objExcel.DisplayAlerts = false\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objExcel.ActiveWorkbook.Close(false)<\/pre>\n<pre>Loop<\/pre>\n<pre>objSourceLinks.Close\r\nobjHyperLinks.Close<\/pre>\n<pre>'-- SUBROUTINES --<\/pre>\n<pre>Sub FindExcelDocs\r\n\u00c2\u00a0 set objFolder = objFS.GetFolder(START_FOLDER)<\/pre>\n<pre>\u00c2\u00a0 'Files in root of folder\r\n\u00c2\u00a0 For each objFile in objFolder.Files\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If Instr(LCase(objFile.Name),\".xls\") Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 fileDate = objFile.DateLastModified\r\n\r\n      'Modify the next line to change date range, or comment out to check all files\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If (DatePart(\"yyyy\",fileDate) = \"2008\") Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 wscript.echo objFile.Path\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 'count = count + 1\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 'ReDim Preserve arrExcel(count + 1)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 'arrExcel(count) = objFile.Path\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objList.Writeline(objFile.Path)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If\r\n\u00c2\u00a0 Next\r\n\u00c2\u00a0\r\n\u00c2\u00a0 'Files in subfolders\r\n\u00c2\u00a0 SearchSubFolders objFolder<\/pre>\n<pre>End Sub<\/pre>\n<pre>Sub SearchSubFolders(Folder)\r\n\u00c2\u00a0 For Each Subfolder in Folder.Subfolders\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 set objFolder = objFS.GetFolder(Subfolder.Path)\r\n\u00c2\u00a0 For each objFile in objFolder.Files\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If Instr(LCase(objFile.Name),\".xls\") Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 fileDate = objFile.DateLastModified\r\n\r\n      'Modify the next line to change date range, or comment out to check all files\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If (DatePart(\"yyyy\",fileDate) = \"2008\") Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 wscript.echo objFile.Path\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 'count = count + 1\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 'ReDim Preserve arrExcel(count + 1)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 'arrExcel(count) = objFile.Path\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objList.Writeline(objFile.Path)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If\r\n\u00c2\u00a0 Next\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 wscript.echo\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 SearchSubFolders Subfolder\r\n\u00c2\u00a0 Next\r\nEnd Sub<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>&#8216; find_links_folder.vbs &#8216; Written by Carol Wapshere, 2008 &#8216; Search for .xls files and find links to other documents. Const REPORT_DIR = &#8220;C:\\ExcelLinks\\&#8221; &#8216;The folder where you want to save the lists Const START_FOLDER = &#8220;X:\\&#8221; &#8216;The folder to search Dim SUFFIX, FILE_LIST, RESULTS_SOURCELINKS, RESULTS_HYPERLINKS Dim objList, objFS, objFolder, objFile SUFFIX = Replace(Replace(START_FOLDER,&#8221;:&#8221;,&#8221;&#8221;),&#8221;\\&#8221;,&#8221;_&#8221;) FILE_LIST =&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"class_list":["post-257","page","type-page","status-publish","hentry"],"jetpack_shortlink":"https:\/\/wp.me\/Pkp1o-49","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/257","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=257"}],"version-history":[{"count":2,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/257\/revisions"}],"predecessor-version":[{"id":259,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/257\/revisions\/259"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=257"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}