VBA code halts after opening a file
 

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

VBA code halts after opening a file
 

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.

"Bill in Inverness" wrote in message
...
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

VBA code halts after opening a file
 

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

"Bill in Inverness" wrote in message
...
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





----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

VBA code halts after opening a file
 

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

Bill in Inverness wrote in message
...
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

VBA code halts after opening a file
 

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.


Bill in Inverness wrote:

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

--

Dave Peterson

VBA code halts after opening a file
 

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

"Bill in Inverness" wrote in message
...
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





----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

VBA code halts after opening a file
 

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.

VBA code halts after opening a file
 

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