CODEWITHSUNDEEP
excelvba
Tim_D
Tim_D

Reputation: 11

Error Message "Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed" when saving with VBA password protection on

I am trying to save a single sheet from a workbook as a .txt file- the code I have to do this is:

ActiveWorkbook.SaveAs Filename:= _
directory & Application.UserName & "_" & file_name _
, FileFormat:=xlUnicodeText, CreateBackup:=False

(directory and file-name are defined earlier in the macro)

The macro that contains this code is password protected. When the macro is run without entering the password, I receive the error message "Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed" . When I open up visual basic and enter the password, the macro runs fine with no error messages. Is there a different syntax I can use to save the .txt file, or does anyone know what could be causing this error?

I found an old help post on another site from 2006, with 0 answers:

https://www.excelforum.com/excel-general/567235-error-when-save-as-csv-with-vba-password-protection-on.html

Any help/advice would be greatly appreciated!

Upvotes: 1

Views: 1013

Answers (1)

Tim_D
Tim_D

Reputation: 11

Not sure if answering my own question is allowed or is the correct thing to do - but I managed to get the macro working and hopefully this will help someone else in the future:

Instead of:

ActiveWorkbook.SaveAs Filename:= _ directory & Application.UserName & "_" & file_name _ , FileFormat:=xlUnicodeText, CreateBackup:=False

Use:

ThisWorkbook.SaveAs Filename:=direct & file_name, FileFormat:=xlText

Not sure why this works and the previous code doesn't but it will save a .txt file even when the vba is password protected.

Upvotes: 0

Related Questions