ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,596
- Office Version
- 2007
- Platform
- Windows
Hi,
Can you advise please how i hyperlink a customers name to a file in another workbook & from another workbook.
I am currently using this code below which works fine BUT i need to have the worksheet open & then select customer in question for it to hyperlink.
In another workbook i have the customers name in a worksheet cell,so my plan is to make the hyperlink happen from here without having to close one workbook then open another then run the code below.
Some info for you.
The open workbook is called DISCO CALC
The worksheet in use is called PRINT LABELS
The cell where the customers name is at is B3
Example on how it should work.
Using the info above look in cell B3 for the customers name & in this case it is TOM JONES 002
The code then looks at my other closed workbook / worksheet where the names are located.
The workbook is called DR.xlsm
The worksheet is called POSTAGE
Using the xlup code look in column B for TOM JONES 002, in all cases the customers name will only be within the last few entries.
Once found hyperlink it.
The stored file location path is C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF
Majority of what you need is above but its working from another workbook im stumped with
Can you advise please how i hyperlink a customers name to a file in another workbook & from another workbook.
I am currently using this code below which works fine BUT i need to have the worksheet open & then select customer in question for it to hyperlink.
In another workbook i have the customers name in a worksheet cell,so my plan is to make the hyperlink happen from here without having to close one workbook then open another then run the code below.
Some info for you.
The open workbook is called DISCO CALC
The worksheet in use is called PRINT LABELS
The cell where the customers name is at is B3
Example on how it should work.
Using the info above look in cell B3 for the customers name & in this case it is TOM JONES 002
The code then looks at my other closed workbook / worksheet where the names are located.
The workbook is called DR.xlsm
The worksheet is called POSTAGE
Using the xlup code look in column B for TOM JONES 002, in all cases the customers name will only be within the last few entries.
Once found hyperlink it.
The stored file location path is C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF
Majority of what you need is above but its working from another workbook im stumped with
VBA Code:
Private Sub HyperlinkDisco_Click()
Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\"
If ActiveCell.Column = Columns("B").Column Then
If Len(Dir(FILE_PATH & ActiveCell.Value & ".pdf")) Then
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".pdf"
With ActiveCell
.Font.Size = 12
End With
MsgBox "HYPERLINK WAS SUCCESSFUL.", vbInformation, "POSTAGE SHEET HYPERLINK MESSAGE"
End If
Else
MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE FILE.", vbCritical, "POSTAGE SHEET DISCO II HYPERLINK MESSAGE"
Exit Sub
End If
If Dir(FILE_PATH & ActiveCell.Value & ".pdf") = "" Then
If MsgBox("THERE IS NO FILE FOR THIS CUSTOMER" & vbNewLine & "WOULD YOU LIKE TO OPEN THE DISCO II FOLDER ?", vbYesNo + vbCritical, "HYPERLINK CUSTOMER DISCO II MESSAGE.") = vbYes Then
CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\")
End If
End If
End Sub