application.run cant find open file

[Jamie]

This is quite a strange one...

I have created a macro that runs on book1 and half way through
executes a procedure on book2, when that has finished it returns to
book1, does a loop and starts the process again to go back to book2
then to sheet1 etc till the proccess has finished.

Book1 is used to control the process and there are several different
versions of Book2 which have identical code but different data. when
designing the macro everything worked and it has been working fine for
over a week. all of a sudden one of the versions of book2 stoped
working but the others are fine.

The following code is on book1 controling the process. The first time
the code runs it works fine and executes "PasteData" on book2. If
book2 is not open allready the error handler will open book2 and
everything still works fine. the next time the process comes to this
bit it cannot find that book2 is open. the variable DestinationBook
had not changed. it executes the error handler which asks if i want to
re-open book2 so at that stage it must have found that it is open! if
i click yes to re-open it goes into a continuous loop of not finding
book2 and then trying to re-open it. if i click no i get a run-time
error 1004. "Method open of object workbooks failed."

If RouteFound = True Then
errBookNotOpenres:
On Error GoTo errBookNotOpen
Application.Run DestinationBook & "!PasteData", var1, var2, var3
On Error GoTo 0
End If

Exit sub

errBookNotOpen:
Workbooks.Open FileName:=DestinationBook
Err.Clear
Resume errBookNotOpenres

I have tried to re-create the file. firstly by copying and moving the
sheets in book2 to a fresh book using the tabs at the bottom of the
sheets. This did not work. secondly i tried copying and pasting just
the data into a copy of one of the other versions of book2 (with
identical code) this worked fine a few times on windowsNT but then was
tried on windowsXP (both using excel97)and the same problem occured. I
tried it back on the windowsNT which worked minutes before and it was
failing again!!?!?!?!?!?

This is way beyond my level of knowledge since i do not know why one
line of code should not be able to recognise that a book is open and
available and the very next line can find the book open and ask to
re-open it.

Any suggestions much appreciated.

Jamie

 

[Don Guillett]

Perhaps you can use this idea from a sub I use (called from double click
event) to open a workbook or activate if already open.

Sub GetWorkbook()
If ActiveCell.Value = "" Then Exit Sub
workbookname = ActiveCell.Value
On Error GoTo OpenWorkbook
Windows(workbookname & ".xls").Activate
Exit Sub
OpenWorkbook:
Workbooks.Open(workbookname & ".xls").RunAutoMacros xlAutoOpen
End Sub

BUT, why do you have to go back and forth? If you are copying between
workbooks get rid of your selects and just use something like this to copy
from yours.xls to the active workbook (assumes both open)
with workbooks("yours.xls")
..range("a1:a3").copy range("a1")
end with