VBA & Macro Problems

How to Fix Excel VBA SaveAs and SaveCopyAs Cross-Platform Path Errors

Tauseeq MagsiTauseeq Magsi Nov 4, 2026 870 views

Question details

The user needs a VBA macro to dynamically save an Excel workbook using a file name stored in a specific cell, ensuring the script functions correctly on both Windows and macOS.

How to Fix Excel VBA SaveAs and SaveCopyAs Cross-Platform Path Errors
Product
Excel
Device & OS
Windows and macOS
Scenario
Creating an Excel template with a macro button that triggers a SaveAs or SaveCopyAs operation to a dynamic file path using data from cell X27.
Observed behavior
Using SaveCopyAs with hard-coded paths results in runtime error 1004, and the macro fails to locate the correct directory when switching between Windows and macOS environments.
Before you start

Ensure your Excel workbook is already saved as a Macro-Enabled Workbook (.xlsm) and verify that your target naming cell does not contain any illegal file name characters.

Solution 1Recommended

Use Application.PathSeparator in a Standard Module

Move your VBA code to a standard module and use the built-in Application.PathSeparator property to automatically generate the correct folder paths for both Windows and macOS.

Hard-coding backslashes (\) in VBA file paths will cause your macro to break on macOS, which uses colons (:) or forward slashes (/) for directories. Application.PathSeparator dynamically detects the operating system and applies the correct slash.

Additionally, keeping macro code that saves the workbook inside the 'ThisWorkbook' object can sometimes cause reference issues; standard modules are more stable for these operations.

1
Move code to a Standard Module

Open the VBA Editor (Alt + F11), right-click on your project in the Project Explorer, select 'Insert' > 'Module', and move your save macro from 'ThisWorkbook' into this new standard module.

2
Implement Application.PathSeparator

Update your directory path code to dynamically combine the workbook path and the separator. For example: path = ThisWorkbook.Path & Application.PathSeparator

3
Sanitize the dynamic file name

Ensure the value fetched from your naming cell (e.g., Range("X27").Value) is stripped of invalid characters such as \ / < > ? * : " or |.

4
Execute the SaveAs command

Combine your cross-platform path and sanitized file name, then run ActiveWorkbook.SaveAs Filename:=path & fileName

Use Application.PathSeparator in a Standard Module
Consider Using a Template Format: If this file is meant to be reused frequently, consider saving the master file as a Macro-Enabled Template (.xltm). This forces users to save a new copy by default, preventing accidental overwrites.
Run Macros Seamlessly in WPS

Write and Execute VBA Macros Using WPS Spreadsheets

WPS Spreadsheets provides robust support for VBA macros, allowing you to run cross-platform scripts and automate your workflow with high compatibility and performance.

  1. 1. Open your macro workbook: Launch WPS Spreadsheets and open your .xlsm file containing the cross-platform save macro.
  2. 2. Access the VBA Editor: Navigate to the 'Developer' tab on the top ribbon and click on 'Visual Basic' to launch the integrated code editor.
  3. 3. Run your automation: Click the 'Run' button or trigger your assigned form button in the spreadsheet to seamlessly execute the SaveAs operation.
Fully compatible with Microsoft Excel .xlsm and .xltm file formats.Built-in VBA editor for writing, debugging, and executing macros.Lightweight application design for faster processing speeds.Familiar interface that requires zero learning curve for Excel users.
QA img-9

Frequently Asked Questions

Why do I get Error 1004 when using SaveCopyAs in VBA?

Run-time error 1004 typically occurs if the file path you are trying to save to does not exist, if you lack write permissions, or if the generated file name contains illegal characters.

What characters are invalid for saving Excel files?

File names in both Windows and macOS environments cannot contain characters such as \ / < > ? * : " or |. If your VBA script dynamically pulls a name containing any of these, the save operation will fail.

How does Application.PathSeparator solve macOS compatibility?

Application.PathSeparator is a built-in VBA property that automatically returns the correct folder separator character based on the operating system running the script (a backslash for Windows, and a forward slash or colon for macOS), preventing broken file paths.