How to save an Excel fie with a new password in vba

Question

I'm trying to save a file with a new password, but when I reopen the file still asks me for the old password. The code I have is as follows:

    FileCopy strLastMonthFilePath, strFilePath
    
    ' Open MF
    Workbooks.Open Filename:=strFilePath, Password:=OldPassword, UpdateLinks:=0
    
    MF_FileName = ActiveWorkbook.Name 
    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Password:=NewPassword
    Application.DisplayAlerts = True
    
    ' Close Source files
    Application.DisplayAlerts = False
    Windows(MF_FileName).Activate
    Workbooks(MF_FileName).Close

    ' This line does not work. If if change it to the OldPassword then it works
    Workbooks.Open Filename:=strFilePath, Password:=NewPassword, UpdateLinks:=0

I tried also to assign the workbook and change the password to a variable but didn't work:

Set myWBk = Workbooks.Open(Filename:=strFilePath, Password:=OldPassword , UpdateLinks:=0)
myWBk.SaveAs Password:=NewPassword

Any idea why the password does not change?

You are setting the new password to ThisWorkbook - that's the workbook where the code is stored, not the workbook that you just opened. — FunThomas
– FunThomas, Commented Aug 17, 2021 at 14:41
Uhm, I thought that when it is opened, ThisWorkbook refers to the one opened. I tried other options, assigning the workbook to a variable, but didn't work either. I'll amend the detail — Selrac
– Selrac, Commented Aug 17, 2021 at 15:57

FunThomas · Accepted Answer · 2021-08-17 16:36:48Z

3

(1) ThisWorkbook is always the workbook where the code is stored. Opening a workbook makes it the ActiveWorkbook. The ActiveWorkbook is the workbook that has currently the focus. However, it is much better to store the workbook reference into a variable

(2) SaveAs creates a copy of the workbook. You need to use Save (or Close with parameter True). However, Save doesn't have a parameter to set or change the password, but this can easily be done using the Password property of the workbook.

Try this:

Dim myWBk  As Workbook
Set myWBk = Workbooks.Open(filename:=strFilePath, Password:=OldPassword, UpdateLinks:=0)
myWBk.Password = NewPassword
myWBk.Close True

edited Aug 17, 2021 at 16:36

answered Aug 17, 2021 at 16:19

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to save an Excel fie with a new password in vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related