Save file over existing file with password

I use the below code to save a file with a date and a password

ActiveWorkbook.SaveAs Filename:="\\ukancbdata02\S-gdata\AllFiles\Recon\London\Events\Consolidated Report\Consolidated " & Format(DateAdd("d", -0, Date), "YYYY") & ".xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="pact", _
        ReadOnlyRecommended:=True, CreateBackup:=False


What I need to do do is overwrite the file each day but because it is password protected the code returns an error.

Could an Expert provide me with the code that will enable me to save the file each day over the existing file with the password.
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

which PW is missing

ActiveWorkbook.SaveAs Filename:="\\ukancbdata02\S-gdata\AllFiles\Recon\London\Events\Consolidated Report\Consolidated " & Format(DateAdd("d", -0, Date), "YYYY") & ".xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="pact", _
        ReadOnlyRecommended:=True, CreateBackup:=False

Regards
0
JagwarmanAuthor Commented:
Hi Rgonzo, soory not sure I understand the question.

when the original file was saved, it was saved with a password. If I open the file manually I am asked for a password and then I get asked "Open as Read Only?"

So now I want to save over this file.

 Does this answer your question?
0
Rob HensonFinance AnalystCommented:
I just created a simple file and saved with a password. I then closed the file and tried saving another file with the same name and it gave me the usual "File exists, Replace" warning to which I clicked Yes and the file overwrote with no problem.

Have you closed the original file before trying to Overwrite?

If you are doing changes in the open file which you want to save, just use "ActiveWorkbook.Save" command. The password will be retained for future use.

Thanks
Rob H
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

JagwarmanAuthor Commented:
Hi Rob,

If I open the original file and the save it down its fine.

If I create a new file and call it the same name it does not work.

So maybe it's not possible.
0
Rob HensonFinance AnalystCommented:
That's exactly what I did. Created file and saved with password. Closed file, created new file and saved with same name, overwrote fine after confirming message.
0
JagwarmanAuthor Commented:
Hi Rob,

when I save outside of a Macro it does exactly what you say. When I try to get the Macro to save it I get error saying the file is Write protected.
0
Rob HensonFinance AnalystCommented:
Bizarre, just tried it myself and got the same but got it to work using the following:

ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\lu677d\Documents\Personal\EE\With Password.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, Password:="", WriteResPassword:="pact", _
        ReadOnlyRecommended:=True, CreateBackup:=False

Open in new window


Note the format change. You just need to change the filename bit back to how you want it.

Thanks
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
Hi Rob thanks for working on this and sorting it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.