{"id":260,"date":"2008-11-14T09:03:39","date_gmt":"2008-11-14T09:03:39","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?page_id=260"},"modified":"2008-11-14T09:03:39","modified_gmt":"2008-11-14T09:03:39","slug":"find_links_filelistvbs","status":"publish","type":"page","link":"https:\/\/www.wapshere.com\/missmiis\/find_links_filelistvbs","title":{"rendered":"find_links_filelist.vbs"},"content":{"rendered":"<pre>' find_links_filelist.vbs\r\n' Written by Carol Wapshere, 2008\r\n' Search a list of Excel documents for sourcelinks and hyperlinks.\r\n\r\nConst FILE_LIST = \"C:\\ExcelLinks\\x-all.txt\"\r\nConst RESULTS_SOURCELINKS = \"C:\\ExcelLink\\x-all_sourcelinks.csv\"\r\nConst RESULTS_HYPERLINKS = \"C:\\ExcelLinks\\x-all_hyperlinks.csv\"<\/pre>\n<pre>Dim objList, objFS, objFolder, objFile<\/pre>\n<pre>Dim arrExcel()<\/pre>\n<pre>'Log links with the following strings.\r\n'To log ALL links there are lines to comment out below.\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>'-- 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\")<\/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 = true<\/pre>\n<pre>On Error Resume Next<\/pre>\n<pre>set objList = objFS.OpenTextFile(FILE_LIST,1)\r\nstrLine = objList.Readline<\/pre>\n<pre>Do Until objList.AtEndOfStream<\/pre>\n<pre>\u00c2\u00a0 strLine = objList.Readline\r\n\u00c2\u00a0 arrItems = Split(strLine,\";\")\r\n\u00c2\u00a0 path = arrItems(0)<\/pre>\n<pre>\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\")<\/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 to lines and corresonding \"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) &amp; \";\" &amp; fileDate)\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 to lines and corresonding \"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\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","protected":false},"excerpt":{"rendered":"<p>&#8216; find_links_filelist.vbs &#8216; Written by Carol Wapshere, 2008 &#8216; Search a list of Excel documents for sourcelinks and hyperlinks. Const FILE_LIST = &#8220;C:\\ExcelLinks\\x-all.txt&#8221; Const RESULTS_SOURCELINKS = &#8220;C:\\ExcelLink\\x-all_sourcelinks.csv&#8221; Const RESULTS_HYPERLINKS = &#8220;C:\\ExcelLinks\\x-all_hyperlinks.csv&#8221; Dim objList, objFS, objFolder, objFile Dim arrExcel() &#8216;Log links with the following strings. &#8216;To log ALL links there are lines to comment out below&#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-260","page","type-page","status-publish","hentry"],"jetpack_shortlink":"https:\/\/wp.me\/Pkp1o-4c","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/260","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=260"}],"version-history":[{"count":2,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/260\/revisions"}],"predecessor-version":[{"id":262,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/260\/revisions\/262"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=260"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}