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