CODEWITHSUNDEEP
vba
Selrac
Selrac

Reputation: 2293

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

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?

Upvotes: 2

Views: 935

Answers (1)

FunThomas
FunThomas

Reputation: 29276

(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

Upvotes: 3

Related Questions