Hyperlink url changes - mass change

Mich6661

New Member
Joined
Aug 1, 2013
Messages
16
Hi
I am having an issue performing a mass update on url addresses within excel.
I have a macro embedded spreadsheet that contains a list of url links to file folders. The purpose of the macro is to select each folder individually and email out to the relevant person.
The root file folder names change weekly from Wk44 to Wk45 etc.

If I use Ctrl+H I can mass change the Wk no in the displayed address text but it doesn't change the root url address and I have to edit each hyperlink manually


<tbody>
</tbody>

Since my file is in excess of 200 files, it takes far too long to amend manually. Is there an alternative process I can use?
I am using Office 365.

I used to be able to change this easily in the past so not sure what has changed.

thx
Mich.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
Re: Hyperlink url changes - mass chainge

Would you be happy to use a hyperlink formula? and by url address do you mean folder paths?
 

Mich6661

New Member
Joined
Aug 1, 2013
Messages
16
Re: Hyperlink url changes - mass chainge

Happy to try anything. And yea, folder paths
Thx
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
Re: Hyperlink url changes - mass chainge

Hi Mich,

You could setup path controls, that you can update file hyperlinks. Copy down your file list & amend the root directory to suit your PC & file location.

The Full path example confirms your control changes, also the File names need to have the extension as well;

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">File Names</td><td style="font-weight: bold;;">Full File Paths</td><td style="font-weight: bold;;">Hyperlink</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;;">Directory</td><td style="font-weight: bold;;">Folder Name</td><td style="font-weight: bold;;">Folder #</td><td style="font-weight: bold;;">Back Slash</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">DOC051119.pdf</td><td style=";">C:\Users\User Name\Desktop\WK46\DOC051119.pdf</td><td style="text-decoration: underline;color: #0563C1;;">DOC051119.pdf</td><td style="text-align: right;;"></td><td style=";">C:\Users\User Name\Desktop\</td><td style=";">WK</td><td style=";">46</td><td style=";">\</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">File2.pdf</td><td style=";">C:\Users\User Name\Desktop\WK46\File2.pdf</td><td style="text-decoration: underline;color: #0563C1;;">File2.pdf</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">File3.pdf</td><td style=";">C:\Users\User Name\Desktop\WK46\File3.pdf</td><td style="text-decoration: underline;color: #0563C1;;">File3.pdf</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Full path example</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">File4.pdf</td><td style=";">C:\Users\User Name\Desktop\WK46\File4.pdf</td><td style="text-decoration: underline;color: #0563C1;;">File4.pdf</td><td style="text-align: right;;"></td><td style="font-style: italic;;">C:\Users\User Name\Desktop\WK46\</td><td style="text-align: right;;"></td><td style="text-align: right;text-decoration: underline;color: #0563C1;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">File5.pdf</td><td style=";">C:\Users\User Name\Desktop\WK46\File5.pdf</td><td style="text-decoration: underline;color: #0563C1;;">File5.pdf</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=$E$2&$F$2&$G$2&$H$2&A2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=HYPERLINK(<font color="Blue">$E$2&$F$2&$G$2&$H$2&A2,A2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E5</th><td style="text-align:left">=E2&F2&G2&H2</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Forum statistics

Threads
1,089,368
Messages
5,407,862
Members
403,167
Latest member
LakesideChill19

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top