在新打开的工作簿中引用工作表时遇“下标越界”错误求助

错误原因分析 #

你遇到的“下标越界”错误核心原因是：

• Workbooks(myFile)中使用了完整文件路径作为索引，但Workbooks集合仅识别打开文件的文件名（含后缀），而非完整路径，导致无法匹配到目标工作簿。
• 未处理用户取消文件选择的场景，此时myFile会返回False，后续执行Workbooks.Open会触发额外错误。
• 代码中myData赋值为单元格区域值后，直接调用PasteSpecial是无效操作——数组无法通过剪贴板粘贴方法写入目标区域。

修正方案及代码 #

关键修正点 #

1. 从完整路径中提取文件名：用Dir(myFile)函数获取纯文件名，作为Workbooks集合的索引。
2. 增加取消选择判断：用户取消操作时直接退出子程序，避免后续报错。
3. 优化数据复制逻辑：直接将数组赋值给目标区域，无需依赖剪贴板，更高效可靠。

修正后的完整代码 #

Sub OpenExcelFile()
    'Declare variables
    Dim myFile As Variant '改用Variant接收返回值，兼容取消选择的False情况
    Dim myFileName As String
    Dim myData As Variant
    Dim mySheet As Worksheet
    Dim sourceWB As Workbook
    
    'Get the path to the Excel file
    myFile = Application.GetOpenFilename(Title:="Select an Excel file", MultiSelect:=False)
    
    '处理用户取消选择的情况
    If myFile = False Then Exit Sub
    
    '打开文件并保存工作簿引用，避免重复查找
    Set sourceWB = Workbooks.Open(myFile)
    '提取纯文件名
    myFileName = Dir(myFile)
    
    '获取目标工作表
    Set mySheet = sourceWB.Sheets("Sheet1") '直接使用已保存的工作簿引用更可靠
    
    '读取数据到数组
    myData = mySheet.Range("A14:A83").Value
    
    '将数组直接赋值到目标区域，自动匹配尺寸
    ActiveSheet.Range("O15").Resize(UBound(myData, 1), UBound(myData, 2)).Value = myData
    
    '可选：关闭源工作簿，根据需求决定是否保存更改
    sourceWB.Close SaveChanges:=False
End Sub

额外注意事项 #

• 确认源文件中确实存在名为Sheet1的工作表，若工作表名称不同，需修改代码中对应的字符串。
• 尽量避免使用ActiveSheet，建议指定具体工作表（例如ThisWorkbook.Sheets("目标表名")），防止因激活工作表变化导致错误。

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