Macro to unlock and lock file

Bellatrix461

New Member
Joined
Sep 7, 2020
Messages
8
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: 31

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sorry, you try add:

Dim Rep_password as variant

then
Set Rep_password = "password"
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top