使用VBA从外部工作簿更新单元格失败求助

原始代码 #

Dim FilePath As String
Dim ExternalWorkbook As Workbook
Dim ExternalWorksheet As Worksheet

' Get the file path from cell A1
FilePath = Range("A1").Value

' Activate the worksheet where you want to update cells
Worksheets("Sheet1").Activate

' Open the external workbook
Set ExternalWorkbook = Workbooks.Open(FilePath)

' Set the external worksheet to retrieve data from
Set ExternalWorksheet = ExternalWorkbook.Worksheets("Sheet1")

' Update cells in the current worksheet with data from the external worksheet
Range("D5:W5").Value = ExternalWorksheet.Range("D5:W5").Value

' Close the external workbook without saving changes
ExternalWorkbook.Close SaveChanges:=False

' Notify the user that the update is complete
MsgBox "Cells updated successfully!"
End Sub

问题描述 #

运行宏的工作簿和待提取数据的外部工作簿中，均有约20个月的现金流数据，且现金流都位于D5:W5区域。运行上述VBA代码时，能看到外部工作簿短暂打开，也收到了更新成功的提示，但目标单元格并未更新。

排查与解决方案 #

1. 核心问题：未明确指定目标工作簿/工作表 #

原始代码中Range("D5:W5").Value没有绑定具体的工作簿，当外部工作簿打开后，活动工作簿会自动切换为外部文件，导致数据实际写入到了外部工作簿的D5:W5区域，而非你原本的工作簿。

2. 修正后的代码 #

通过ThisWorkbook明确指定当前宏所在的工作簿，避免活动工作簿切换带来的歧义：

Sub UpdateCashFlow()
    Dim FilePath As String
    Dim SourceWB As Workbook
    Dim SourceWS As Worksheet
    Dim TargetWS As Worksheet
    
    ' 绑定当前宏所在工作簿的目标工作表
    Set TargetWS = ThisWorkbook.Worksheets("Sheet1")
    
    ' 从当前工作簿的A1读取文件路径
    FilePath = TargetWS.Range("A1").Value
    
    ' 提前校验文件是否存在
    If Dir(FilePath) = "" Then
        MsgBox "指定文件不存在，请检查路径！"
        Exit Sub
    End If
    
    ' 打开外部工作簿
    Set SourceWB = Workbooks.Open(FilePath)
    ' 绑定外部工作簿的数据源工作表
    Set SourceWS = SourceWB.Worksheets("Sheet1")
    
    ' 明确将外部数据写入目标工作表的指定区域
    TargetWS.Range("D5:W5").Value = SourceWS.Range("D5:W5").Value
    
    ' 关闭外部工作簿
    SourceWB.Close SaveChanges:=False
    
    MsgBox "数据更新完成！"
End Sub

3. 额外排查点 #

• 工作表名称校验：确认外部工作簿的工作表确实叫Sheet1，如果是中文名称（如"现金流表"）或带空格，需同步修改代码中的工作表名称。
• 单元格状态检查：目标区域D5:W5是否被工作表保护锁定？如果是，需在写入前添加TargetWS.Unprotect Password:="你的密码"，写入后再重新保护。
• 数据格式兼容性：外部工作簿的D5:W5是否存在合并单元格、隐藏列或特殊格式？这类情况可能导致赋值失败，可尝试单个单元格测试（如TargetWS.Range("D5").Value = SourceWS.Range("D5").Value）。
• 文件路径有效性：确认A1中的路径是完整绝对路径（如C:\Users\XXX\Documents\现金流数据.xlsx），且文件未被其他程序锁定或设为只读。

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