Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro conflict causing problems
G'day there One & All,
I'm back again with a problem (surprise, surprise). This time, it's not all of my own making. I'm writing an application which opens a whole series of workbooks, selects the first sheet from each & copies it as a new sheet into my workbook where my users filter/sort/pick & choose and then print out the data they want. That's not a problem and works fine. Where I'm finding a conflict is that there may well be up to 50 or so sheets to be imported from across a network. Sometimes our network gets a little tired and its tiny little legs can't run very fast. Hence it may take a while to get all the required data. This still works fine, although slowly. This slowness impels my users to start the process and then go get a coffee or do something else while this is all running. However the worksheets being imported include code that I didn't write. The code is passworded; I have no access; and the author is not available for me to punch into submission. So his code remains. Not only that, but his code works quite well and I have no inclination to rewrite his application. My problem is that his code displays a splash screen. It has useful information for first time users (we have frequent staff changes and there are often new users on the floor) and a check box for the "If you never want to see this again" function. That would be just dandy, but when my application opens his workbooks, those ones where the splash screen checkbox isn't checked opens the modal splash screen. It then sits there waiting for someone to click the "OK" button. It's not so bad if that's the last sheet to be imported, but if it's the first then my user comes back with his/her coffee and chocolate biscuits and finds that nothing's been done in his/her absence. Clicking the button resolves the issue as it all just continues running until the next unchecked splash screen, or completion. Whichever comes first. Is there a way that I can open the other workbooks without triggering the splash screen code, which I assume to be fired by the Workbook_Open event? An online search found code which will allow me to transfer the data without opening the source workbook, but to use this method I need to know the used range of the source sheets and that's something I don't have knowledge of prior to importation. The used range is not consistent. It varies from workbook to workbook and week to week so that method isn't really practical. My application still works, but at present my ears burn from the cursing and swearing. Can anyone offer advice? (other than "Buy some earplugs!!) Thanks for listening, Ken McLennan Qld, Australia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro conflict causing problems
I'm betting that you're right about the code that creates the splash screen is
in the workbook_open event for that excel file. If you turn off events before you open the file, then that code won't run. application.enableevents = false 'code to open the other workbook application.enableevents = true There could be code in the workbook_beforeclose event, too. You may want to add the same kind of thing when you close the other workbook. The only bad thing that I can think of is if there is code that you actually need to run when that workbook opens. You'll either have to replace that functionality or live with the existing problem. ps. You may want to avoid opening the file and updating the links, too. Check the Workbooks.open statement in VBA's help to see how to do this. ======== I'm not sure if this will help your speed issue, but when I've had a speed problem with workbooks strewn over remote networks, I've sometimes added some code to: Create a temporary folder in the Windows Temp folder copy all the files to that I need to that location (maybe using FSO??) process those local copies of the file delete that temporary folder in the windows temp folder Ken McLennan wrote: G'day there One & All, I'm back again with a problem (surprise, surprise). This time, it's not all of my own making. I'm writing an application which opens a whole series of workbooks, selects the first sheet from each & copies it as a new sheet into my workbook where my users filter/sort/pick & choose and then print out the data they want. That's not a problem and works fine. Where I'm finding a conflict is that there may well be up to 50 or so sheets to be imported from across a network. Sometimes our network gets a little tired and its tiny little legs can't run very fast. Hence it may take a while to get all the required data. This still works fine, although slowly. This slowness impels my users to start the process and then go get a coffee or do something else while this is all running. However the worksheets being imported include code that I didn't write. The code is passworded; I have no access; and the author is not available for me to punch into submission. So his code remains. Not only that, but his code works quite well and I have no inclination to rewrite his application. My problem is that his code displays a splash screen. It has useful information for first time users (we have frequent staff changes and there are often new users on the floor) and a check box for the "If you never want to see this again" function. That would be just dandy, but when my application opens his workbooks, those ones where the splash screen checkbox isn't checked opens the modal splash screen. It then sits there waiting for someone to click the "OK" button. It's not so bad if that's the last sheet to be imported, but if it's the first then my user comes back with his/her coffee and chocolate biscuits and finds that nothing's been done in his/her absence. Clicking the button resolves the issue as it all just continues running until the next unchecked splash screen, or completion. Whichever comes first. Is there a way that I can open the other workbooks without triggering the splash screen code, which I assume to be fired by the Workbook_Open event? An online search found code which will allow me to transfer the data without opening the source workbook, but to use this method I need to know the used range of the source sheets and that's something I don't have knowledge of prior to importation. The used range is not consistent. It varies from workbook to workbook and week to week so that method isn't really practical. My application still works, but at present my ears burn from the cursing and swearing. Can anyone offer advice? (other than "Buy some earplugs!!) Thanks for listening, Ken McLennan Qld, Australia -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro conflict causing problems
G'day there Dave,
If you turn off events before you open the file, then that code won't run. application.enableevents = false 'code to open the other workbook application.enableevents = true Of course!! It's obvious once you write it out plainly like that! There could be code in the workbook_beforeclose event, too. You may want to add the same kind of thing when you close the other workbook. Hmmm... not sure. I'll have to check, but I have used the other spreadsheets before, and I'm pretty sure that the macros do 3 things only: 1. Splash screen 2. New help function 3. Insert set text from various key combinations If I turn off events, the new help screen code won't be initiated and hence I won't have to turn it off at the end. The only bad thing that I can think of is if there is code that you actually need to run when that workbook opens. You'll either have to replace that functionality or live with the existing problem. No. There's nothing in code that I need from the other workbook. I only want the data from the first worksheet. ps. You may want to avoid opening the file and updating the links, too. Check the Workbooks.open statement in VBA's help to see how to do this. Some users have links in their own versions of the workbooks, but I'm working with copies so even if there are links they won't be critical to my application, nor to the source workbooks as the users will be working on the originals. ======== I'm not sure if this will help your speed issue, but when I've had a speed problem with workbooks strewn over remote networks, I've sometimes added some code to: Create a temporary folder in the Windows Temp folder copy all the files to that I need to that location (maybe using FSO??) process those local copies of the file delete that temporary folder in the windows temp folder Nodnodnodnod. That's a good idea, and I may yet introduce the technique. However for the moment, the copying of the worksheets is only done once a fortnight at most, and probably only once a month (they're staff rosters). I've tried persuading our tech support to provide us with a better network, but so far my suggestions have not been met with enthusiasm. Something about the cost of upgrading a network that covers thousands of terminals across the entire state. Our one tech officer reckons her office budget wouldn't quite cover it. I tried to convince her that she should forego wages to help pay for it and she threw things at me. Some of these tech officers just have no sense of humour!! Thanks very much for your advice, Dave. I'm sure that will do the trick. I'll try it later tonight, but for now I'm off to read the grandson a story. Thanks once again See ya Ken McLennan Qld, Australia |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro conflict causing problems
G'day there Again, Dave,
There could be code in the workbook_beforeclose event, too. You may want to add the same kind of thing when you close the other workbook. I just tried your method now, and it worked like a charm. Thanks very muchly for your advice, and sharing your expertise. It's greatly appreciated. See ya Ken McLennan Qld, Australia |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro conflict causing problems
Tell her that you're willing for her whole staff to go without a salary if
that's what it takes! Maybe that'll help -- or get you in trouble! Ken McLennan wrote: <<snipped I tried to convince her that she should forego wages to help pay for it and she threw things at me. Some of these tech officers just have no sense of humour!! <<snipped -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro conflict causing problems
G'day there Dave,
Tell her that you're willing for her whole staff to go without a salary if that's what it takes! Maybe that'll help -- or get you in trouble! Oh **** no!!! She's got quite a few large objects in that office; and most of them have sharp corners!!!! See ya Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Watermark Causing Print Problems | Excel Discussion (Misc queries) | |||
interior.colorindex causing problems | Excel Programming | |||
Rand () causing Problems. | Excel Discussion (Misc queries) | |||
Splash Screen Causing Problems | Excel Programming | |||
Addins causing problems | Excel Programming |