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.