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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
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,271
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,271
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,271
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,271
As another way to code it, give this a try...

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,946
Messages
5,514,327
Members
408,997
Latest member
VJarvis

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top