Macro stopped working - error code

Question

This macro has worked for years but today it all of a sudden threw up the following error.

I am not very clever with VBA code but reading some of the websites it talks about declaring the sheet separately!! Can anyone help me fix the code below so that I can get it to work please.

Sub SavePDFRemittance()

'This macro opens the SaveAs option with the default file path "you have to set this file path below" converts the whole sheet into .pdf file format

'And opens the .pdf to view <-- you can disable the view after convert option with lower code: OpenAfterPublish:=False

pdfName = ActiveSheet.Range("G3") 

ChDir "F:\Confidential\0.  Cash\Remittances\" 'This is where you set a default file path. 

fileSaveName = Application.GetSaveAsFilename(pdfName, \_ 

fileFilter:="PDF Files (\*.pdf), \*.pdf") 

If fileSaveName <> False Then 

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= \_ 

    fileSaveName \_ 

    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas \_ 

    :=False, OpenAfterPublish:=False 

End If 

MsgBox "File Saved to" & " " & fileSaveName 

End Sub

I use this macro in many many spreadsheets so I wouldn't know what to do if I can't get it to work!!!!

Thanks in advance

Michelle

Answer 1

I am now back in the office and have noticed that all my files that use the code above all have an error so I don't think it is file specific sadly.

Hi Michelle,

The usual answer if the error is anywhere in your system / updates or addins:

Check your system for errors.

System file check (SFC) Scan and Repair System Files & - Microsoft Community

Repair your Office
Repair an Office application - Office Support

Check if some of your AddIns causes the issue, follow these steps:

Close Excel
Press and hold the CTRL key
Open Excel
Wait for a message to appear and ask for "Safe Mode"
Release the CTRL key
Click Yes
After Excel opens
Click File\Open and select your file
Press and hold the SHIFT key
Click Open
Wait for your file to open
Release the SHIFT key
Test the behavior

It's a guessing game... ask your administrator in your company.

Andreas.

Answer 2

Hi Andreas

I am the administrator so the buck stops with me lol.

I did all the above and it doesn't seem to have fixed, the error is shown below, are you able to identify from that? Otherwise I will have to rethink my complete set of files:

Answer 3

The code has worked for years and if you are the administrator, I assume that you have checked whether manual saving is still possible and that the file name is also possible and the directory is not write-protected, etc.

So it's clear that it can't be the code.

Try a clean boot, open Excel in safe mode, save the sheet manually and record a macro during the save. If that works, delete the pdf and execute the macro. Be sure your virus scanner / other security tools are disabled, it might be possible that they prevent proper functioning.

That's all I can say, from my view, it works on millions of machines, if it doesn't work on yours, it's your machine.

Andreas.

Answer 4

Hi Andreas

I have opened the file in safe mode and the macro works.

I have repaired 365, is there anything else I can do?

It seems other macros are working in other files but if I copy the code to the Cash Log it still doesn't work.

If you can't help no problem - I will have to go back to the start and figure it out.

Thanks for all your help

Michelle

Answer 5

I have opened the file in safe mode and the macro works.

Michelle,

it is one of your (COM-) Add-Ins. Disable all and try the file, IMHO it works. Enable the Add-Ins one by one and you will quickly find the culprit.

Andreas.