Hi all,

A colleague at work has an xl file that is linked to about 10 other
password-protected files over a winNT network. He want those files opening
without having to laboriously type in passwords in order to update his
master copy. The code below does work (it's behind a third file called
CodeFile.xls, but when i attempt to error trap I receive the usual Debug |
End |Help dialog box. Please can someone help as the vba is useless without
knowing which, if any, files failed to open (i.e. cos they were moved,
changed, deleted by other users).

Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler

Workbooks.Open Filename:="blah\blah\Link.xls", updateLinks:=3, _
password:="gollum"
Workbooks.Open Filename:="blah\Master.xls", updateLinks:=3

Workbooks("Link.xls").Close

Exit Sub

ErrorHandler:
Select Case Err.Number

Case 1004
MsgBox "The file Could not be found", vbOKOnly + vbCritical, "Error"

Case Else
MsgBox (Err.Number & Err.Description)
End Select
Resume Next

End Sub

I couldn't get the Debug window to pop up while stepping thru the code
you posted. An error does occur when it hits the first Workbook.Open
line and it drops down to the errorhandler, executes the Case 1004
msgbox. At that point the Resume Next stmt sends the code to the 2nd
Workbook.Open line (Master.xls)

It may be due to how you're directing the code with the Resume Next
statement. It may also help to reset the error condition using On Error
Goto 0 such as:

Case Else
MsgBox (Err.Number & Err.Description)
End Select
On Error GoTo 0
Resume Next

Set a break point in your code by clicking on a line and then go to the
Debug menu and choose Toggle Breakpoint. Then go to your workbook and
click on Commandbutton1 to start the macro, which will then be stopped
at the break point. Then use the F8 key to execute one line of code at
a time to see exactly what the code is doing.


HTH


---
Message posted from http://www.ExcelForum.com/

Hi Paul,

Make sure you haven't selected "Break on all errors" via Tools | Options,
General tab in the VBE.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Paul Wagstaff wrote:
Hi all,

A colleague at work has an xl file that is linked to about 10 other
password-protected files over a winNT network. He want those files
opening without having to laboriously type in passwords in order to
update his master copy. The code below does work (it's behind a third
file called CodeFile.xls, but when i attempt to error trap I receive
the usual Debug | End |Help dialog box. Please can someone help as
the vba is useless without knowing which, if any, files failed to
open (i.e. cos they were moved, changed, deleted by other users).

Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler

Workbooks.Open Filename:="blah\blah\Link.xls", updateLinks:=3, _
password:="gollum"
Workbooks.Open Filename:="blah\Master.xls", updateLinks:=3

Workbooks("Link.xls").Close

Exit Sub

ErrorHandler:
Select Case Err.Number

Case 1004
MsgBox "The file Could not be found", vbOKOnly + vbCritical, "Error"

Case Else
MsgBox (Err.Number & Err.Description)
End Select
Resume Next

End Sub

Tools Options General Error Trapping Break on Unhandled Errors

did the trick.

zzz..! Thanks anyway!
Paul.

"btadams " wrote in message
...
I couldn't get the Debug window to pop up while stepping thru the code
you posted. An error does occur when it hits the first Workbook.Open
line and it drops down to the errorhandler, executes the Case 1004
msgbox. At that point the Resume Next stmt sends the code to the 2nd
Workbook.Open line (Master.xls)

It may be due to how you're directing the code with the Resume Next
statement. It may also help to reset the error condition using On Error
Goto 0 such as:

Case Else
MsgBox (Err.Number & Err.Description)
End Select
On Error GoTo 0
Resume Next

Set a break point in your code by clicking on a line and then go to the
Debug menu and choose Toggle Breakpoint. Then go to your workbook and
click on Commandbutton1 to start the macro, which will then be stopped
at the break point. Then use the F8 key to execute one line of code at
a time to see exactly what the code is doing.


HTH


---
Message posted from http://www.ExcelForum.com/