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.

 

[mal909]

What if I want the "Publisher" value as the text in the cell, where you've put "View Report"?

thanks,

 

[AlphaFrog]

Does this method work?

Publisher instead of "View Report"...

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

 

[mal909]

I got an "application defined or object defined error"

I think we're doomed for this never to work......