CODEWITHSUNDEEP
excelpasswords
woshishui
woshishui

Reputation: 2074

bulk unprotect excel workbook

I have over 100 excel workbooks in the same folder all protected with the same password. Looking for a way to bulk unprotect these workbook.

One potential solution I found is a vbs code as workaround to copy and save the workbook, but I'm not sure how to apply this code for all files in the folder.

Set objExcel = CreateObject("Excel.Application")
'
objExcel.Visible = TRUE
objExcel.DisplayAlerts = FALSE
'
Path1="C:\Users\xxxx\Test\Amazing Pty Ltd PW.xls"
Path2="C:\Users\xxxx\TestCopy\Amazing Pty Ltd PW no.xls"
'
Set objWorkbook = objExcel.Workbooks.Open(Path1,,,," ")
'
objWorkbook.Unprotect("password")
objWorkbook.SaveAs Path2
'
objExcel.Quit

Thanks for your help!!

Upvotes: 1

Views: 2032

Answers (1)

Jonathan
Jonathan

Reputation: 663

Here is a tested script that applies your code to each file by looping through the files in the "Test" directory. 

'Loop through files in a directory using Filesystemobject
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Set as the directory containing your files
objStartFolder = "C:\Users\xxxx\Test\"

'Set as save to directory 
objSaveToFolder = "C:\Users\xxxx\TestCopy\"

Set objFolder = objFSO.GetFolder(objStartFolder)
Set colFiles = objFolder.Files

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = TRUE
objExcel.DisplayAlerts = FALSE

For Each objFile in colFiles
'Open the file with it full path name
Set objWorkbook = objExcel.Workbooks.Open(objFSO.GetAbsolutePathName(objFile),false,false)
objWorkbook.Unprotect("password")
objWorkbook.SaveAs objSaveToFolder & objFile.name
Next

objExcel.Quit

Upvotes: 2

Related Questions