{"id":263,"date":"2008-11-14T09:12:06","date_gmt":"2008-11-14T09:12:06","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?page_id=263"},"modified":"2008-11-14T09:12:06","modified_gmt":"2008-11-14T09:12:06","slug":"change_hyperlinksvbs","status":"publish","type":"page","link":"https:\/\/www.wapshere.com\/missmiis\/change_hyperlinksvbs","title":{"rendered":"change_hyperlinks.vbs"},"content":{"rendered":"<pre>' change_hyperlinks.vbs\r\n' Written by Carol Wapshere\r\n'\r\n' Change hyperlinks in a list of Excel documents as specified in a csv file.\r\n' The csv must have the following columns:\r\n'        path;worksheet;link text;link address\r\n' Set the arrFind and arrReplace values below to find and replace strings in the links.\r\n\r\nOption Explicit<\/pre>\n<pre>Const CHANGE_LIST = \"C:\\ExcelLinks\\x-hyperlinks.csv\"\r\nConst LOG_FILE = \"C:\\ExcelLinks\\x_hyperlinks.log\"<\/pre>\n<pre>Dim arrFind(1)\r\nDim arrReplace(1)\r\narrFind(0) = \"\\\\OLD_SERVER\"\r\narrReplace(0) = \"\\\\NEW_SERVER\"\r\narrFind(1) = \"X:\\\"\r\narrReplace(1) = \"P:\\\"<\/pre>\n<pre>Dim objFS, objList, objExcel, objWorkbook, objWorksheet, objLog\r\nDim strLine, path, worksheet\r\nDim arrItems, arrPath\r\nDim LinkText, newLinkText, LinkAddress, newLinkAddress\r\nDim link, i\r\nDim bEOF, bKeepFileOpen<\/pre>\n<pre>set objFS = CreateObject(\"Scripting.FileSystemObject\")\r\nset objList = objFS.OpenTextFile(CHANGE_LIST,1)\r\nset objLog = objFS.OpenTextFile(LOG_FILE, 8, true)<\/pre>\n<pre>set objExcel = CreateObject(\"Excel.Application\")\r\nobjExcel.Visible = true\r\nobjExcel.DisplayAlerts = true<\/pre>\n<pre>On Error Resume Next<\/pre>\n<pre>bKeepFileOpen = false\r\nbEOF = false<\/pre>\n<pre>strLine = objList.Readline\r\nstrLine = objList.Readline<\/pre>\n<pre>Do Until bEOF\r\n\u00c2\u00a0 arrItems = Split(strLine,\";\")\r\n\u00c2\u00a0 path = arrItems(0)\r\n\u00c2\u00a0 worksheet = arrItems(1)\r\n\u00c2\u00a0 LinkText = arrItems(2)\r\n\u00c2\u00a0 LinkAddress = arrItems(3)\r\n\u00c2\u00a0 NewLinkText = \"\"\r\n\u00c2\u00a0 NewLinkAddress = \"\"<\/pre>\n<pre>\u00c2\u00a0 If objFS.FileExists(path) Then<\/pre>\n<pre>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 For i = 0 to Ubound(arrFind)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If Instr(UCase(LinkAddress), UCase(arrFind(i))) &gt; 0 Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 NewLinkAddress = Replace(LinkAddress, arrFind(i), arrReplace(i),1,1,1)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If Instr(LinkText, arrFind(i)) &gt; 0 Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 NewLinkText = Replace(LinkText, arrFind(i), arrReplace(i),1,1,1)\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Exit For\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Next\r\n\u00c2\u00a0\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If NewLinkAddress &lt;&gt; \"\" Then\r\n\u00c2\u00a0\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 'Open the Excel file\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If Not bKeepFileOpen Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 wscript.echo path\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objLog.Writeline \"File: \" &amp; path\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objExcel.DisplayAlerts = true\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 set objWorkbook = objExcel.Workbooks.Open(path,0,false,,\"password\")\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 wscript.echo worksheet\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 set objWorksheet = objWorkbook.Worksheets(worksheet)\r\n\u00c2\u00a0\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 For Each link in objWorksheet.Hyperlinks\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If link.Address = LinkAddress Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 wscript.echo LinkAddress\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objLog.Writeline \"Changing link: \" &amp; LinkAddress\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objLog.Writeline \"New link address: \" &amp; NewLinkAddress<\/pre>\n<pre>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 link.Address = NewLinkAddress\r\n          wscript.echo \"Address is now \" &amp; link.Address\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If link.Address &lt;&gt; NewLinkAddress 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 \"Unable to change link\"\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objLog.Writeline(\"Error: Unable to change link\")\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If<\/pre>\n<pre>\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 If NewLinkText &lt;&gt; \"\" 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 link.TextToDisplay = NewLinkText\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objLog.Writeline \"New link text: \" &amp; NewLinkText\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\u00c2\u00a0\u00c2\u00a0 wscript.echo\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objLog.Writeline \"\"\r\n\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 End If\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Next\r\n\u00c2\u00a0\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 End If<\/pre>\n<pre>\u00c2\u00a0 Else\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objLog.Writeline \"File not found: \" &amp; path\r\n\u00c2\u00a0 End If<\/pre>\n<pre>\u00c2\u00a0 'Read the next line in the CSV file\r\n\u00c2\u00a0 If objList.AtEndOfStream Then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 bEOF = TRUE\r\n\u00c2\u00a0 Else\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 strLine = objList.Readline\r\n\u00c2\u00a0 End If<\/pre>\n<pre>\u00c2\u00a0 'Only close the current doc if the next one is different\r\n\u00c2\u00a0 If (InStr(strLine, path) = 0) Or bEOF then\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 bKeepFileOpen = FALSE\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objExcel.DisplayAlerts = true\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objExcel.ActiveWorkbook.Save\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objExcel.ActiveWorkbook.Close\r\n\u00c2\u00a0 Else\r\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 bKeepFileOpen = TRUE\r\n\u00c2\u00a0 End If\r\nLoop<\/pre>\n<pre>objList.Close\r\nobjLog.close\r\nobjExcel.Visible = true\r\nobjExcel.DisplayAlerts = true<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>&#8216; change_hyperlinks.vbs &#8216; Written by Carol Wapshere &#8216; &#8216; Change hyperlinks in a list of Excel documents as specified in a csv file. &#8216; The csv must have the following columns: &#8216; path;worksheet;link text;link address &#8216; Set the arrFind and arrReplace values below to find and replace strings in the links. Option Explicit Const CHANGE_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-263","page","type-page","status-publish","hentry"],"jetpack_shortlink":"https:\/\/wp.me\/Pkp1o-4f","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/263","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=263"}],"version-history":[{"count":1,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/263\/revisions"}],"predecessor-version":[{"id":264,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/263\/revisions\/264"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=263"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}