Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA just stops when opening a macro workbook
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" -- Thx MSweetG222 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA just stops when opening a macro workbook
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")) -- Regards, OssieMac "MSweetG222" wrote: 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" -- Thx MSweetG222 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA just stops when opening a macro workbook
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 wrote: 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" -- Thx MSweetG222 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA just stops when opening a macro workbook
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 "Dave Peterson" wrote: 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 wrote: 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" -- Thx MSweetG222 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA just stops when opening a macro workbook
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. -- Thx MSweetG222 "OssieMac" wrote: 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")) -- Regards, OssieMac "MSweetG222" wrote: 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" -- Thx MSweetG222 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA just stops when opening a macro workbook
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. -- Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility Free and Pro versions "MSweetG222" wrote in message ... 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" -- Thx MSweetG222 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA just stops when opening a macro workbook
Tim,
Thank you. I will try your suggestions and let you know. Thank you for your help. -- Thx MSweetG222 "Tim Zych" wrote: 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. -- Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility Free and Pro versions "MSweetG222" wrote in message ... 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" -- Thx MSweetG222 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA just stops when opening a macro workbook
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. -- Thx MSweetG222 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA just stops when opening a macro workbook
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. -- Regards, OssieMac |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA just stops when opening a macro workbook
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" wrote: 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" -- Thx MSweetG222 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA just stops when opening a macro workbook
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. -- Thx MSweetG222 "Barb Reinhardt" wrote: 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" wrote: 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" -- Thx MSweetG222 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening a Workbook with a Macro | Excel Discussion (Misc queries) | |||
Opening a csv file stops macro | Excel Programming | |||
Opening a file stops a Macro | Excel Worksheet Functions | |||
Formula stops working after re-opening worksheet | Excel Worksheet Functions | |||
Macro stops when another workbook is open | Excel Programming |