Macro conflict causing problems

[Ken McLennan]

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]

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

[Ken McLennan]

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

[Ken McLennan]

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

[Dave Peterson]

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

[Ken McLennan]

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