Excel VBA导入数据异常：文件资源管理器可打开但无法复制粘贴

核心问题定位 #

你遇到的下标越界错误，直接原因是ImportWorkbook.Worksheets(2)引用失败——选中的目标工作簿里不存在索引为2的工作表（Excel工作表索引从1开始计数）。你怀疑第11行（Set ImportWorkbook = Workbooks.Open(...)）有问题，实际是后续的工作表引用逻辑忽略了不同文件的工作表结构差异。

另外原代码还有一个隐性bug：FileLocation被定义为String类型，但Application.GetOpenFilename返回的是Variant——当用户取消选择时，它返回的是布尔值False，而非字符串"False"，原判断逻辑会失效，可能导致后续执行异常。

修正后的完整代码 #

Sub ImportData()
    Dim FileLocation As Variant
    Dim ImportWorkbook As Workbook
    Dim TargetWS As Worksheet
    
    ' 打开文件选择对话框，仅允许选择Excel格式文件
    FileLocation = Application.GetOpenFilename( _
        FileFilter:="Excel文件 (*.xlsx;*.xls;*.xlsm), *.xlsx;*.xls;*.xlsm", _
        Title:="请选择要导入的工作簿")
    
    ' 判断用户是否取消选择
    If FileLocation = False Then
        Beep
        Exit Sub
    End If
   
    Application.ScreenUpdating = False
    
    On Error GoTo Cleanup ' 错误捕获，避免程序崩溃
    
    ' 打开选中的工作簿
    Set ImportWorkbook = Workbooks.Open(Filename:=FileLocation)
    
    ' 方案1：改用工作表名称引用（推荐，避免索引变动问题）
    ' 替换成目标文件中实际的工作表名称，比如"数据导入表"
    Set TargetWS = ImportWorkbook.Worksheets("Sheet2")
    
    ' 方案2：如果必须用索引，先检查工作表数量是否足够
    ' If ImportWorkbook.Worksheets.Count >= 2 Then
    '     Set TargetWS = ImportWorkbook.Worksheets(2)
    ' Else
    '     MsgBox "目标工作簿至少需要2个工作表！"
    '     GoTo Cleanup
    ' End If
    
    ' 复制数据到当前工作簿指定区域
    TargetWS.Range("F11:Q18").Copy ThisWorkbook.Worksheets("Analysis (blk sub)").Range("B14:M21")
    
Cleanup:
    ' 无论是否出错，都关闭导入工作簿并恢复屏幕更新
    If Not ImportWorkbook Is Nothing Then
        ImportWorkbook.Close SaveChanges:=False
    End If
    Application.ScreenUpdating = True
    
    ' 如果有错误，提示详细信息
    If Err.Number <> 0 Then
        MsgBox "执行出错：" & Err.Description, vbExclamation
    End If
End Sub

关键修正点说明 #

• 修正文件选择判断逻辑：把FileLocation改为Variant类型，正确识别用户取消选择的布尔值False，避免无效的文件打开操作。
• 显式声明对象类型：添加Dim ImportWorkbook As Workbook和Dim TargetWS As Worksheet，避免变体类型带来的潜在问题。
• 工作表引用优化：
  • 优先推荐按工作表名称引用（比如Worksheets("Sheet2")），因为不同文件的工作表索引可能因添加/删除表而变动，名称引用更稳定。
  • 如果必须用索引，先通过Worksheets.Count检查目标工作簿的工作表数量，避免下标越界。
• 添加错误捕获：通过On Error GoTo Cleanup确保无论是否出错，都能关闭导入工作簿并恢复屏幕更新，避免资源占用。

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