Microsoft Visual Basic Runtime error 1004: Document not saved, The document may be open, or an error may have been encountered when saving

Question

Hello,

I've got a issue with my Macros in Excel in my PC only.

It worked just fine before and it still works on another pc with both Win 7 & Win 10, however it does not on my PC.

I have done clean installation of Win 10, at start it works fine. After few days and recent Windows update it again started throwing an error.

When I try to run excel with macro "ExportAsFixedFormat xlTypePDF", it throws an error and highlights this area when I press Debug: as shown in images.

The error thrown line is as below

Range("A1:I48").ExportAsFixedFormat xlTypePDF, Environ("Userprofile") & "\Desktop\PrintPDF.pdf"

here I am trying to export a certain range in PDF format.

Kindly Help. As I cannot clean install again and again.

Answer 1

Hi EkknathJujare,

Sorry to hear that you're experiencing this issue!

I am Bhavik, a Microsoft user like you. Please note, I am not a Microsoft employee and don’t have direct access to your account, but I will do everything I can to help you with your queries. :)

Make sure you don't have that PDF opened while running this Macro because if the PDF file is opened somewhere, Macro would not be able to save it.

I was able to reproduce the same error on my end while PDF was open.

If that's not the case then make sure that the PDF file is not locked for writing by OS.

Try restarting the machine and see if that helps.

Also, try to rename the file in Macro code and see if gets saved.

One last thing, make sure that you have "Userprofile" environment variable properly defined, check it by going to the below path:

Settings > System > About > Advanced System Settings > Environment Variables

Write below code in Macro to see if you're getting the value correctly:

Msgbox(Environ("Userprofile")

Let me know in case of any further assistance is required, I will be glad to assist.

Thanks

Bhavik

Answer 2

Correction:

Msgbox(Environ("Userprofile"))

Answer 3

Thanks Bhavik for responding so soon on my issue in excel.

The points you mentioned, I have checked again for making sure if am I missing something or making any unknown mistake. Everything is good. Pdf file is closed, user profile is defined properly.

One thing I am not getting is how to check that the PDF file is not locked for writing by OS.

And as I mentioned before that same file is running flawlessly on other PC's. I have restart my PC and try to run the macro, same issue.

When I clean install win10 oct 2020 version it worked without an error. After few days and automatic updates of windows the error came back on my PC.

I can't clean install windows everytime the issue arises as it costs me the data, time and money loss. I guess it is something related with windows issue.

Thanks again.

Answer 4

From my first reply, have you tried this?

Also, try to rename the file in Macro code and see if gets saved.

If not then, please try to change the name in the Macro code and see if that creates another file or not.

Answer 5

Thanks Bhavik,

I tried by changing the name in macro code and tested in my PC and others PC.

It successfully generated pdf and opened in others pc, but same error in my pc.