Results 1 to 11 of 11

Password array to open all password protected workbook in a folder

Threaded View

  1. #1
    Registered User
    Join Date
    11-13-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003, 2010
    Posts
    73

    Password array to open all password protected workbook in a folder

    Hi guys, I've currently 10 excel password protected workbook in a shared drive with a master workbook with datas from each of the 10 workbook. Is it possible for me to create a macro in the master workbook whereby when the macro is invoked, all of the worksheets in all the 10 workbook will do what my code says? Currently my code is like what is shown below but it will prompt me password for all 10 workbook. Is it possible to have an array of password or something? such that I will not need to key in password after password for each of the10 workbooks individually. Thanks in advance.

    Sub RunCodeOnAllXLSFiles()
    Dim lCount As Long
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook
    
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    
    On Error Resume Next
        Set wbCodeBook = ThisWorkbook
            With Application.FileSearch
                .NewSearch
                'Change path to suit
                .LookIn = "G:\Test\Test"
                .FileType = msoFileTypeExcelWorkbooks
                '.Filename = "Book*.xls"
                    If .Execute > 0 Then 'Workbooks in folder
                        For lCount = 1 To .FoundFiles.Count 'Loop through all
                            'Open Workbook x and Set a Workbook variable to it
                            Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                            
                            'MY CODE
    
                            wbResults.Close SaveChanges:= True
                        Next lCount
                    End If
            End With
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    End Sub
    Last edited by rename; 12-07-2012 at 05:09 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1