Excel hangs on reopening a file

[Stephen Poley]

I am developing a VPA procedure that needs to use two temporary copies
of the workbook supplied by the user. I create these with the following
code:

On Error GoTo KanGeenBestandOpslaan
ActiveWorkbook.SaveAs Filename:=Distmap & OutputBestand
ActiveWorkbook.SaveAs Filename:=Distmap & TempBestand
On Error GoTo 0

On Error GoTo KanBestandNietHeropenen
Workbooks.Open Filename:=Distmap & OutputBestand
On Error GoTo 0

If the two temporary files exist on the disk, the user is asked to
confirm that they can be overwritten; the procedure then runs correctly.

However, if the temporary files do NOT exist on the disk, either because
the tidy-up code at the end of the procedure has removed them on the
previous run (this is of course intended to be the normal situation) or
because I deleted them manually, then Excel hangs on the Open statement.
The file is in fact opened, but the next line of VBA is never executed
(I've tried both inserting a MsgBox, and placing a breakpoint), the
error-handling code is never reached, and no error message is generated.

Weirder still: if I place a breakpoint on the Open statement itself, the
breakpoint is reached; after Continue (F5) the procedure then runs
correctly!

The data workbook I am testing with itself contained a macro, and I
thought this might be related, but after removing the module (i.e. all
modules) this hang remains.

A colleague said that Excel has an occasional bug where it "forgets" to
remove a breakpoint, but after cutting the code, restarting Excel and
pasting the code back in, the problem remains.

I have also tried rebooting.

Suggestions please?

Am using Excel 2003, Dutch language, Windows XP

 

[NickHK]

Stephen,
Sounds like using .SaveCopyAs and possibly Kill would be a better approach.
Then you are not affecting the current open WB.

NickHK

 

[Stephen Poley]

But in order to use the temporary files I need to open them, and it's
the opening that gives the problem. The only difference (unless I've
missed something somewhere) with .SaveCopyAs is that I need to open two
files instead of one. (The original workbook must not be modified.)

Any other ideas?

 

[Stephen Poley]

If the two temporary files exist on the disk, the user is asked to
confirm that they can be overwritten; the procedure then runs correctly.

However, if the temporary files do NOT exist on the disk, either because
the tidy-up code at the end of the procedure has removed them on the
previous run (this is of course intended to be the normal situation) or
because I deleted them manually, then Excel hangs on the Open statement.

To eliminate any possible extraneous effects, I have now created a
minimum test-case, on a new workbook with text in a single cell and the
following routine:

Sub testopen()
MsgBox "Running test"
ActiveWorkbook.SaveAs Filename:="test1.xls"
ActiveWorkbook.SaveAs Filename:="test2.xls"

Workbooks.Open Filename:="test1.xls"
MsgBox "Got there"
End Sub

This replicates the situation: "test1.xls" is opened but the routine
then hangs and does not reach the final MsgBox.

What is going on?

 

[NickHK]

Yes, you know the path/filename of where you saved the copy, so just open
it/them.

The original workbook must not be modified.

That's the whole point of .SaveCopyAs, them open it and work on it.

NickHK

 

[Stephen Poley]

On Thu, 31 May 2007 11:51:48 +0200, Stephen Poley

To eliminate any possible extraneous effects, I have now created a
minimum test-case, on a new workbook with text in a single cell and the
following routine:

Sub testopen()
MsgBox "Running test"
ActiveWorkbook.SaveAs Filename:="test1.xls"
ActiveWorkbook.SaveAs Filename:="test2.xls"

Workbooks.Open Filename:="test1.xls"
MsgBox "Got there"
End Sub

This replicates the situation: "test1.xls" is opened but the routine
then hangs and does not reach the final MsgBox.

Well, I found a work-around: I close the ActiveWorkbook after the saves,
and then reopen both saved files. It still isn't clear to me why this is
necessary, but at least it works, which is the main thing.

 

[Stephen Poley]

Well, that's sort of what I ended up doing (though not with SaveCopyAs)
- see my other post. It's weird that I can't open one file, but can open
two. Still, it works. Thanks.