VBA引用关闭工作簿工作表单元格公式语法修正求助

你的问题核心在于外部工作簿单元格引用的格式拼接错误，正确的跨关闭工作簿引用格式应该是：

'文件路径\[工作簿名.xlsx]工作表名'!单元格地址

而你的代码直接将工作表名拼在完整文件路径后，导致出现[Group View_2016.xlsxMonth KPI Dec16]这种错误格式（把工作表名塞进了工作簿名的[]里）。下面是具体的修正方案：

步骤1：添加辅助函数处理引用格式 #

先写一个小函数自动生成正确的外部引用字符串，避免手动拼接出错：

Function GetExternalRef(filePath As String, sheetName As String) As String
    Dim fileName As String
    Dim pathOnly As String
    
    ' 从完整路径中拆分出文件名和单独的路径
    fileName = Dir(filePath)
    pathOnly = Left(filePath, Len(filePath) - Len(fileName))
    
    ' 按照Excel要求的格式拼接：'路径\[文件名]工作表名'
    GetExternalRef = "'" & pathOnly & "[" & fileName & "]" & sheetName & "'"
End Function

步骤2：修正循环中的公式拼接 #

将原来循环里的公式全部替换为使用上述函数的版本，同时修复那些只写了工作表名（缺少路径和工作簿）的错误公式：

修正后的完整代码片段：

Dim Wb As Workbook
Set Wb = ThisWorkbook ' 确保指向当前工作簿，可根据实际情况调整

answer = MsgBox("To get the December KPI values from the previous year folder click Yes, if you want to go to default path, click No and If you are not sure, click Cancel", vbYesNoCancel + vbQuestion, "User Specified Path")
If answer = vbYes Then
    MyFile = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")
    If MyFile = False Then Exit Sub ' 用户取消选择文件时直接退出
    Set wkb = Workbooks.Open(MyFile, UpdateLinks:=0)
    
TryAgain:
    xName = InputBox("Enter sheet name to find in the workbook: )", "Sheet search")
    If xName = "" Or xName = False Then Exit Sub
    found = False
    
    On Error Resume Next
    Sheets(xName).Activate
    If Err.Number = 0 Then ' 更可靠的工作表存在判断方式
        MsgBox ("Sheet Found")
        found = True
        Dim externalRef As String
        externalRef = GetExternalRef(MyFile, xName) ' 提前生成引用字符串，避免重复计算
        
        For j = 3 To 17
            ' 第16行公式：修正引用格式
            Wb.Sheets(Name).Cells(16, j).Formula = "=(R[-13]C-" & externalRef & "!R[-13]C)/" & externalRef & "!R[-13]C"
            ' 第17-20行：添加正确的外部引用（原代码仅写了工作表名，缺少路径和工作簿）
            Wb.Sheets(Name).Cells(17, j).Formula = "=(R[-13]C-" & externalRef & "!R[-13]C)/" & externalRef & "!R[-13]C"
            Wb.Sheets(Name).Cells(18, j).Formula = "=(R[-13]C-" & externalRef & "!R[-13]C)/" & externalRef & "!R[-13]C"
            Wb.Sheets(Name).Cells(19, j).Formula = "=(R[-13]C-" & externalRef & "!R[-13]C)/" & externalRef & "!R[-13]C"
            Wb.Sheets(Name).Cells(20, j).Formula = "=(R[-13]C-" & externalRef & "!R[-13]C)/" & externalRef & "!R[-13]C"
            ' 第21行：修正引用格式
            Wb.Sheets(Name).Cells(21, j).Formula = "=R[-12]C -" & externalRef & "!R[-12]C"
            ' 第22行：修正引用格式
            Wb.Sheets(Name).Cells(22, j).Formula = "=R[-9]C -" & externalRef & "!R[-9]C"
            ' 第23行：修正引用格式
            Wb.Sheets(Name).Cells(23, j).Formula = "=IFERROR(((R[-9]C- " & externalRef & "!R[-9]C)/" & externalRef & "!R[-9]C),0)"
        Next j
        wkb.Close SaveChanges:=False ' 关闭源工作簿时不保存，避免意外修改
    End If
    On Error GoTo 0 ' 恢复默认错误处理
    
    If found = False Then
        If MsgBox("Worksheet name does not exist, click OK to try again or Click Cancel to Exit", vbOKCancel) = vbCancel Then Exit Sub
        GoTo TryAgain
    End If
End If

额外优化说明 #

1. 更可靠的错误判断：替换原代码依赖ActiveSheet的判断方式，改用Err.Number检查工作表是否存在，避免因工作表保护等场景误判。
2. 提升代码效率：提前生成externalRef字符串，避免循环内重复调用函数。
3. 增加边界处理：添加用户取消文件选择的判断逻辑，防止后续代码报错。
4. 明确关闭行为：关闭源工作簿时指定SaveChanges:=False，防止意外修改源文件。

修改后生成的公式会完全符合你需要的正确格式：
=(C3-'E:\John\2016\[Group View_2016.xlsx]Month KPI Dec16'!C3)/'E:\John\2016\[Group View_2016.xlsx]Month KPI Dec16'!C3

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