Excel VBA: Create and Save Files to User’s Desktop with Special Characters

Are you struggling to save Excel files to the user’s desktop using VBA when your filenames contain special characters? This common issue can cause frustration due to file path errors. In this tutorial, we’ll explain why it happens and provide a step-by-step solution to ensure your files are saved correctly every time.

Why It Happens

The issue arises because certain characters like \ / : * ? ” | are illegal in file names on Windows systems. If these characters are present in your filename variables, Excel’s SaveAs method will throw a runtime error (400).

Step-by-Step Solution

To resolve this issue, follow the steps below to ensure your VBA code correctly handles filenames with special characters and saves files to the user’s desktop:

Step 1: Clean Illegal Characters from Filenames

Create a helper function that removes illegal characters from filenames. This function will be used in conjunction with your SaveAs method.

Function CleanFileName(s As String) As String
    Dim i As Integer
    Dim BadChars As String
    BadChars = "\/:*?\"|"
    For i = 1 To Len(BadChars)
        s = Replace(s, Mid(BadChars, i, 1), "")
    Next i
    CleanFileName = Trim(s)
End Function

Step 2: Determine Desktop Path

Use the Environ function to dynamically obtain the user’s desktop path in your VBA code. This ensures that your script is compatible across different systems.

Dim fpath As String
fpath = Environ("UserProfile") & "\Desktop\"

Step 3: Build the Filename

Combine the cleaned filename with the desktop path before saving. This ensures that filenames with special characters are handled correctly.

Dim vesselName As String
Dim MBLNo As String
vesselName = "Example Vessel"
MBLNo = "123/456"  ' Example containing illegal character

' Clean the filename and save to desktop
Dim fname As String
fname = fpath & CleanFileName(vesselName) & " - " & CleanFileName(MBLNo) & ".xlsx"

ThisWorkbook.SaveAs Filename:=fname, FileFormat:=xlOpenXMLWorkbook

Step 4: Full Example Code

Here is the complete VBA code combining all the steps above:

Function CleanFileName(s As String) As String
    Dim i As Integer
    Dim BadChars As String
    BadChars = "\/:*?\"|"
    For i = 1 To Len(BadChars)
        s = Replace(s, Mid(BadChars, i, 1), "")
    Next i
    CleanFileName = Trim(s)
End Function

Sub SaveToDesktop()
    Dim fpath As String
    fpath = Environ("UserProfile") & "\Desktop\"

    Dim vesselName As String
    Dim MBLNo As String
    ' Example values (you can set these based on your actual data)
    vesselName = "Example Vessel"
    MBLNo = "123/456"  ' Contains illegal character

    ' Clean the filename and save to desktop
    Dim fname As String
    fname = fpath & CleanFileName(vesselName) & " - " & CleanFileName(MBLNo) & ".xlsx"

    ThisWorkbook.SaveAs Filename:=fname, FileFormat:=xlOpenXMLWorkbook
End Sub

Extra Tip: Error Handling for Missing Values

Consider adding error handling to your VBA code in case the variables (vesselName or MBLNo) might be empty. This ensures that you won’t get an unexpected error when saving the file.

Sub SaveToDesktop()
    On Error GoTo ErrorHandler

    Dim fpath As String
    fpath = Environ("UserProfile") & "\Desktop\"

    Dim vesselName As String
    Dim MBLNo As String

    ' Example values (you can set these based on your actual data)
    If Len(vesselName) = 0 Or Len(MBLNo) = 0 Then
        MsgBox "Vessel name or MBL number is missing. Please ensure both are provided."
        Exit Sub
    End If

    ' Clean the filename and save to desktop
    Dim fname As String
    fname = fpath & CleanFileName(vesselName) & " - " & CleanFileName(MBLNo) & ".xlsx"

    ThisWorkbook.SaveAs Filename:=fname, FileFormat:=xlOpenXMLWorkbook
    MsgBox "File saved successfully!"

    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description
End Sub

Conclusion

Handling filenames with special characters and ensuring that files are saved to the user’s desktop can be tricky in VBA. By using a helper function to clean illegal characters from filenames and dynamically determining the desktop path, you can avoid runtime errors and ensure smooth file saving processes.

This solution will save you time debugging and make your Excel macros more robust and reliable. Now that you have a clear understanding of how to handle special character issues in filenames, why not explore CelTools for additional tools to enhance your Excel automation? Happy coding!

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical