VBA Workbooks.Open执行时出现Error 9下标越界问题求助

问题描述 #

一段此前正常运行的VBA代码，当前执行Set wb = Workbooks.Open(FolderPath & FilePath)时触发「Error 9 下标越界」错误。奇怪的是，文件夹中的第一个文件能成功打开，但循环执行到该行代码时仍会报错。尝试过更换文件、重写宏、修改路径、移除Set直接调用Workbooks.Open等操作，问题仍未解决。

原代码如下：

Sub OpenFilesFromFolder()
    Dim wb As Workbook
    Dim FolderPath As String
    Dim FilePath As String
        FolderPath = "S:\GESTION_ASS\Coordination Internationale\Global\_Monthly Asset Inventory Report (MAIRe)\1. Consolidation\Country data for macro\"
        FilePath = Dir(FolderPath & "*.xls*")
        Do While FilePath <> ""
            Set wb = Workbooks.Open(FolderPath & FilePath)
            FilePath = Dir
        Loop
End Sub

解决方案 #

1. 排查文件是否已被打开 #

如果文件夹内存在已在Excel中打开的文件，Workbooks.Open尝试打开同名文件时会触发下标越界错误。可以在打开前先判断文件状态：

Function IsWorkbookOpen(FileName As String) As Boolean
    Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks(FileName)
    On Error GoTo 0
    IsWorkbookOpen = Not wb Is Nothing
End Function

' 在原循环中添加判断
Do While FilePath <> ""
    If Not IsWorkbookOpen(FilePath) Then
        Set wb = Workbooks.Open(FolderPath & FilePath)
    Else
        ' 可选：激活已打开的工作簿
        Workbooks(FilePath).Activate
    End If
    FilePath = Dir
Loop

2. 改用FileSystemObject遍历文件 #

Dir函数在处理特殊文件名、权限限制文件时可能出现异常，改用FileSystemObject能更稳定地遍历文件夹内文件：

Sub OpenFilesWithFSO()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim wb As Workbook
    Dim FolderPath As String
    
    FolderPath = "S:\GESTION_ASS\Coordination Internationale\Global\_Monthly Asset Inventory Report (MAIRe)\1. Consolidation\Country data for macro\"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(FolderPath)
    
    For Each file In folder.Files
        ' 仅处理Excel文件
        If LCase(fso.GetExtensionName(file.Name)) Like "xls*" Then
            Set wb = Workbooks.Open(file.Path)
        End If
    Next file
    
    Set fso = Nothing
    Set folder = Nothing
End Sub

3. 检查路径与文件权限 #

• 确认目标文件夹内无损坏的Excel文件，或无系统生成的临时文件（如开头带~$的隐藏文件），这类文件可能导致Workbooks.Open执行失败。
• 检查当前用户对目标文件夹及文件的读写权限，权限不足也可能触发异常。

4. 验证Excel信任设置 #

若近期修改过Excel信任中心设置，需确认目标文件夹已添加至「受信任位置」：

1. 打开Excel选项 → 信任中心 → 信任中心设置
2. 选择「受信任位置」，添加目标文件夹路径

内容的提问来源于stack exchange，提问作者Maxime Le coz