This error can occur if Excel encounters an issue related to the file name or file path.
Here are some methods that you can try-
- File locking can prevent Excel from creating or overwriting the file. Check if the file is locked by another process or application.
- Try specifying a different folder location (e.g., your desktop) to see if the issue is specific to the current directory.
- Ensure that you have the necessary permissions to create files in the specified directory.
Also, refer to this thread- https://stackoverflow.com/questions/25064160/run-time-error-52-bad-file-name-or-number