Remove Excel password
I have about 500 Excel files which are password protected with the same
password. I do know the password. Is there a utility or can a sciprt be written to remove all passwords on all files in the same folder ? I am trying to avoid opening them all and changing the setting manually. Thanks |
Remove Excel password
You could try this...
Option Explicit Sub testme01() Dim myNames() As String Dim fCtr As Long Dim myFile As String Dim myPath As String Dim TempWkbk As Workbook Dim DestCell As Range Dim myPWD As String myPWD = "hi" 'change the folder here myPath = "C:\my documents\excel\test" If myPath = "" Then Exit Sub If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If myFile = "" On Error Resume Next myFile = Dir(myPath & "*.xls") On Error GoTo 0 If myFile = "" Then MsgBox "no files found" Exit Sub End If Set DestCell = Workbooks.Add(1).Worksheets(1).Range("a1") DestCell.Resize(1, 2).Value _ = Array("Workbook Name", "Ok?") Application.ScreenUpdating = False 'get the list of files fCtr = 0 Do While myFile < "" fCtr = fCtr + 1 ReDim Preserve myNames(1 To fCtr) myNames(fCtr) = myFile myFile = Dir() Loop If fCtr 0 Then For fCtr = LBound(myNames) To UBound(myNames) If LCase(myNames(fCtr)) = LCase(ThisWorkbook.Name) Then 'do nothing, skip this file Else Application.StatusBar _ = "Processing: " & myNames(fCtr) & " at: " & Now Set DestCell = DestCell.Offset(1, 0) DestCell.Value = myNames(fCtr) Set TempWkbk = Nothing On Error Resume Next Set TempWkbk = Workbooks.Open(Filename:=myPath _ & myNames(fCtr), Password:=myPWD) On Error GoTo 0 If TempWkbk Is Nothing Then DestCell.Offset(0, 1).Value = "Could not open" Else With TempWkbk .Password = "" .Save .Close savechanges:=False End With DestCell.Offset(0, 1).Value = "Password removed" End If End If Next fCtr End If DestCell.Parent.UsedRange.Columns.AutoFit With Application .ScreenUpdating = True .StatusBar = False End With End Sub Dave Mc wrote: I have about 500 Excel files which are password protected with the same password. I do know the password. Is there a utility or can a sciprt be written to remove all passwords on all files in the same folder ? I am trying to avoid opening them all and changing the setting manually. Thanks -- Dave Peterson |
All times are GMT +1. The time now is 02:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com