求助：编写VBA宏实现Excel文件按客户+日期命名保存失败

Let’s walk through what’s likely causing your macro to fail, then fix it up with a more robust version that handles common edge cases:

First, here are the key issues in your original code:

• Undefined variable: You use fname but never declare it. VBA allows this, but it’s a prime source of typos and hidden bugs.
• Empty path risk: If your workbook hasn’t been saved before, ActiveWorkbook.Path returns an empty string—this breaks the save path entirely.
• Invalid filename characters: The client name from cell G11 might include characters Windows blocks in filenames (like /\:*?"<>|), which will crash the save process.
• No safety checks: There’s no validation for missing sheets, empty client names, or existing files with the same name, leading to unexpected errors.

Here’s a revised, reliable version of your macro with fixes for all these issues, plus comments to explain each step:

Option Explicit ' Forces you to declare all variables (stops sneaky typos/bugs)

Sub SaveWithClientAndDate()
    Dim wsTool As Worksheet
    Dim fclient As String
    Dim path As String
    Dim fname As String
    Dim safeClientName As String
    Dim saveFullPath As String
    
    ' First, confirm the "Tool" worksheet exists
    On Error Resume Next
    Set wsTool = ThisWorkbook.Sheets("Tool")
    On Error GoTo 0
    
    If wsTool Is Nothing Then
        MsgBox "Can't find the 'Tool' worksheet—double-check it exists!", vbExclamation
        Exit Sub
    End If
    
    ' Unprotect the sheet
    wsTool.Unprotect Password:="xxxx"
    
    ' Get the client name (trim extra spaces to avoid empty names)
    fclient = Trim(wsTool.Range("G11").Value)
    If fclient = "" Then
        MsgBox "Cell G11 is empty! Please enter a client name first.", vbExclamation
        wsTool.Protect Password:="xxxx" ' Re-protect before exiting
        Exit Sub
    End If
    
    ' Clean up the client name: replace illegal filename characters with underscores
    safeClientName = Replace(fclient, "/", "_")
    safeClientName = Replace(safeClientName, "\", "_")
    safeClientName = Replace(safeClientName, ":", "_")
    safeClientName = Replace(safeClientName, "*", "_")
    safeClientName = Replace(safeClientName, "?", "_")
    safeClientName = Replace(safeClientName, """", "_")
    safeClientName = Replace(safeClientName, "<", "_")
    safeClientName = Replace(safeClientName, ">", "_")
    safeClientName = Replace(safeClientName, "|", "_")
    
    ' Get the save path: use existing path if workbook is saved, else let user pick a folder
    path = ThisWorkbook.Path
    If path = "" Then
        With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Choose a folder to save your file"
            If .Show = -1 Then
                path = .SelectedItems(1)
            Else
                MsgBox "No save path selected—macro canceled.", vbExclamation
                wsTool.Protect Password:="xxxx"
                Exit Sub
            End If
        End With
    End If
    
    ' Build the full filename with date (add .xlsm to match macro-enabled format)
    fname = "Discount for " & safeClientName & " " & Format(Now, "DD-MM-YYYY")
    saveFullPath = path & "\" & fname & ".xlsm"
    
    ' Check if the file already exists and ask before overwriting
    If Dir(saveFullPath) <> "" Then
        If MsgBox("The file " & fname & ".xlsm already exists—do you want to overwrite it?", vbYesNo + vbQuestion) <> vbYes Then
            wsTool.Protect Password:="xxxx"
            Exit Sub
        End If
    End If
    
    ' Try to save and catch any unexpected errors
    On Error Resume Next
    ThisWorkbook.SaveAs Filename:=saveFullPath, FileFormat:=52, CreateBackup:=False
    If Err.Number <> 0 Then
        MsgBox "Save failed: " & Err.Description, vbCritical
    Else
        MsgBox "File saved successfully to:" & vbNewLine & saveFullPath, vbInformation
    End If
    On Error GoTo 0
    
    ' Re-protect the sheet
    wsTool.Protect Password:="xxxx"
End Sub

Key Improvements Breakdown: #

• Option Explicit: A VBA best practice that forces you to declare every variable, so you catch typos (like fclent instead of fclient) right away.
• Sheet existence check: Prevents errors if someone renames or deletes the "Tool" sheet.
• Safe client name: Replaces all illegal filename characters so your save doesn’t fail unexpectedly.
• Path handling: If the workbook hasn’t been saved yet, it lets you pick a folder instead of crashing.
• Empty name validation: Ensures you don’t try to save a file with no client name.
• Overwrite prompt: Gives you control instead of silently overwriting existing files.
• Error feedback: Tells you exactly what went wrong if the save fails, instead of just crashing.

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