如何用单元格作为超链接目标路径及工作簿名？（VBA/公式实现）

Your formula is throwing a #NAME? error due to two critical syntax mistakes. Let's break down the fixes and get your hyperlinks working smoothly:

Corrected Formula for Excel Tables #

If you’re using an Excel Table (evidenced by your [@SITE] and [@FULLNAME] references), use this adjusted formula:

=HYPERLINK("C:\Users\ME\Desktop\folder1\folder2\" & [@SITE] & "\" & [@FULLNAME] & ".xlsm", [@FULLNAME])

What Was Wrong With the Original Formula? #

• Incorrect quote usage: You used single quotes (') around the base path, but Excel requires double quotes (") to enclose text strings. The original 'C':\Users... structure was invalid syntax—Excel couldn’t recognize it as a valid text string, hence the #NAME? error.
• Mixed path separators: While Excel sometimes tolerates forward slashes (/), Windows natively uses backslashes (\) for file paths. Switching to backslashes ensures full compatibility.

Formula for Regular Ranges (Non-Table) #

If you aren’t using an Excel Table, replace structured references with cell addresses. For example, if your first data row is row 2 (A2 = SITE, N2 = FULLNAME):

=HYPERLINK("C:\Users\ME\Desktop\folder1\folder2\" & A2 & "\" & N2 & ".xlsm", N2)

Drag this formula down to apply it to all 3200 rows.

VBA Alternative (Bulk Hyperlink Creation) #

For large datasets like yours, a VBA script can automate hyperlink creation in one go:

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 this code:

Sub CreateHyperlinks()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim basePath As String
    
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace with your sheet name
    basePath = "C:\Users\ME\Desktop\folder1\folder2\"
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Find last data row in column A
    
    For i = 2 To lastRow ' Start at row 2 (assuming row 1 is headers)
        Dim siteFolder As String
        Dim fileName As String
        Dim fullPath As String
        
        siteFolder = ws.Cells(i, "A").Value
        fileName = ws.Cells(i, "N").Value
        fullPath = basePath & siteFolder & "\" & fileName & ".xlsm"
        
        ' Insert hyperlink into column O (adjust column letter as needed)
        ws.Hyperlinks.Add _
            Anchor:=ws.Cells(i, "O"), _
            Address:=fullPath, _
            TextToDisplay:=fileName
    Next i
End Sub

4. Update the sheet name ("Sheet1") and target column ("O") to match your setup.
5. Run the macro (press F5 in the VBA Editor, or assign it to a workbook button).

This script will loop through all rows, build the full file path, and insert hyperlinks directly into your desired column—no formula dragging required.

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