Hyperlink customer in another workbook to a file

[ipbr21054]

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

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

 

[ipbr21054]

Is it possible to just search / find TOM JONES 002 then have it selected & run existing code above

 

[ipbr21054]

@My Aswer Is This are you able to assist please.

 

[ipbr21054]

Forgot to paste code from older post.

Sub Test()
Application.ScreenUpdating = False
Dim c As Range
Dim ans As String
Dim Lastrow As Long
ans = ActiveCell.Value
Lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Sheets("Sheet2").Range("A1:A" & Lastrow)
        If c.Value = ans Then Application.Goto Reference:=Sheets("Sheet2").Range(c.Address): Exit Sub
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I do not believe you can do anything to a closed Workbook.
You must always open the Workbook manually or have a script open the Workbook first. Then have the script do what you want

 

[ipbr21054]

Are you able to the assist.
I did find one of your old posts.

Basically it’s take note of the value in one cell.
Open the other worksheet & select the postage sheet.
Find that value.

 

[My Aswer Is This]

I'm a little lost here. You begin talking about a Hyperlink.
You said quote:
"an you advise please how i hyperlink a customers name to a file in another workbook & from another workbook."

I have never tried to do something like this:

And you said:
Basically it’s take note of the value in one cell.
Open the other worksheet & select the postage sheet.
Find that value.

Are you sure you mean another Workbook or do you mean Worksheet.

 

[ipbr21054]

I have a code at present that when I manually select the customers name it’s hyperlinks to the file in question.
So I thought let the new code use the value in the cell B3 to find on the other sheet, have it select that found value then use the existing hyperlink code.

I thought the file was called the workbook & once open you then select the worksheet ?

Info for you.
Workbook is DISCO CALC.xlsm
Sheet in question is PRINT LABELS
Cell with value in is B3 current its say TOM JONES 003

Now open Workbook DR.xlsm
Select sheet POSTAGE.
Select column B
Now we start to find TOM JONES 003
Once found just select that value.

Then i will add my working code to hyperlink the file.

Makes sense ?
The value to look for will always be towards the last few rows of the current values that are in column B
This will speed things up & saves starting at row 1 to look in each row to the current row which is 2440 etc

Thanks

 

[My Aswer Is This]

I think you need to post a new posting on the forum and see if someone else may be a able to help you.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[ipbr21054]

This post i found of yors searched for a value on the same worksheet.

My only difference is to use value on one worksheet & find it in another.

Sub Test()
Application.ScreenUpdating = False
Dim c As Range
Dim ans As String
Dim Lastrow As Long
ans = ActiveCell.Value
Lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Sheets("Sheet2").Range("A1:A" & Lastrow)
        If c.Value = ans Then Application.Goto Reference:=Sheets("Sheet2").Range(c.Address): Exit Sub
    Next
Application.ScreenUpdating = True
End Sub