VBA just stops when opening a macro workbook

[MSweetG222]

Hello,

I have written VBA code to open workbooks, perform procedures and then saves
and close the workbooks. Every thing works just fine until I get to a
workbook that has a macro, then my VBA stops just after the workbook with the
macro is opened.
No error messages. It just stops. The call stack totally disappears.

I have tried turning off events just before opening the workbook with the
macro, but that did not work.

I have tried "On Error" statements, that did not work.

I have tried signing with workbook with a trusted certificate, did not help.

QUESTION: What do I need to do to open macro workbooks and perform my
procedures with out my VBA terminating like this?

Example:

Application.EnableEvents = False
Workbooks.Open Filename:= "C:\Test\ExcelWB_Template.xls"
'>>> This is where my VBA just stops
Range("A1").FormulaR1C1 = "ABC Company"
Range("A2").FormulaR1C1 = "123"

 

[OssieMac]

Your code appears to work OK under test so I can't be sure of exactly what
the problem is but it might be that Excel is attempting to compile macros or
recalculate while the code is attempting to write to the worksheet.

I don't really know if this will help or not but try placing a wait command
immediately after the workbook open line. I understand that the VBA code
stops until the wait time has expired but background activity continues. This
might allow Excel to finsh what it is attempting to do after opening the
workbook. Test with different wait periods. The following waits for 10 secs.

Application.Wait (Now + TimeValue("0:00:10"))

 

[Dave Peterson]

I'm betting that you're running your macro using a shortcut key. And that
shortcut key combination includes the shift-key.

Remove the shift-key from the shortcut key and try it again.

 

[MSweetG222]

Dave,

Thanks for your idea.

I checked, there is not a hotkey associated with my macro or the macro in
the workbook being opened. I also tried turning off all addins and stepping
thru the code 1 line at a time using the f8 key...the VBA still dies just
after the workbook with the macros are open.

I even thought it might have something to do with the macros being event
driven, sheet level macros, class macros or module level type macros...no
change in results.
--
Thx
MSweetG222

I'm betting that you're running your macro using a shortcut key. And that
shortcut key combination includes the shift-key.

Remove the shift-key from the shortcut key and try it again.

 

[MSweetG222]

OssieMac,

I inserted your line code just after the workbook open code and the VBA
never even made it to that line. I even stepped thru the code 1 line at a
time using the f8 key, no change in results. The VBA died just after the
workbook was opened.

Just to let you know, I also turned off all addins (including COMs listed
under the COM addin box). No change. There is an Adobe PDF addin but I
can't find where to turn that off. When I find it, I will try your idea
again.

Thank your for the suggestion.

 

[Tim Zych]

A few ideas to help debug, not necessarily in order.

Close Excel and reopen.

Replace ExcelWB_Template.xls with a fresh new workbook with no macros and no
data, then run the macro, to see if that fixes it. Also, move just the
relevant portion of the workbook-opener code to a fresh new workbook and see
if it can open the existing ExcelWB_Template.xls. The idea here is to see
whether the problem lies with the calling workbook or target workbook

Are you using error handling prior to opening the workbook? E.g. On Error
Resume Next? If so comment that out so that no errors are suppressed.

Clean the code in the calling workbook using a code cleaner (e.g. Rob
Bovey's code cleaner).

Step through the code manually and then execute it in its current state
(e.g. via a button click event). Is there any difference?

See if there is anything wrong with WB_Template.xls such as illegal sheet
names, invalid range names, bad links or some other problem.

 

[MSweetG222]

Tim,

Thank you. I will try your suggestions and let you know. Thank you for
your help.
--
Thx
MSweetG222

A few ideas to help debug, not necessarily in order.

Close Excel and reopen.

Replace ExcelWB_Template.xls with a fresh new workbook with no macros and no
data, then run the macro, to see if that fixes it. Also, move just the
relevant portion of the workbook-opener code to a fresh new workbook and see
if it can open the existing ExcelWB_Template.xls. The idea here is to see
whether the problem lies with the calling workbook or target workbook

Are you using error handling prior to opening the workbook? E.g. On Error
Resume Next? If so comment that out so that no errors are suppressed.

Clean the code in the calling workbook using a code cleaner (e.g. Rob
Bovey's code cleaner).

Step through the code manually and then execute it in its current state
(e.g. via a button click event). Is there any difference?

See if there is anything wrong with WB_Template.xls such as illegal sheet
names, invalid range names, bad links or some other problem.

 

[gimme_this_gimme_that]

I don't know if this has anything to do with why our VBA code is
failing.

But the code should look more like this (untested)

Dim book as Workbook
Dim sheet as Worksheet
    Application.EnableEvents = False
    Set book = Workbooks.Open Filename:= "C:\Test
\ExcelWB_Template.xls"
Set sheet = book.Sheets("Sheet 1")
sheet.Range("A1").FormulaR1C1 = "ABC Company"
sheet.Range("A2").FormulaR1C1 = "123"

As a general rule two things will make VBA hang:

1. A failed internet connection (you can have a connection to the
internet but some server might not be responding), or
2. Some spot in the code that is resource intensive - such as a huge
copy and paste instruction.

The template that you are opening might be doing something related to
items 1 and 2.

 

[OssieMac]

Only clutching at straws but I don't suppose that you are getting an alert
dialog box asking you to confirm to open a workbook with macros and that
dialog box is hidden under the other windows. Try clicking all the window
tabs in the Task Bar and check them.

 

[Barb Reinhardt]

I bet you're multitasking while this macro is being run and you are typing
the shift key during execution. Read on here.

http://www.jkp-ads.com/Articles/WorkbookOpenBug.asp

I've used this for a while and since I've moved to 2007, I've run into
trouble because I believe I'm having a bit of a delay between testing for the
shift key and the workbook opening. Hopefully it'll work now while I'm
typing this, because I have it in code that is executing now.

HTH,
Barb Reinhardt

 

[MSweetG222]

Thank you everyone for your help. I tried the suggestions by Tim & Barb as
well. Barb, I did add your code also, because I do multi-task and it could be
a combination of things.

I finally got it to work. This is what I had to do.

1. Clean code
2. Set EnableEvents = False
3. Set AutomationSecurity = 1
4. Check for Shift Key
5. Open the workbook
6. Set AutomationSecurity = 3
7. Re-enable EnableEvent = True

Just to let you know, I did try setting security to low via the user
interface panel and that did not help.

Does anyone see a problem with setting security to low while EnableEvents is
set to False for the few seconds it takes to get the workbook open?

Can a malicious macro run if EnableEvents is set to false when you open the
workbook?

P.S. Sorry it took so long to respond. I am have a dickens of a time
posting to the board. I keep getting Service Temp Unavailable.