Macro to unlock and lock file

Bellatrix461

New Member
Joined
Sep 7, 2020
Messages
4
Platform
  1. Windows
Hello,
I'm trying to create a macro that will unlock and lock a source file saved in a different folder from the file I'm running the macro. It keeps giving me an error and I'm not sure what's referring to. The error says: "<Method 'Range of object'_Global' failed>". Please see screenshot attached.

Here's the macro I created to unlock the file (same error for the lock macro):

Sub Password_Unlock()

Dim WD_Report As Workbook

Dim Password As String
Rep_Password = Range("Password")
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Removes password from files

MsgBox "Macro is now running, please wait"

' Application.CommandBars("Queries and Connections").Visible _
= Not (Application.CommandBars("Queries and Connections").Visible)

Set WD_Report = Workbooks.Open(Filename:=(Environ("USERPROFILE") & "\COMPANY\FOLDER NAME\FOLDER NAME\Inputs\AutomationFile.xlsx"), Password:=Rep_Password, WriteResPassword:="")
WD_Report.SaveAs Filename:=(Environ("USERPROFILE") & "\COMPANY\FOLDER NAME\FOLDER NAME\Inputs\AutomationFile.xlsx"), Password:="", WriteResPassword:=""
' Application.DisplayAlerts = False
WD_Report.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox "Finished removing password from Automation File"

End Sub


Thank you for all your help!
 

Attachments

  • MacroScreenshot.JPG
    MacroScreenshot.JPG
    72.2 KB · Views: 9

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

excel_newbie86

New Member
Joined
Aug 1, 2020
Messages
17
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
Sorry, you try add:

Dim Rep_password as variant

then
Set Rep_password = "password"
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
With your post #1 code in the line
Rep_Password = Range("Password")
you're pulling a value out of a worksheet range called "password". If such a named range doesn't exist a run-time error message pops up.
If your intension is to assign the text string "password" to the variable called Rep_password use the syntax:
VBA Code:
Dim Rep_Password as String
Rep_Password = "Password"
 

Watch MrExcel Video

Forum statistics

Threads
1,112,995
Messages
5,543,183
Members
410,584
Latest member
Bluefox68
Top