Macro to create hyperlink refuses to work

[mal909]

Hi All,

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

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-comfficeffice" /><o></o>
The information to be saved is unique per row of the original excel sheet. <o></o>
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></o>
It then moves to the next row and repeats the process until it hits an empty row.<o></o>
This all works great! <o></o>
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></o>
For the life of me I cannot seem to get this to work. <o></o>
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></o>
<o> </o>
Where the code works looks like this:<o></o>

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> </o>
Where it doesn’t work looks like this:<o></o>
<o> </o>

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> </o>
For some bizarre reason this one doesn’t work. <o></o>
When I edit the Hyperlink this is what the macro has entered:<o></o>
<o> </o>

[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></o>
<o> </o>

\\dublin1\DATA\Dept\General Licensing\General Licensing Summary (DO NOT EDIT)\Sync Licensing Applications\Test Licence - test sync1.xlsm

<o> </o>
<o> </o>
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></o>
I’m hoping its something simple and I just can’t see the wood for the trees. <o></o>
<o> </o>
Please, Please help!!<o></o>
It’s driving me crazy at this stage!!<o></o>
Let me know if you need more info.<o></o>

Best wishes,

Paul.

 

[AlphaFrog]

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]

nope, they're all names "EMI Music Publishing" "Treasure Entertainment" etc...

 

[AlphaFrog]

Just for fun, can you post what x evaluates to...

x = Link & CompanyName & " - " & ProjectTitle & "\" & TrackTitle & " - " & Publisher & ".xlsm"

 

[mal909]

One random example:

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 =

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]

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]

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]

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]

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]

As another way to code it, give this a try...

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