VBA code halts after opening a file

[Guest]

Hi all,

I have a number of files to process and I don't want to add the same duplicative VBA code to each file. Putting the code into single workbook with a loop to open and process the files sequentially seemed logical.

However, my code halts after opening the first file. Any idea what might be wrong?

I reduced the code to bare bones (without the loop) and it still quits after opening the first file. The start of the processing code is simple:

Workbooks.Open Filename:="C:\Tests\OpenTest.xls"
Range("B2").End(xlDown).Offset(1,0).Select

The computer a new Compaq running Excel XP SP2 with all available Office updates applied and running under Windows XP Home Edition SP1 with all Critical updates and all but a few of the Recommended updates installed.

Thanking you in advance, I look forward to enlightenment

Bill

 

[Harald Staff]

Hi Bill

I can only think of two things -assuming that the file actually exists and that you'll
write code to ensure it does in the finished product.

First, you don't specify which sheet in the file to go to. And second, opening files may
take a while, insert a DoEvents to give her a little space. Let me also suggest you insert
a On Error GoTo 0
sldo, it clears error handling and so the debugger should provide the corrrect error
message on errors:

Sub test()
On Error GoTo 0
Workbooks.Open Filename:="C:\Tests\OpenTest.xls"
DoEvents
Workbooks("OpenTest.xls").Sheets(1).Activate
DoEvents
Range("B2").End(xlDown).Offset(1, 0).Select
End Sub

--
HTH. Best wishes Harald
Followup to newsgroup only please.

Hi all,

I have a number of files to process and I don't want to add the same duplicative VBA

code to each file. Putting the code into single workbook with a loop to open and process
the files sequentially seemed logical.

However, my code halts after opening the first file. Any idea what might be wrong?

I reduced the code to bare bones (without the loop) and it still quits after opening the

first file. The start of the processing code is simple:

Workbooks.Open Filename:="C:\Tests\OpenTest.xls"
Range("B2").End(xlDown).Offset(1,0).Select

The computer a new Compaq running Excel XP SP2 with all available Office updates applied

and running under Windows XP Home Edition SP1 with all Critical updates and all but a few
of the Recommended updates installed.

 

[Nigel]

Bill,
It seems the selection or activation of the workbook and worksheet might be
required?

When you say it quits,do you mean the procedure or Excel?

Cheers
Nigel

Hi all,

I have a number of files to process and I don't want to add the same

duplicative VBA code to each file. Putting the code into single workbook
with a loop to open and process the files sequentially seemed logical.

However, my code halts after opening the first file. Any idea what might be wrong?

I reduced the code to bare bones (without the loop) and it still quits

after opening the first file. The start of the processing code is simple:

Workbooks.Open Filename:="C:\Tests\OpenTest.xls"
Range("B2").End(xlDown).Offset(1,0).Select

The computer a new Compaq running Excel XP SP2 with all available Office

updates applied and running under Windows XP Home Edition SP1 with all
Critical updates and all but a few of the Recommended updates installed.

 

[Tom Ogilvy]

Does the workbook you are opening contain a Workbook_Open event? How do you
run your macro? Do you use a shortcut key?

--
Regards,
Tom Ogilvy

Hi all,

I have a number of files to process and I don't want to add the same

duplicative VBA code to each file. Putting the code into single workbook
with a loop to open and process the files sequentially seemed logical.

However, my code halts after opening the first file. Any idea what might be wrong?

I reduced the code to bare bones (without the loop) and it still quits

after opening the first file. The start of the processing code is simple:

Workbooks.Open Filename:="C:\Tests\OpenTest.xls"
Range("B2").End(xlDown).Offset(1,0).Select

The computer a new Compaq running Excel XP SP2 with all available Office

updates applied and running under Windows XP Home Edition SP1 with all
Critical updates and all but a few of the Recommended updates installed.

 

[Dave Peterson]

And if you do use a shortcut key, make sure you don't include the shift key.

Holding the shift key while opening a file stops the open code from running.
And it seems to confuse excel into stopping any currently running code.

 

[Guest]

Hi Nigel,

When I say halts, Excel is active and the worksheet has loaded. However, VBA is no longer runing.

I solved my problem by rewriting the code completely and it runs per all the Microsoft documentation. However, I want o understand theproblem so I know shat to do when it occurs again. When I figure this out, where should I send my diagnosis?

Thanks,Bill

----- Nigel wrote: -----

Bill,
It seems the selection or activation of the workbook and worksheet might be
required?

When you say it quits,do you mean the procedure or Excel?

Cheers
Nigel

Hi all,

duplicative VBA code to each file. Putting the code into single workbook
with a loop to open and process the files sequentially seemed logical.updates applied and running under Windows XP Home Edition SP1 with all
Critical updates and all but a few of the Recommended updates installed.

 

[Guest]

Thanks for your thoughts. The test file has only one sheet, so that didn't cause the problem.

Tom and Dave seem to have figured this one out -- holding the shift key down during loading a file halts execution. So Macros of the form <Ctrl><Shift> are dangerous!

I'd like to see this noted somewhere in Microsoft's documentation on writing macros.

 

[Guest]

Thanks Tom

Yes I use a shortcut key to start the macro and, yes, it does use <Ctrl><Shift>. Hence Dave Peterson figured this out for me and you were on the right track

I guess I've learned to use controls instead of shortcuts to start macros since most of my macros involve file manipulations