VBA & Macro Problems

How to Fix VBA Run-Time Error 52 When Saving a JSON File

Maira Mehtab Sep 22, 2026 868 views

Question details

The user is encountering VBA run-time error 52 while attempting to execute a macro that saves data to a JSON file.

Product: VBA Macros (Spreadsheets)
Device & OS: not provided
Scenario: Running a VBA macro script intended to output or save data into a localized JSON file.
Observed behavior: The script execution halts, and the system throws a 'Run-time error 52: Bad file name or number' dialog instead of saving the JSON file.

Before you start

Before proceeding, click 'Debug' on the error dialog to identify the exact line of code causing the issue. Make a note of the exact file path and file name your macro is attempting to use.

Solution 1Recommended

Verify File Path and Name Validity

Ensure the file path used in your VBA code exists and the filename contains no restricted characters.

Run-time error 52 usually means VBA cannot recognize the file path. This happens if the destination folder does not exist, the string contains illegal characters, or the drive letter is incorrect.

Inspect the Path String

In the VBA editor, hover over your file path variable or use the Immediate Window (by typing `?yourFilePathVariable` and pressing Enter) to see the exact string being evaluated.

Validate Characters

Ensure the JSON file name does not contain any illegal characters such as <, >, :, ", /, \, |, ?, or *.

Confirm Directory Exists

VBA's standard Open statement cannot create new folders automatically. Navigate your computer's file explorer to manually verify that the target directory actually exists.

Check String Concatenation

If you are building the path dynamically, ensure there is a backslash (\) between the folder path and the file name (e.g., `C:\Folder\` & `data.json`).

Dynamic Paths: Instead of hardcoding paths like 'C:\Users\Name\Desktop', consider using 'ThisWorkbook.Path & "\data.json"' to keep paths relative and prevent errors when moving the file.

Solution 2

Resolve File Access and Network Permissions

Check if the target file is locked, restricted by network permissions, or mapped to a disconnected drive.

Free Microsoft Office alternative

Test and Debug Your VBA Scripts in WPS Office

If you are dealing with persistent VBA issues, consider utilizing WPS Office. It provides robust VBA support, high compatibility with Excel macros, and a lightweight environment to test and debug your scripts effortlessly without the heavy overhead.

1. Install WPS Office: Download and install WPS Office on your computer.
2. Open Your Macro File: Launch WPS Spreadsheets and open your macro-enabled (.xlsm) file.
3. Access the VBA Editor: Navigate to the 'Developer' tab and click on 'Visual Basic' to launch the integrated code editor.
4. Run and Debug: Execute your JSON export script to test its compatibility and isolate the path error seamlessly.

Native support for writing, editing, and running VBA macros.Fully compatible with Microsoft Excel macro-enabled formats (.xlsm and .xltm).Lightweight installation and fast loading times for smooth execution of complex data scripts.Familiar interface makes debugging straightforward with zero learning curve.Cost-effective, highly accessible alternative to traditional office suites.

Frequently Asked Questions

What exactly does VBA Run-time error 52 mean?

Error 52 'Bad file name or number' is thrown when VBA attempts to access a file using an invalid path string, a drive that does not exist, or when the file name contains restricted characters.

Can I use relative paths when saving a JSON file via VBA?

While VBA can use relative paths, they rely on the current working directory which may unexpectedly change. It is highly recommended to construct an absolute path dynamically using `ThisWorkbook.Path` (e.g., `ThisWorkbook.Path & "\output.json"`).

Why does my macro work on my PC but throw Error 52 on a colleague's computer?

This commonly happens if the hardcoded file path includes your specific Windows username (e.g., C:\Users\YourName\...) or points to a mapped network drive that is not configured on your colleague's machine.

How do I correctly format a file path string in VBA?

Ensure you use backslashes (\) to separate folders on Windows. Always verify that when concatenating folder paths and file names, a backslash sits between them to prevent merging the folder name and file name into an invalid string.

Excel & Data Problems