Macro to create hyperlink refuses to work

mal909

New Member
Joined
Aug 5, 2010
Messages
7
Hi All,

it's a while since I've been around these parts, so long in fact I've forgotten my login!:LOL:

I have a marco that takes information from an excel sheet and moves it to a new workbook, saves the workbook in a specific folder (which it creates), with a specific name (based on the data it copied).<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
The information to be saved is unique per row of the original excel sheet. <o:p></o:p>
So, for example, the macro copies the information from the first row, arranges it in a particular manner in the new workbook, creates a folder in a specific directory, saves the new workbook in the new folder. <o:p></o:p>
It then moves to the next row and repeats the process until it hits an empty row.<o:p></o:p>
This all works great! <o:p></o:p>
However, once a row has been saved to it’s new workbook I want to add a hyperlink to a particular cell in the relevant row in the original excel sheet, so I can click on this cell and it’ll open the new workbook with the relevant data. <o:p></o:p>
For the life of me I cannot seem to get this to work. <o:p></o:p>
The really odd thing is I have it working in other bits of code in the same workbook but it just refuses t o work in the current code I’m working on. <o:p></o:p>
<o:p> </o:p>
Where the code works looks like this:<o:p></o:p>
Code:
Dim Co As String<o:p></o:p>
Dim Link As String<o:p></o:p>
Dim Project As String<o:p></o:p>
<o:p> </o:p>
Link = "R:\Dept\General Licensing\General Licensing Summary (DO NOT EDIT)\Sync Licensing Applications\"<o:p></o:p>
Cells(rowno1, 7).Select<o:p></o:p>
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Link & Co & " - " & Project & ".xlsm", TextToDisplay:="View Report"
<o:p> </o:p>
Where it doesn’t work looks like this:<o:p></o:p>
<o:p> </o:p>
Code:
Dim CompanyName  As String<o:p></o:p>
Dim ProjectTitle As String<o:p></o:p>
Dim TrackTitle As String<o:p></o:p>
Dim Publisher As String<o:p></o:p>
Dim Link As String<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Link = "R:\Dept\General Licensing\General Licensing Summary (DO NOT EDIT)\Sync Licensing Applications\Publisher Sync Requests\"<o:p></o:p>
<o:p> </o:p>
Cells(rowno1, 7).Select<o:p></o:p>
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _<o:p></o:p>
Link & CompanyName & " - " & ProjectTitle & "\" & TrackTitle & " - " & Publisher & ".xlsm", TextToDisplay:=Publisher<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
For some bizarre reason this one doesn’t work. <o:p></o:p>
When I edit the Hyperlink this is what the macro has entered:<o:p></o:p>
<o:p> </o:p>
Code:
[COLOR=black][FONT=Calibri]Publisher%20Sync%20Requests\Test%20Licence%20-%20test%20sync1\Star%20-%20PM%20Productions.xlsm<o:p></o:p>[/FONT][/COLOR]
<o:p> </o:p>
In the first example that works the hyperlink looks like this in the cell:<o:p></o:p>
<o:p> </o:p>
Code:
\\dublin1\DATA\Dept\General Licensing\General Licensing Summary (DO NOT EDIT)\Sync Licensing Applications\Test Licence - test sync1.xlsm
<o:p> </o:p>
<o:p> </o:p>
All the other code, making directories, saving files etc work fine, it’s just this particular macro causing me headaches and I can’t for the life of me figure out what’s wrong. <o:p></o:p>
I’m hoping its something simple and I just can’t see the wood for the trees. <o:p></o:p>
<o:p> </o:p>
Please, Please help!!<o:p></o:p>
It’s driving me crazy at this stage!!<o:p></o:p>
Let me know if you need more info.<o:p></o:p>

Best wishes,

Paul.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
Are there any reserved characters in your variables CompanyName, ProjectTitle, TrackTitle, or Publisher ?

< (less than)
> (greater than)
: (colon)
" (double quote)
/ (forward slash)
\ (backslash)
| (vertical bar or pipe)
? (question mark)
* (asterisk)
 

mal909

New Member
Joined
Aug 5, 2010
Messages
7
nope, they're all names "EMI Music Publishing" "Treasure Entertainment" etc...
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
Just for fun, can you post what x evaluates to...
Code:
x = Link & CompanyName & " - " & ProjectTitle & "\" & TrackTitle & " - " & Publisher & ".xlsm"
 

mal909

New Member
Joined
Aug 5, 2010
Messages
7

ADVERTISEMENT

One random example:

Code:
R:\Dept\General Licensing\General Licensing Summary (DO NOT EDIT)\Sync Licensing Applications\Publisher Sync Requests\Val Falvey Ltd - Val Falvey T.D\Relax - Perfect Songs.xlsm

where Link =

Code:
R:\Dept\General Licensing\General Licensing Summary (DO NOT EDIT)\Sync Licensing Applications\Publisher Sync Requests\

the thing is the hyperlink posted starts at the "Publisher Sync Requests" folder and forgets about the first rest of the link before that.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
Just as a quick test, can you get rid of the period in "Falvey T.D" ?

I doubt it will matter but give it a try. It should be easy to test.
 

mal909

New Member
Joined
Aug 5, 2010
Messages
7

ADVERTISEMENT

Yeah,

I've many, many of these done already, I'd say , maybe 1 in 20 has something other than letters and spaces.

This bug has been plagueing me for months, I'm still using the system, just none of the hyperlinks are currently working.

I can confirm 100% that even if I just use letters and spaces the same thing happens.

thanks,
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
Got it. I didn't think the period thing was it, but now we know.

I really don't have any other ideas. Sorry. Maybe it has to do with the length of the path and file name. Is there some sort of limit with your network? Again just throwing that out there..

As another test, can you manually create in a cell a hyperlink to...
R:\Dept\General Licensing\General Licensing Summary (DO NOT EDIT)\Sync Licensing Applications\Publisher Sync Requests\Val Falvey Ltd - Val Falvey T.D\Relax - Perfect Songs.xlsm
 

mal909

New Member
Joined
Aug 5, 2010
Messages
7
yes I can, if I manually type in the link as a hyperlink it works fine.

I'll check with our network guys to see if there are any issues in that regard.

thanks,

Paul.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
As another way to code it, give this a try...

Code:
Cells(rowno1, 7).Formula = "=HYPERLINK(" & Link & CompanyName & " - " & ProjectTitle & "\" & _
                           TrackTitle & " - " & Publisher & ".xlsm, ""View Report"")"
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,920
Messages
5,834,375
Members
430,282
Latest member
MeredithD26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top