Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code in ThisWorkbook crashes Excel
Excel 2002
WindowsXP I have a macro in a module that works great when ran directly. But when I run it from ThisWorkbook on a BeforeSave event it crashes Excel constantly. Basically, the macro saves to an FTP site as well as the hard drive when the user saves. I can have the user hit a macro button to run my saveFTP/saveHardDrive macro directly and everything is great, but I'd like to launch this action upon a normal save as well. But, like I said, calling it up in BeforeSave crashes Excel. I've had other projects become unstable when I called a macro from within ThisWorkbook. So, I must be doing something consistently wrong. I shy away from adding any code at all to ThisWorkbook because of my experiences. Maybe I'm doing something wrong that can easily be corrected? Does anyone have ideas or do I need to furnish the exact code? Thanks, Pat Beck |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code in ThisWorkbook crashes Excel
Hi Pat
I thought I read in an earlier thread that if you use "DoEvents" it may allow the system to complete all it's tasks before returning processing to Excel. Perhaps you need the system to finish the save to your FTP site before Excel starts it's save. Be sure to read the help file on "DoEvents" before using it, it can yeild some unpredictable results. Another thought is to try using "Application.Wait(Now + TimeValue("0:00:2")) " for a few seconds at the end of the before save event, it may allow the system to process unfinished tasks through multitasking. Using Application.Wait has helped me quite a few times in the past. I hope this helps, Regards, Rocky McKinley "Pat Beck" wrote in message ... Excel 2002 WindowsXP I have a macro in a module that works great when ran directly. But when I run it from ThisWorkbook on a BeforeSave event it crashes Excel constantly. Basically, the macro saves to an FTP site as well as the hard drive when the user saves. I can have the user hit a macro button to run my saveFTP/saveHardDrive macro directly and everything is great, but I'd like to launch this action upon a normal save as well. But, like I said, calling it up in BeforeSave crashes Excel. I've had other projects become unstable when I called a macro from within ThisWorkbook. So, I must be doing something consistently wrong. I shy away from adding any code at all to ThisWorkbook because of my experiences. Maybe I'm doing something wrong that can easily be corrected? Does anyone have ideas or do I need to furnish the exact code? Thanks, Pat Beck |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code in ThisWorkbook crashes Excel
Hi, John. I left out the code in hopes I was committing an obvious error
<g, also I may have to simplify it or add elaboration for others to understand it. If need be, I'll do that and post it. To clarify, I coded only a call to another sub in the SaveBefore workbook event using: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.Run ("DualSave") End Sub Does that part look right? I noticed from testing that the crash occurs, not during the execution of my code added to the SaveBefore event, but when it is finished and Excel proceeds to do its normal save it crashes. So, it does not seem to like saving to an FTP site, then saving as the same original file (over-writing) to my hard drive, then doing its normal save. I can see how it could get confused, but do not know what to do about it. Any ideas? Thanks, Pat As you didn't post the code that you were using, it's difficult to attempt to diagnose what might be wrong with it. As a rule, I *never* actually code anything directly in the Workbook Events other than a call to another sub. Makes testing a lot easier and I don't have to worry if it's the *event* that's causing the problem since I can just add a MsgBox to the event (for testing purposes) just to see if/when it's firing. John "Pat Beck" wrote in message ... Excel 2002 WindowsXP I have a macro in a module that works great when ran directly. But when I run it from ThisWorkbook on a BeforeSave event it crashes Excel constantly. Basically, the macro saves to an FTP site as well as the hard drive when the user saves. I can have the user hit a macro button to run my saveFTP/saveHardDrive macro directly and everything is great, but I'd like to launch this action upon a normal save as well. But, like I said, calling it up in BeforeSave crashes Excel. I've had other projects become unstable when I called a macro from within ThisWorkbook. So, I must be doing something consistently wrong. I shy away from adding any code at all to ThisWorkbook because of my experiences. Maybe I'm doing something wrong that can easily be corrected? Does anyone have ideas or do I need to furnish the exact code? Thanks, Pat Beck |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code in ThisWorkbook crashes Excel
Hi, Rocky.
Actually, I'm pretty confident the system completes it save to the FTP site before it starts its own save. In fact, I think it completes that and completes my next procedure of saving it as the original file it was (over-write) on my hard drive before it tries its own save. In between, it writes info into cells and does that completely before it does its own save. I saw this by creating breakpoints and checking the results. I hope the answer is not DoEvents. <g I read the help file and it turned my brain to spaghetti. I'll do without the Save event if that's what it takes. <g I did try, however, your wait method. Unfortunately no joy. I set it for 10 seconds. It just took 10 seconds longer to crash. <g I thought maybe it was the writing into the cells confusing Excel. Maybe upon the Save launch it gets ready to save and then I change data in between causing confusion when it finally does its save. But that's unlikely. I commented out all writing lines of code and it still crashed when it started its own save. Thanks for the help and I'm all ears if you have other suggestions. Pat "Rocky McKinley" wrote in message ... Hi Pat I thought I read in an earlier thread that if you use "DoEvents" it may allow the system to complete all it's tasks before returning processing to Excel. Perhaps you need the system to finish the save to your FTP site before Excel starts it's save. Be sure to read the help file on "DoEvents" before using it, it can yeild some unpredictable results. Another thought is to try using "Application.Wait(Now + TimeValue("0:00:2")) " for a few seconds at the end of the before save event, it may allow the system to process unfinished tasks through multitasking. Using Application.Wait has helped me quite a few times in the past. I hope this helps, Regards, Rocky McKinley "Pat Beck" wrote in message ... Excel 2002 WindowsXP I have a macro in a module that works great when ran directly. But when I run it from ThisWorkbook on a BeforeSave event it crashes Excel constantly. Basically, the macro saves to an FTP site as well as the hard drive when the user saves. I can have the user hit a macro button to run my saveFTP/saveHardDrive macro directly and everything is great, but I'd like to launch this action upon a normal save as well. But, like I said, calling it up in BeforeSave crashes Excel. I've had other projects become unstable when I called a macro from within ThisWorkbook. So, I must be doing something consistently wrong. I shy away from adding any code at all to ThisWorkbook because of my experiences. Maybe I'm doing something wrong that can easily be corrected? Does anyone have ideas or do I need to furnish the exact code? Thanks, Pat Beck |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code in ThisWorkbook crashes Excel
Hi Pat,
A few things I can think of to try, as shown in the modified event procedure below. First, disable event handling while your save code is running so as not to end up calling the BeforeSave event recursively. Second, get rid of Application.Run and call the procedure directly (I'm assuming it's in the same workbook, so Application.Run shouldn't be required). Third, since your code is saving the workbook, why not just cancel Excel's save, if that's what's causing it to crash, by setting the Cancel argument to True. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ''' Cancel Excel's save and replace it with yours. Cancel = True ''' This ensures that event handling is ''' restored in case of an error. On Error GoTo ErrorExit Application.EnableEvents = False DualSave ErrorExit Application.EnableEvents = True End Sub -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Pat Beck" wrote in message ... Hi, John. I left out the code in hopes I was committing an obvious error <g, also I may have to simplify it or add elaboration for others to understand it. If need be, I'll do that and post it. To clarify, I coded only a call to another sub in the SaveBefore workbook event using: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.Run ("DualSave") End Sub Does that part look right? I noticed from testing that the crash occurs, not during the execution of my code added to the SaveBefore event, but when it is finished and Excel proceeds to do its normal save it crashes. So, it does not seem to like saving to an FTP site, then saving as the same original file (over-writing) to my hard drive, then doing its normal save. I can see how it could get confused, but do not know what to do about it. Any ideas? Thanks, Pat As you didn't post the code that you were using, it's difficult to attempt to diagnose what might be wrong with it. As a rule, I *never* actually code anything directly in the Workbook Events other than a call to another sub. Makes testing a lot easier and I don't have to worry if it's the *event* that's causing the problem since I can just add a MsgBox to the event (for testing purposes) just to see if/when it's firing. John "Pat Beck" wrote in message ... Excel 2002 WindowsXP I have a macro in a module that works great when ran directly. But when I run it from ThisWorkbook on a BeforeSave event it crashes Excel constantly. Basically, the macro saves to an FTP site as well as the hard drive when the user saves. I can have the user hit a macro button to run my saveFTP/saveHardDrive macro directly and everything is great, but I'd like to launch this action upon a normal save as well. But, like I said, calling it up in BeforeSave crashes Excel. I've had other projects become unstable when I called a macro from within ThisWorkbook. So, I must be doing something consistently wrong. I shy away from adding any code at all to ThisWorkbook because of my experiences. Maybe I'm doing something wrong that can easily be corrected? Does anyone have ideas or do I need to furnish the exact code? Thanks, Pat Beck |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code in ThisWorkbook crashes Excel
Sorry, I forgot the colon on the end of the ErrorExit label. That's air code
for you. <g Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ''' Cancel Excel's save and replace it with yours. Cancel = True ''' This ensures that event handling is ''' restored in case of an error. On Error GoTo ErrorExit Application.EnableEvents = False DualSave ErrorExit: Application.EnableEvents = True End Sub -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code in ThisWorkbook crashes Excel
Hi Pat,
Glad to hear it worked out! Come on back if you run into any other problems. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Pat Beck" wrote in message ... Brilliant! Absolutely brilliant! I didn't know I could cancel Excel's save. That's what was causing the problem. Having done that, it works magnificently! I also didn't know that method of calling another procedure. Yes, my procedures are in the same workbook. But, dummy me, I just thought Application.Run was the standard method. <g I'll start calling the way you say from now on. I'm so thankful there's experts like you and the others willing to lend a helping hand. Thank you ever so much. Pat "Rob Bovey" wrote in message ... Sorry, I forgot the colon on the end of the ErrorExit label. That's air code for you. <g Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ''' Cancel Excel's save and replace it with yours. Cancel = True ''' This ensures that event handling is ''' restored in case of an error. On Error GoTo ErrorExit Application.EnableEvents = False DualSave ErrorExit: Application.EnableEvents = True End Sub -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 View Code ThisWorkbook | Excel Discussion (Misc queries) | |||
Programatically adding macro to Excel - "ThisWorkbook" | Excel Discussion (Misc queries) | |||
ThisWorkbook of personal.xls | Excel Discussion (Misc queries) | |||
Code crashes after clearing comments | Excel Worksheet Functions | |||
ThisWorkbook Macros | Excel Programming |