Excel VBA SaveAs方法出现下标越界（Subscript out of range）错误求助

Hey there, let's break down why you're hitting that frustrating "Subscript out of range" error and fix it up. The root cause here is a mismatch between what Application.GetOpenFilename returns and what the Workbooks() collection expects, plus a missing step to actually open the selected file. Let's dive in:

Why the Error Happens #

When you use Application.GetOpenFilename, it returns a full file path (e.g., C:\Documents\MyFile.xlsx), not just the workbook's display name. But Workbooks(my_FileName) looks for an already open workbook using either its short name (like MyFile.xlsx) or its index number in the Workbooks collection. Since you're passing a full path instead of a valid open workbook identifier, VBA can't find the workbook you're referencing—hence the "subscript out of range" error.

Worse, your current code doesn't even open the file the user selects! GetOpenFilename only lets the user pick a file; it doesn't load it into Excel, so the workbook isn't even in the Workbooks collection to begin with.

Step-by-Step Fixes #

1. Update ProduceDoc to Open the Selected File #

First, we need to open the user's selected file and capture the actual Workbook object. This avoids relying on fragile filename strings entirely:

Sub ProduceDoc()
    Dim selectedPath As Variant
    Dim targetWB As Workbook
    Dim sFolder As String
    
    MsgBox "Please Select the File that Contains the Document"
    ' Filter for Excel files to avoid invalid selections
    selectedPath = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xls;*.xlsx;*.xlsm), *.xls;*.xlsx;*.xlsm", _
        Title:="Select Source Workbook")
    
    ' Exit if user cancels the file picker
    If selectedPath = False Then Exit Sub
    
    ' Open the selected workbook and store it in a variable
    Set targetWB = Workbooks.Open(selectedPath)
    
    ' Example: Get the save folder (replace this with your actual folder logic)
    sFolder = "C:\Your\Target\Save\Folder\" ' Or use Application.GetSaveAsFilename to let user pick
    
    ' Call SaveWorkbook with the actual Workbook object
    SaveWorkbook targetWB, sFolder
End Sub

2. Rewrite SaveWorkbook to Use a Workbook Object #

Instead of passing a string name, pass the Workbook object directly. This eliminates name mismatch issues and makes the code clearer:

Sub SaveWorkbook(targetWB As Workbook, sFolder As String)
    Dim fName As String
    Dim savePath As String
    Dim invalidChars As String
    invalidChars = "\/:*?""<>|" ' Illegal characters for Windows filenames
    
    ' Get filename from B9 (specify the exact sheet to avoid active sheet issues)
    fName = CStr(targetWB.Sheets("Sheet1").Range("B9").Value) ' Replace "Sheet1" with your sheet name
    
    ' Validate filename isn't empty
    If fName = "" Then
        MsgBox "Error: Cell B9 is empty. Please enter a filename first.", vbExclamation
        Exit Sub
    End If
    
    ' Check for illegal characters in the filename
    For i = 1 To Len(invalidChars)
        If InStr(fName, Mid(invalidChars, i, 1)) > 0 Then
            MsgBox "Error: Filename contains invalid character (" & Mid(invalidChars, i, 1) & ").", vbExclamation
            Exit Sub
        End If
    Next i
    
    ' Ensure save folder path ends with a backslash
    If Right(sFolder, 1) <> "\" Then sFolder = sFolder & "\"
    
    ' Check if save folder exists
    If Dir(sFolder, vbDirectory) = "" Then
        MsgBox "Error: Save folder does not exist. Please check the path.", vbExclamation
        Exit Sub
    End If
    
    ' Build full save path
    savePath = sFolder & fName & ".xls"
    
    ' Save as Excel 97-2003 format
    targetWB.SaveAs Filename:=savePath, FileFormat:=xlExcel8
End Sub

Bonus Tips to Avoid Future Errors #

• Always validate user inputs (empty filenames, invalid paths, illegal characters) to catch issues early.
• Use Workbook objects instead of string names whenever possible—they're far less error-prone.
• Never rely on the active sheet; always specify the exact worksheet when accessing cells (e.g., targetWB.Sheets("Data").Range("B9")).

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