If you work extensively with Excel, you’ve likely faced formulas that link to external workbooks. Managing and understanding these links can be tricky, especially when you need to extract the workbook’s name or path and create easy navigation through hyperlinks.

In this blog post, you’ll learn how to use a simple yet powerful VBA macro to automatically extract linked workbook information from a formula and add a clickable hyperlink to it. This method streamlines workbook management and enhances spreadsheet navigation. You can quickly see what workbook is being referenced, and if you need to, you can quickly click the link to open it up. No need to go into the Workbook Links panel and click around, which can be challenging if you have several links.

Why Extract Linked Workbook Info and Create Hyperlinks?

Excel formulas that reference external workbooks usually contain the linked workbook’s path and filename in the formula text, like:

text='C:\Users\UserName\Documents\[SalesData.xlsx]Sheet1'!A1

Manually parsing these formulas to find the linked workbook name and create hyperlinks can be tedious. Automating this process helps:

• Quickly identify all linked files
• Avoid broken or outdated links
• Improve navigation within your workbook
• Save time in auditing external references

Understanding the VBA Code to Extract and Link Workbook Info

Here’s the VBA code we’ll use to extract the linked workbook name from a formula in cell A1, store that info in cell B1, and create a clickable hyperlink in cell C1:

Dim wbName As String
Dim cellFormula As Range

Set cellFormula = ws.Range("A1")
fText = cellFormula.Formula

startPos = InStr(fText, "[")
endPos = InStr(fText, "]")

If startPos > 1 Then
    filePath = Mid(fText, 2, startPos - 2)
    filePath = Replace(filePath, "'", "") ' remove single quotes
    
    wbName = Mid(fText, startPos + 1, endPos - startPos - 1)
    wbName = Replace(wbName, "[", "")
    wbName = Replace(wbName, "]", "")
    
    Debug.Print filePath & wbName
    ws.Range("B1").Value = filePath & wbName
    ws.Hyperlinks.Add Anchor:=ws.Range("C1"), _
        Address:=filePath & wbName, _
        TextToDisplay:=wbName
End If

Breaking Down the Code Step-by-Step

1. Define the Target Cell and Get the Formula

textSet cellFormula = ws.Range("A1")
fText = cellFormula.Formula

• We focus on cell A1 in the worksheet ws.
• We grab the entire formula from this cell as a string for processing.

2. Locate the Workbook Name within the Formula

textstartPos = InStr(fText, "[")
endPos = InStr(fText, "]")

• The workbook name is enclosed in square brackets [WorkbookName.xlsx].
• InStr finds the position of these brackets in the formula text.

3. Extract the File Path

textfilePath = Mid(fText, 2, startPos - 2)
filePath = Replace(filePath, "'", "")

• Extract the file path, which usually precedes the [.
• Remove any single quotes to keep the path clean.

4. Extract and Clean the Workbook Name

textwbName = Mid(fText, startPos + 1, endPos - startPos - 1)
wbName = Replace(wbName, "[", "")
wbName = Replace(wbName, "]", "")

• Extract the workbook name between the brackets.
• Remove any remaining brackets for a clean filename.

5. Output the Combined Path and Workbook Name

textDebug.Print filePath & wbName
ws.Range("B1").Value = filePath & wbName

• Print the full file path combined with the workbook name in the Immediate Window (useful for debugging).
• Also store this combined string in cell B1.

6. Create a Hyperlink to the Workbook

textws.Hyperlinks.Add Anchor:=ws.Range("C1"), _
    Address:=filePath & wbName, _
    TextToDisplay:=wbName

• Add a hyperlink in cell C1.
• The link points to the extracted full path of the linked workbook.
• The link text displays the workbook name for clarity.

How to Use This VBA Macro in Your Workbook

1. Open the Visual Basic for Applications editor (Press Alt + F11 in Excel).
2. Insert a new module: Right-click your workbook in the Project explorer, select Insert > Module.
3. Copy and paste the code inside a Sub procedure, making sure you define ws properly
4. Run the macro or assign it to a button for quick execution.
5. Check cells B1 for the extracted link and C1 for the clickable hyperlink.

Dim wbName As String
Dim cellFormula As Range

Set cellFormula = ws.Range("A1")
fText = cellFormula.Formula

startPos = InStr(fText, "[")
endPos = InStr(fText, "]")

If startPos > 1 Then
    filePath = Mid(fText, 2, startPos - 2)
    filePath = Replace(filePath, "'", "") ' remove single quotes
    
    wbName = Mid(fText, startPos + 1, endPos - startPos - 1)
    wbName = Replace(wbName, "[", "")
    wbName = Replace(wbName, "]", "")
    
    Debug.Print filePath & wbName
    ws.Range("B1").Value = filePath & wbName
    ws.Hyperlinks.Add Anchor:=ws.Range("C1"), _
        Address:=filePath & wbName, _
        TextToDisplay:=wbName
End If

Tailoring This Code for Your Needs

• Range flexibility: Loop through multiple cells with formulas and automate link extraction and hyperlink creation for all.
• Error handling: Add more robust checks for broken links or non-standard formula formats.
• User interface: Create a user form that lets users pick ranges or sheets interactively.
• Dynamic output: Write results to a dedicated worksheet listing all external links in your workbook.

Conclusion

Automating the process to extract linked workbook information from formulas and creating hyperlinks with VBA can significantly increase your workbook management efficiency. Whether auditing external links or creating easy navigation across related files, this VBA snippet provides a solid foundation.

Try enhancing this script by adapting it to your specific workbook structures and enjoy smoother workflows in Excel.

keywords to help others find this post: extract linked workbook, hyperlinks, getting linked workbook info

---

Do You Need Personalized Help and Custom Solutions?

I have been called a guru and hero more times than I can count, as I am a great listener and truly have a knack for asking the right questions to understand unique business challenges. I am very passionate about crafting tools and processes that work for users of all levels and experience. 

Reach out today and let’s discuss how I can help you and your business!

I also offer one-on-one tutoring for customized learning and upskilling. Visit my consulting page and send a message if you are interested.

Other Resources

Also, consider checking out some great resources on Amazon!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to make a purchase which will help cover the cost of hosting this website. 

Please bookmark and subscribe!  I am actively working on adding new, relevant content to help others out! Thanks so much!

Subscribe via Email and Stay Up to Date!

If I helped you, please consider leaving a tip via PayPal to support the blog! Thanks!!

Thanks so much for supporting my blog and for helping others too!