Password protected files

southernsun · August 21st, 2007, 06:02 AM

I have created approx 500 workbooks containing sensitive data and passworded them with individual passwords (contained in a password table). I have sent these workbooks out to users who will complete and return these workbooks to me. I intend to process these workbooks upon their return using an Excel VBA macro.
Assuming the user does not change the filename i am able to open the returned file by looking up the filename in my password table. All good so far.
My problem occurs when a user changes the filename meaning that my macro can not find a matching password and so can not open or process the file.

Where can i store a unique file reference in a passworded file that i can read without knowing the password and which will then enable me to lookup the password from my table? And what would the code to do this look like?

BrianWren · August 21st, 2007, 02:17 PM

I changed the title of a workbook under Properties (which I could only do while it was unprotected). In VBA I was able to use:

Code:

    Dim w As Workbook
    Set w = _
            Application. _
            Workbooks. _
            Open("C:\Documents and Settings\" & _
                 "<Me >\Desktop\" & _
                 "ProtectedBook.xls")

I was then able to read

Code:

    w.BuiltinDocumentProperties("Title").value

to read the title. So you could open the files found in a certain location, then use the documents properties to uniquely identify the specific file that you are working with, and use that property to find the right password in your list.

southernsun · August 22nd, 2007, 04:40 AM

Thanks Brian but your solution requires the file to be opened before reading the Title property.
I can not do this as i won't know the password before opening it. What i need is some way of reading the Title property (or any other file property where i can store a password "hint") without opening the passworded file first.

I appreciate that this may not be possible but remain hopeful.
Thanks for trying though.

southernsun · August 22nd, 2007, 04:43 AM

Just had a thought - perhaps i was not clear in that the workbooks are not just "protected" but the files themselves are "passworded" against being opened without a password. This makes it an altogether tougher problem.

Would really appreciate an help anyone can give....

BrianWren · August 22nd, 2007, 04:01 PM

Quote:

quote:Thanks Brian but your solution requires the file to be opened before reading the Title property.
I can not do this as i won't know the password before opening it.

I tried this before I posted the message. I was able to get that property information without using any password info. I think the file properties are outside the password fence.

(Perhaps you are using a different password mechanism provided by Excel than the one I used though...)

BrianWren · August 22nd, 2007, 04:04 PM

OK, I answered the one post, then read what was after it...

Tell me how you established the password protection, and let me see what I can do with a horse of that stripe.

BrianWren · August 22nd, 2007, 05:11 PM

OK, I believe I have your answer

I created a .xls file, and saved it. While saving, I used the Save dlg's ability to password protect, and protected to open (not merely to modify).

Then I created a VBA project.
I added a reference to "Microsoft Shell Controls and Automation"
(C:\Winnt\System32\shell32.dll)

Then I added the following code, and ran it from the Debug window

Code:

Public Sub ab()

    Dim i       As Integer
    Dim sh      As New Shell   ' This class comes rom Shell32
    Dim FldrItm As FolderItem  ' This class comes rom Shell32
    Dim fldr    As Folder      ' This class comes rom Shell32

    Set fldr = sh.NameSpace("C:\ . . . \My Documents\")
    Set FldrItm = fldr.ParseName("Book1.xls")

    For i = 0 To 30
        Debug.Print i, fldr.GetDetailsOf(FldrItm, i)
    Next i

End Sub

The result I got was as follows (bold items are ones that I added in the File|Properties... in Excel before saving)

Code:

  0           Book1.xls
  1           14 KB
  2           Microsoft Excel Worksheet
  3           8/22/2007 14:17
  4           A
  5           These are the Comments
  6           8/22/2007 14:15
  7           8/22/2007 14:17
  8           NADSUSWE\brian.wren
  9           
 10           brian.wren
 11           This is the Title
 12           This is the Subject
 13           This is the Category
 14           
 15           
 16           
 17           
 18           
 19           
 20           
 21           
 22           
 23           
 24           
 25           
 26           
 27           
 28           
 29           
 30

Obviously, you can choose where you would like to stash your identifying data, but you can read these properties without having the password for the file...

southernsun · August 23rd, 2007, 04:53 AM

Thanks very much for this Brian. It works perfectly - sheer genius.