Code for Hyperlink

jrtdcg

New Member
Joined
Dec 4, 2012
Messages
45
Hi All,
I've been doing some searching regarding hyperlinks, but can't seem to find a near match for what I'm trying to accomplish.

I have a workbook that is generating a review process from the current user in the workbook. The process involves a lot of different steps. Everything seem to be working well within the review process except for the hyperlink. What I'd like to do is have the code look at the current path and provide a hyperlink to a cell within the spreadsheet. After the workbook has been renamed from the review process the next user has a direct like from the previous workbook. The problem that I'm having now is my code only show a link to the folder and the link won't open. I'd like for it to show the entire path directly to the file so that the file will open when it hyperlink is clicked.

Currently showing up in cell D1:
S:\Sales_Data\Q2014\010186-10901 Roosevelt Island- S046FD0- XX SEPS- D XXXXX\Latest Revision to Customer

This is what should be showing up in cell D1:
S:\Sales_Data\Q2014\010186-10901 Roosevelt Island- S046FD0- XX SEPS- D XXXXX\Latest Revision to Customer\10186-10901.xlsm

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>

See below:
Code:
'--------------------This is where I'm currently putting the filename path 
Range("D1").Select
        ActiveCell.FormulaR1C1 = ActiveWorkbook.Path
    
   
'---------------This where I copy from one workbook to the other workbook and add the hyperlink

    Windows("Approval_Review.xlsm").Activate
Sheets("Bill of Material").Select
    Range("D1").Select
    Selection.Copy
     Windows("book1.xlsx").Activate
    Range("C40").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Dim cell As Range
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If cell <> "" Then
ActiveSheet.Hyperlinks.Add cell, cell.Value
End If
Next

Any help would be appreciated....Thanks in advance!!!

J.R.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try changing:

Code:
ActiveCell.FormulaR1C1 = ActiveWorkbook.Path

to:

Code:
ActiveCell.FormulaR1C1 = ActiveWorkbook.FullName
 

jrtdcg

New Member
Joined
Dec 4, 2012
Messages
45
Thanks Andrew! That give me the desired results for the full name in cell D1, but when I click the hyperlink after the process is completed I'm still getting an error message. Not sure if it's code related or what???

The message is from Microsoft Excel and states "Cannot open the specified file."

Any thoughts?

Thanks!
 

jrtdcg

New Member
Joined
Dec 4, 2012
Messages
45

ADVERTISEMENT

Yes, just inserted manual hyperlink and it opened the workbook.
 
Last edited:

jrtdcg

New Member
Joined
Dec 4, 2012
Messages
45

ADVERTISEMENT

From the code standpoint...no they look nothing alike. When I do it manually I have to go to the file location which is captured in the code from as each step in the manual process.

What I was told from previous applications was if I copied the full file name and then used the code listed below that it would provide the hyperlink based on the full file path name. Which is what was copied from cell D1 to another workbook to cell C40 as you can see below.

Code:
 Windows("Approval_Review.xlsm").Activate
Sheets("Bill of Material").Select
    Range("D1").Select
    Selection.Copy
     Windows("book1.xlsx").Activate
    Range("C40").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Dim cell As Range
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If cell <> "" Then
ActiveSheet.Hyperlinks.Add cell, cell.Value
End If
Next
 
Last edited:

jrtdcg

New Member
Joined
Dec 4, 2012
Messages
45
Sorry, misunderstand on my part. They look exactly the same except for the link that was copied from the workbook has the server name in front it. The manually input link does not have the server name in front.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,322
Members
414,053
Latest member
Dual Showman

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