Skip past Password Protected Excel file

[koops2121 via OfficeKB.com]

I have a routine that is going through Excel files and making a change to
every file in a specified folder.
This works great until I hit a file that's password protected. Since I don't
know the password I want to log then skip it.
Is there a way to open a workbook, not know the password, realize it has a
password and close it down and move on to the next file in the folder?

Thanks,

 

[JW]

I'm assuming that you are setting a variable to hold the workbook
currently being processed. If so, use something like this:
If wb.HasPassowrd Then
'whatever you want to happen
End If

Would need to see your code to recommend the proper spot to place
this.

 

[koops2121 via OfficeKB.com]

Sorta yes.

I do this:

Set xApp = CreateObject("Excel.Application")
Set xBook = xApp.Workbooks.Open(objFile.Path)

If I then do xBook.HasPassword = True, it's too late and I'm already stuck at
the prompt asking for a password from the Open command. Maybe I'm doing this
wrong.

Thanks,

 

[Gary Keramidas]

does the workbook require a password to open it, or just to modify a worksheet?

 

[koops2121 via OfficeKB.com]

It requires a password to open it.
So everything hangs until you enter a password.

 

[koops2121 via OfficeKB.com]

It requires a password to open it.
So everything hangs until you enter a password.

 

[Dave Peterson]

Dim TestWkbk as workbook
inside your looping code...
set testwkbk = nothing
on error resume next
set testwkbk = workbooks.open(filename:=theloopvaluehere,password:="")
on error goto 0
if testwkbk is nothing then
'it wasn't opened, skip it
else
'opened ok
'do the real work here.
end if
end of loop

 

[Leo.Divella]

I was actually having the same problem except I was using the
Office.Interop library from a C# class and
every time I tried to open a workbook with a password an actual Excel
prompt would appear, which was no good.
The key insight for me was that entering "" as a password parameter
would cause no problems opening non password
protected files.

In my case I had to do something like this:

try{
myExcelApp.WorkBooks.Open(filename,...,"",...)
}
catch (System.runtime.InteropServices.COMException e)
{
// workbook is password-protected, gracefully exit
}

// continue with non-password-protected workbook

Thanks Dave for your elucidating post.