• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

Unprotect excel files in a folder

I have a list of excel files(2010) in a folder that are protected. Instead of going to each file and unprotect it., Im looking for code to read the folder and unprotect each file. excel vba or vb.net is fine
0
zachvaldez
Asked:
zachvaldez
  • 5
  • 3
  • 3
1 Solution
 
jppintoCommented:
Please try the attached code.

jppinto
Sub UnlockThemAll()
    Dim pw As String
    Dim ws As Worksheet
    pw = InputBox("Password please!")
    On Error GoTo ErrHandler
    For Each ws In ThisWorkbook.Worksheets
        ws.Unprotect pw
    Next
    MsgBox "All unlocked", vbOKOnly, "Yeah!"
    Exit Sub
ErrHandler:
    MsgBox "There was a password problem", vbCritical, "Unable to unprotect!"
End Sub


Sub subOpenFiles()
 
Dim strSearch As String
Dim strFile As String
Dim wb As Workbook
 
strSearch = "c:\My Documents\"  'Search string
 
strFile = Dir(strSearch & "*.xls")
 
Do While strFile <> ""    ' Start the loop.
   Set wb = Workbooks.Open(strSearch & strFile)     
    
'Run your Sub here
    UnlockThemAll()
    
    wb.Close True
    Set wb = Nothing
    strFile = Dir    ' Get next entry.
Loop
  
End Sub

Open in new window

0
 
zachvaldezAuthor Commented:
if you crate this in vb, a reference to teh Microsoft Excel Library may be needed and referred in the code?
0
 
jppintoCommented:
This code is to be placed on an Excel macro, on Visual Basic Editor from Excel. Isn't this what you wanted?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
zachvaldezAuthor Commented:
i use vb.net. So I modified it a bit but  am missing a reference on the workbook here.

Dim pw As String
        Dim xlsWB As Excel._Workbook
        Dim ws As Excel.Worksheet
        pw = InputBox("Password please!")
        On Error GoTo ErrHandler
        For Each ws In xlsWB.Worksheets>>> this need to be fix??
            ws.Unprotect(pw)
        Next
        MsgBox("All unlocked", vbOKOnly, "Yeah!")
        Exit Sub
ErrHandler:
        MsgBox("There was a password problem", vbCritical, "Unable to unprotect!")
    End Sub
0
 
jppintoCommented:
Can't help you with VB .Net.... sorry, it's not my area. Making it from Excel, would work like I showed you.
0
 
CodeCruiserCommented:
Try the below code. You would need to add reference to Excel Interop Assembly and add the

Imports Microsoft.Office.Interop.Excel

at the top


Sub UnlockThemAll(wb As WorkBook)
Try
    Dim pw As String
    Dim ws As Worksheet
    pw = InputBox("Password please!")
    For Each ws In wb.Worksheets
        ws.Unprotect pw
    Next
    MsgBox "All unlocked", vbOKOnly, "Yeah!"
Catch Ex As Exception
    MsgBox "There was a password problem", vbCritical, "Unable to unprotect!"
End Try
End Sub


Sub subOpenFiles()
 
Dim strSearch As String
Dim wb As Workbook
Dim app As New Excel.Application
 
strSearch = "c:\My Documents\"  'Search string
 
Dim strFiles As String() = IO.Directory.GetFiles(strSearch, "*.xls")
 
For Each File In strFiles
   wb = app.Workbooks.Open(File)     
   
   'Run your Sub here
   UnlockThemAll(wb)
    wb.Close True
    Set wb = Nothing
Next 
  
End Sub

Open in new window

0
 
zachvaldezAuthor Commented:


i tried the vb.net solution.
It's not looping to the list of excel files.
It only sees the first file in the folder. and ends with the message
Actually, it should only unprotect the workbook (sheet1-first sheet) not all sheets
 .even though there are 3 sheets in a file,

thanks
0
 
zachvaldezAuthor Commented:
a couple of things.
It should also pick ".xlsx" files
and save it after removing the protection...
0
 
CodeCruiserCommented:
You would have to reference and use different versions of Office Primary Interop Assemblies to be able to handle both xls and xlsx files.

>It only sees the first file in the folder. and ends with the message
Does it unprotect that file?
0
 
zachvaldezAuthor Commented:
Codecruser-It's working
at the same time Id like to unhide column 'A'
0
 
CodeCruiserCommented:
Try

ws.Range("A1").EntireColumn.Hidden = False
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now