check for password prior to opening excel file 4

[jenmcclary]

I have a directory with numerous excel files some of these files are password protected. I will be moving all of these files into 2 different directories. One directory will hold the password protected files, the other directory will hold the files with no protection.

Dim FileName As String
Dim mainWB As Workbook

Set mainWB = ActiveWorkbook
FileName = "C:\AA\Bill\HasPassword\The Rockefeller Foundation.xls"
Workbooks.Open FileName:=FileName
If ActiveWorkbook.HasPassword = True Then
MsgBox "Has Password"
Else
MsgBox "No Password"
End If
ActiveWindow.Close

This doesn't work because the excel workbook hangs waiting for you to enter a password. I don't want to enter a password I just want to see if the file has password protection and then move it. I have the moving part under control I just need help with checking for password protection.

 

[VBAjedi]

Jen,

Try using the following code (set TestWorkbook to whatever workbook you want to test instead of ActiveWorkbook):

Dim TestWorkbook As Workbook
Set TestWorkbook = ActiveWorkbook
If Workbooks(TestWorkbook.Name).HasPassword Then
. . . your file move routine here

End If

Hope that helps!

VBAjedi

 

[Zathras]

This is a bit of a kludge, but it works...

Option Explicit

Sub test()
Dim FileName As String
Dim Wkb As Workbook
Dim bProtected As Boolean

  FileName = "c:\P2.xls"
  bProtected = False
  SendKeys "{ESC}"
  On Error Resume Next
  Set Wkb = Workbooks.Open(FileName)
  If Wkb.Name = "" Then

    ' The escape key was processed by the password dialog.

    MsgBox "Protected"
  Else
    MsgBox "(Escape Key is processed by me - don't delete)"
    MsgBox "Not protected"
    Wkb.Close
  End If
  Set Wkb = Nothing
End Sub

 

[jenmcclary]

Zathras,

Thanks for the code. This works; however, the code still halts upon opening of the excel and waits for me to hit the cancel button. I am trying to skip this step otherwise someone will have to sit while this code runs and hit the cancel button all the time. There will be thousands of files run through this code. Any suggestion??

 

[Zathras]

I just tested it again and it works for me. (Excel 97) What precisely does the halt say? Did you use the code exactly as posted, or did you make any changes (other than the file name, of course)?

When I run with c:\P1.xls I get the "Protected" message box.
When I run with c:\P2.xls I get the "Not protected" message box.
(P1.xls is password protected, P2.xls is not.)

I can't help thinking there's something you're not telling me.

 

[VBAjedi]

Zathras,

I think that essentially what Jenmcclary wants to do is check each file WITHOUT opening it. Thats why I suggested referencing the .HasPassword property of each file in my initial post:

If Workbooks(TestWorkbook.Name).HasPassword Then

Is there a reason that isn't a good idea?

VBAjedi

 

[jenmcclary]

VBAjedi,

Exactly.

I am not sure how to set TestWorkbook to my file. Everything I've tried gives me an error. Here is an example.

Dim TestWorkbook As Workbook
Set TestWorkbook = Workbooks("C:\AA\Bill\HasPassword\The Rockefeller Foundation.xls&quot
If Workbooks(TestWorkbook.Name).HasPassword Then
end if

The line in bold won't work. I get subscript out of range. Any suggestions?

 

[Zathras]

VBAJedit: I think it's a great idea. Can you post some working code to demonstrate how to do it? I can't figure any way to create a Workbook object without opening the file.
(Excel 97)

 

[VBAjedi]

Arrgh! My test code worked flawlessly (it seemed), but I just figured out the workbook I was testing was open at the time.

What if you do the following and test the resulting error value:

On Error Goto MyErrorHandler:
x = "C:\TrashLocked.xls"
y = Workbooks.Open(x, , , , "&quot

MyErrorHandler:
z = Err.Number
If z = 1004 Then HasPassword = True
If z = 438 Then HasPassword = False
Resume Next


If the error value is 1004, you supplied the wrong password ("&quot, but if it's 438, the workbook doesnt have a password!

Let me know if this works for you.
VBAjedi

 

[Zathras]

Well done VBAJedi! A star for you.
Here is a slightly cleaned-up version that should do the trick for Jen:

Function NeedsPassword(File As String) As Boolean

' Test whether a file (on disk) needs a password.

Dim wkb As Workbook
  NeedsPassword = True
  On Error Resume Next
  Set wkb = Workbooks.Open(FileName:=File, Password:="")
  If Not wkb Is Nothing Then
    NeedsPassword = False
    wkb.Close
    Set wkb = Nothing
  End If
End Function

Here are a couple of test routines to demo/test the above (plug in your own file names):

Sub Test1()

' This file needs a password

  If NeedsPassword("C:\P1.xls") Then
    MsgBox "Needs a password"
  Else
    MsgBox "No password required"
  End If
End Sub

Sub Test2()

' This file doesn't need a password

  If NeedsPassword("C:\P2.xls") Then
    MsgBox "Needs a password"
  Else
    MsgBox "No password required"
  End If
End Sub

 

[jenmcclary]

Thank you both for all your help. It works perfectly.