关于Excel宏本地正常运行但同事端触发Run-time error '1004'的技术求助

Hey there, let's tackle this Run-time error 1004 with the SaveAs method. Even though you adjusted the file path and you're on the same Excel version, there are a few common gotchas that might be tripping up your colleague's machine. Here's what to check and how to fix it:

1. Verify Write Permissions on the Shared Path #

Even if your colleague can browse the U:\MACRO test\BREAKDOWN RAS TEST 17052021\Output IE folder, they might not have write permissions there. Ask them to manually create a new text file in that directory—if they can't, that's the root issue. You'll need to coordinate with your IT team to adjust the shared folder permissions to allow write access for their account.

2. Check for Illegal Characters in the Generated Filename #

The filename you're building pulls values from Range("A2") and Range("C2"). Windows blocks characters like \ / : * ? " < > | in filenames. If those cells contain any of these, the SaveAs will fail immediately.

To fix this, add a helper function to clean those values before using them in the filename (included in the optimized code below).

3. Fix Unreliable Workbook References #

Your original code uses Workbooks("Test Ras breakdown per markt 20211.xlsm") to reference the source workbook. If your colleague has the file open with a slightly different name (e.g., a typo, or saved as .xls instead of .xlsm), this reference breaks—leading to an invalid filename and the 1004 error.

Switch to ThisWorkbook instead, which always refers to the workbook containing the macro—no more filename mismatches.

4. Optimized Code to Avoid Instability & Add Error Handling #

Your original code relies heavily on Select and ActiveSheet, which can be unstable if the user clicks elsewhere while the macro runs. Here's a cleaned-up version with error handling to help diagnose exactly what's going wrong:

Sub nieuw4()
    Dim sourceWs As Worksheet
    Dim newWb As Workbook
    Dim outputPath As String
    Dim fileNamePart1 As String
    Dim fileNamePart2 As String
    Dim fullFileName As String
    
    ' Reference the source sheet in the macro's workbook
    Set sourceWs = ThisWorkbook.Sheets("Output IE")
    
    ' Copy the sheet to a new workbook
    sourceWs.Copy
    Set newWb = ActiveWorkbook
    
    With newWb.Sheets(1)
        ' Delete all shapes without selecting them
        .Shapes.Delete
        
        ' Protect the sheet
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        .EnableSelection = xlNoRestrictions
    End With
    
    ' Get filename components from the macro's workbook
    fileNamePart1 = ThisWorkbook.Sheets("Grid inladen").Range("A2").Value
    fileNamePart2 = ThisWorkbook.Sheets("Grid inladen").Range("C2").Value
    
    ' Clean illegal characters from filename parts
    fileNamePart1 = CleanFileName(fileNamePart1)
    fileNamePart2 = CleanFileName(fileNamePart2)
    
    ' Build full path and filename
    outputPath = "U:\MACRO test\BREAKDOWN RAS TEST 17052021\Output IE\"
    fullFileName = outputPath & "Breakdown - " & fileNamePart1 & " - " & fileNamePart2 & ".xlsx"
    
    ' Attempt save with error handling
    On Error Resume Next
    newWb.SaveAs Filename:=fullFileName, FileFormat:=xlOpenXMLWorkbook
    If Err.Number <> 0 Then
        MsgBox "Save failed: " & Err.Description & vbCrLf & "File name: " & fullFileName, vbCritical
        Err.Clear
    End If
    On Error GoTo 0
End Sub

' Helper function to remove invalid filename characters
Function CleanFileName(strName As String) As String
    Dim illegalChars As Variant
    Dim char As Variant
    
    illegalChars = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    
    For Each char In illegalChars
        strName = Replace(strName, char, "")
    Next char
    
    CleanFileName = strName
End Function

Next Diagnostic Steps #

1. Have your colleague run the optimized macro—if it fails, the message box will show the exact filename it tried to save, which will reveal if there's an issue with the name or path.
2. Test write permissions in the output folder as mentioned earlier.
3. Double-check that Range("A2") and Range("C2") in their workbook don't have weird characters or empty values.

This should cover most of the common causes for this 1004 error in your scenario.

内容的提问来源于stack exchange，提问作者Thijmen Van Der Meulen