Monday, October 3, 2011

Password Protect Multiple Excel Files

Our Laboratory Manager came to my room this morning and asked me how to password protect an excel sheet.  That is so his department can comply with one of the local government's requirement to ensure that the worksheets won't be easily accessible to others in case they gain access to his office' files.  And I showed him how. 

He said that can I please assist him creating those passwords for his excel report files and I said "sure" without realizing the enormity of that simple request. 

So I started opening his excel file, saving it again this time with password protection, opening another and doing the same thing again........ until I reached the 20th file on the current folder (there are some more folders) where I started to get impatient.  So I stopped what I am doing and decided to see how many files I needed to protect, so, right-click  properties.... darn! 2,765 excel files!!!!  Darn again!

Anyway, I said to myself, why am I wasting my time doing it manually when we can automate it?  Sometimes it escapes my mind that we are developers, LOL!

So I created a simple snippet to password protect multiple files.  To ensure that I won't be touching his originals, this snippet creates a sub-folder called  PassProtect and saves the password protected files there.  Once everything is okay, we can then delete the originals:



Local lcFolder, lnFileCount, lnCount 
lcFolder = Getdir()
If !Empty(m.lcFolder)
      * Change Folder
      Set Default To (m.lcFolder)

      * Create a new folder where a copy of the excel protected
      * files should be saved
      If !Directory("PassProtect",1)
            Md PASSPROTECT
      Endif

      * Create a cursor to hold filenames
      Create Cursor junk (xFile c(200))

      * Create an array for files and insert those records into cursor junk
      lnFileCount = Adir(laFiles, '*.xls*')
      For lnCount = 1 To m.lnFileCount
            Insert Into junk Values (Fullpath(laFiles(m.lnCount,1)))
      Endfor

      * Perform an SCAN....ENSCAN and start password protecting files
      Local loExcel As excel.Application
      loExcel = Createobject("excel.application")
      loExcel.DisplayAlerts =.F.
      loExcel.AlertBeforeOverwriting= .F.
      Scan
            lcFile = Alltrim(junk.xFile)
            Wait Window "Password Protecting "+m.lcFile+"....." Nowait
            loExcel.Workbooks.Open(m.lcFile)
            lcNewFile = Addbs(Justpath(m.lcFile))+"PASSPROTECT\"+Justfname(m.lcFile)
            Try
                  loExcel.ActiveWorkbook.SaveAs(m.lcNewFile,51,"MyPassword","",.F.,.F.)
            Catch
            Endtry
            loExcel.ActiveWorkbook.Close
      Endscan
      loExcel.Quit
      Messagebox("Excel sheets are now all password protected!",0+64,"Success!")
Else
      Messagebox("Password Protecting Aborted",0+16,"Oppppsss!")
Endif
                                                       

And that is it!  Now it is just a matter of running these snippets and leaving it running while I surf the net or do some of my other works.


17 comments:

  1. This is exactly what I need to do!
    What language is this? How can I use this code? (obviously not a developer here, though do have experience with coding...) Any help would be much appreciated!

    ReplyDelete
    Replies
    1. Visual Fox Pro. When I have time I will compile it into an exe so it can be used even by non-developers. ;-)

      Delete
  2. Gracias por compartir el còdigo. saludos
    Jzapata

    ReplyDelete
  3. If you are still unable to open excel password protected sheet then you should try best Excel file unlocker tool that is modern application, this software proficiently recover lost excel file password and break excel protection easily. For more detail about this tool just go with given this link http://www.removeexcelpassword.com/

    ReplyDelete
  4. Try best excel password remover software that has full ability to recover lost excel password and this software has no matter how complicated password. It can smoothly unlock excel protection and open it in well mode.
    Read More.........
    http://www.excelpasswordrecovery.net
    http://www.recoverlostexcelpassword.com

    ReplyDelete
  5. How to unlock the password?

    ReplyDelete
    Replies
    1. Briefly speaking...

      If it is Excel 97-2003, look for a document decryption service. It provides a guaranteed result and is not dependent on the password complexity (AccessBack for example).

      If it is Excel 2007-2013, look for password recovery programs supporting video cards. It will give you a huge increase in the password searching speed (AccentOPR for example).

      Delete
  6. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Yes,
      I used This software to remove Excel password & unlock my Excel file within just few minutes.

      "Perfect Data Solutions"

      Thanks You

      Delete
  7. You can use Excel password unlocker software which easily & smoothly recover Excel sheet password with one or two types. It is good performer software to crack Excel password & unlock Excel File brilliantly. This software gives to you recover excel file password with full protection your lost Excel file without any harm Excel file data.

    Read More-:http://www.excelunlocker.com/

    ReplyDelete
  8. Thanks for sharing this software information about how to protect multiple files in Microsoft excel .Simple snippet is used to password protect multiple files. To ensure that you are not touching the originals, this snippet creates a sub-folder called PassProtect and saves the password protected files there. Once everything is okay, you can then delete the originals.

    ReplyDelete
  9. Almost there... it is not working with "*.xlsb" and "*.xlsm", what can I do?

    ReplyDelete
  10. To recover Excel password on Microsoft Office, we can use a good-to-use Excel password recovery tool to do that. Here I suggest SmartKey Excel Password Recovery to you. It is a powerful Excel password unlocker to remove or recover password in Excel file.

    ReplyDelete
  11. This comment has been removed by a blog administrator.

    ReplyDelete
  12. Recover password from multiple MS Office files (Word, Excel and Access) by using eSoftTools most advanced and effective application, Excel password breaker tool. This tool supports all latest Excel versions and successfully unlock Workbook.

    Read More
    https://excelpasswordbreaker.wordpress.com/

    ReplyDelete
  13. esoftTools- Program of Excel file password removal is the safe way to remove and recover Excel file password with xls, xlsx, xltm, xla, xlm, xlam, xlsm and xlsb Read More: http://www.esofttools.com/excel-password-recovery.html

    ReplyDelete
  14. Great PDS Excel Password Recovery Software is 100% safely recover Excel file password and unlock Excel file password in easily manner. This program to perfectly scan MS Excel file password and re-access open XLS or XLSX excel spreadsheet password.


    http://www.excelpasswordrecovery.net/

    ReplyDelete