CODEWITHSUNDEEP
vbaexceldynamichyperlink
Hunter Velez
Hunter Velez

Reputation: 3

Excel VBA - Extract/Open a dynamic hyperlink URL/Address

Greeting!

I am attempting to use excel vba to extract a dynamic hyperlink from OR use excel vba to open the dynamic hyperlink in a worksheet. I am able to click the hyperlink and it will open up correctly. So far, I am not able to find any relevant information pertaining to how to extract the URL the hyperlink is pointing to or simply open the hyperlink.

Here is the formula that is used to build the dynamic hyperlink based on a model selection. 

=IFERROR(HYPERLINK(VLOOKUP(S.O.P.!$D$3,Models!$C$2:$G$296,4,FALSE), "Click Here to Open Box Label"), "Unable to retrieve part information")

So based on a selection the user makes this formula goes and finds the respective link that will be used to create the hyperlink.

What I want: Using a button - open the file(PDF) to allow the users to easily print the files they need from one button. I am trying to minimize human touches to eliminate the possibility for human errors.

I have also attempted to record a macro to open up the link, but it only takes me to the worksheet that the hyperlink is placed in.

Here are links to other post that were not able to resolve my issue, but provided good information... Excel VBA Get hyperlink address of specific cell

Extract URL From Excel Hyperlink Formula

Thank you, I look forward to some helpful responses c:

let me know if I need to elaborate or clarify anything!

Upvotes: 0

Views: 1678

Answers (1)

Danhol86
Danhol86

Reputation: 1452

Any reason you cant run the vlookup in VBA. Then you will have the link address as a string which you can then do what you want with:

Sub GetLink()
    Dim ReturnedAddress As String
    ReturnedAddress = Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets("S.O.P.").Range("D3"), ThisWorkbook.Sheets("Models").Range("C2:G296"), 4, 0)
end sub

Upvotes: 0

Related Questions