如何用VBA宏从Excel单元格链接中提取工作簿路径并打印至汇总报告？

Absolutely! You can easily pull that workbook path (including the [Input.xlsx] portion) using basic VBA string manipulation. Let's break down two practical approaches: a reusable custom function for direct worksheet use, and a macro to batch process multiple links at once.

1. Custom Worksheet Function #

This function works just like Excel's built-in functions, letting you extract paths directly in your spreadsheet:

Function GetWorkbookPathFromLink(cell As Range) As String
    Dim linkFormula As String
    Dim closingBracketPos As Integer
    
    ' Grab the cell's underlying formula (not just the displayed value)
    linkFormula = cell.Formula
    
    ' Locate the position of the closing "]" bracket in the link
    closingBracketPos = InStr(linkFormula, "]")
    
    If closingBracketPos > 0 Then
        ' Extract everything from the start up to (and including) the "]"
        GetWorkbookPathFromLink = Left(linkFormula, closingBracketPos)
        
        ' Optional: Remove surrounding single quotes if you don't need them
        ' GetWorkbookPathFromLink = Replace(GetWorkbookPathFromLink, "'", "")
    Else
        ' Handle cells without valid external links
        GetWorkbookPathFromLink = "Invalid external link"
    End If
End Function

How to use it: #

1. Press Alt + F11 to open the VBA Editor
2. Insert a new module (Right-click your workbook in the Project Explorer > Insert > Module)
3. Paste the code above
4. Return to your worksheet and enter =GetWorkbookPathFromLink(F4) in a blank cell (like G4) to pull the path from cell F4's link.

2. Batch Processing Macro #

If you need to extract paths for a whole column of links, use this macro to automate the workflow:

Sub ExtractAllLinkPaths()
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim currentRow As Long
    
    ' Set your master sheet (adjust the sheet name if needed)
    Set targetSheet = ThisWorkbook.Sheets("Sheet1")
    
    ' Find the last row with data in column F
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "F").End(xlUp).Row
    
    ' Loop through each cell starting from F4
    For currentRow = 4 To lastRow
        With targetSheet.Cells(currentRow, "F")
            If .HasFormula Then
                ' Extract path and write it to column G
                targetSheet.Cells(currentRow, "G").Value = GetWorkbookPathFromLink(.Cells)
            Else
                targetSheet.Cells(currentRow, "G").Value = "No link present"
            End If
        End With
    Next currentRow
End Sub

Key Details: #

• We use cell.Formula instead of cell.Value because the actual link string (like 'C:\Users\Documents\[Input.xlsx]Sheet1'!E29) is stored in the cell's formula—Value only shows the linked data.
• The function targets the ] character because it marks the end of the workbook path/filename segment in external links.
• Uncomment the optional Replace line if you want to remove the single quotes from the final output (e.g., convert 'C:\Users\Documents\[Input.xlsx] to C:\Users\Documents\[Input.xlsx]).

内容的提问来源于stack exchange，提问作者Diana