VBA如何将Workbooks中的固定文件名替换为单元格A1存储的值？

实现步骤&修改后代码 #

你只需要把原代码中硬编码的文件名替换为A1单元格的引用即可，以下是完整修改后的代码，额外增加了错误判断避免目标工作簿未打开时程序报错：

Private Sub CheckBox1_Click()
    Dim targetWbName As String
    Dim targetWb As Workbook
    Dim targetWs As Worksheet
    Dim cell As Range
    
    ' 读取A1单元格存储的目标文件名，如需修改A1所在工作表可自行调整此处引用
    targetWbName = ThisWorkbook.ActiveSheet.Range("A1").Value
    
    ' 判断目标工作簿是否已打开
    On Error Resume Next
    Set targetWb = Workbooks(targetWbName)
    On Error GoTo 0
    If targetWb Is Nothing Then
        MsgBox "目标工作簿未打开，请检查A1单元格文件名是否正确", vbExclamation
        Exit Sub
    End If
    Set targetWs = targetWb.Worksheets("Planner")
    
    ' 通用配置统一设置，减少冗余代码
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    If CheckBox1.Value = True Then
        With targetWs
            .Rows("11:1897").EntireRow.Hidden = False
            For Each cell In .Range("BE10:BE1897")
                If cell.Value = 1 Then cell.EntireRow.RowHeight = 0.00001
            Next cell
            .Range("Y2:AF2").FormulaR1C1 = "S&S Days"
            .Range("A1").Select
        End With
    Else
        With targetWs
            For Each cell In .Range("BE10:BE1897")
                If cell.Value = 1 Then cell.EntireRow.RowHeight = 12.75
            Next cell
            .Range("Y2:AF2").FormulaR1C1 = "All"
            .Range("A1").Select
        End With
    End If
    
    ' 恢复应用配置
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

关键改动说明 #

• 新增变量读取A1单元格的文件名，所有原硬编码的工作簿引用都替换为该变量值，后续修改文件名只需要更新A1单元格内容即可
• 所有Range引用前都增加了工作表对象限定，避免激活工作表不及时导致的范围引用错误
• 提取了重复的应用状态配置代码，减少冗余，提升运行效率
• 如果存储文件名的A1不在当前运行代码的工作簿的活动工作表，只需要修改targetWbName = ThisWorkbook.ActiveSheet.Range("A1").Value中的工作表引用即可，比如改为ThisWorkbook.Sheets("配置表").Range("A1").Value

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