Macro stops when one a file

[Steve]

I'm using MS Office 2003 and Windows XP PRO

Have a scenario that is quite puzzling, in that when I'm opening a
particular file (.xls) and then macro just stops. However, the if I set a
break on the open statement and then do nothing other than hit F5 to continue
processing the macro completes as intended and generates the desired results.
If I set a break on the following IF Err.Number statement processing never
reaches it. I use the exact same process in opening several other files with
no problems??? Any ideas, thanks in advance for your efforts. By the way,
I don't have access to the PC that the macro is on which is why I typed the
short example of the problem. I believe in debugging the problem I set On
Error Resume Next, but I can't be 100% sure.

Sub Main()

Call openallfiles

End Sub


Sub openallfiles

Dim runpthname As String
Dim invwkbname As String

runpthname = "c:\computername\myname\subdirectory\anotherdirectory\"
invwkbname = "Inventory.xls"

' If I set a break here and hit F5 the macro completes
workbooks.open filename:= runpthname & invwkbname

' If I set a break here it never reaches it?????
If err.number <> 0 Then
msgbox ("Error occurred in opening file: " & invwkbname)
End If

End Sub

 

[JLGWhiz]

It could be a timing problem where the code executes through the End Sub
line before the Workbooks.Open can complete execution, thereby causing the
sub to close without opening the file because the variables lose their
value. Try it with the delay built in.


Sub Main()

Call openallfiles
End Sub


Sub openallfiles
Dim runpthname As String
Dim invwkbname As String
runpthname = "c:\computername\myname\subdirectory\anotherdirectory\"
invwkbname = "Inventory.xls"
' If I set a break here and hit F5 the macro completes
workbooks.open filename:= runpthname & invwkbname

s = Timer + 0.5
Do While Timer < s
DoEvents
Loop

' If I set a break here it never reaches it?????
If err.number <> 0 Then
msgbox ("Error occurred in opening file: " & invwkbname)
End If
End Sub

 

[Steve]

JLGWhiz,

I put a delay of two seconds prior to opening the file and that seems to
have done the trick. Thank you for your suggestion. I don't truly
understand why this works. I've read your explanation, but don't understand
how a sub could close before all of its statements have executed. The file
that was having a problem was the first of four files that are opened. I
added this file in the last set of modifications, but prior to that the other
three files opened without problems. Thanks again truly appreciate your
efforts.

Steve