Auto_Open not running when file opened normally

Ron Ruble · Sep 6, 2003

This is in Excel 97. We have an Auto_open macro in a
worksheet, which is not running when the user opens the
file from the desktop.

We had to use Auto_Open rather than WorkbookOpen,
because this workbook will sometimes be opened by another
workbook (the caller uses RunAutoMacros), and
WorkbookOpen runs too quick. The workbook is loaded
from the corporate intranet site via a URL, and
WorkbookOpen runs a bit too early; causes timing issues.

Its working fine called from the other workbook, but on
some machines (mine and one of the testers; Windows
95), Auto_Open isn't running. As a workaround, I modified
Auto_Open and WorkbookOpen to call the same function,
and use a global variable to track whether the function has
run or not, but this concerns me. I could see timing issues
happening here if WorkbookOpen and Auto_Open
could overlap.

I can't find any references to Auto_Open being suppressed
-interactively-; WorkbookOpen runs fine, on the same
machines.

Any ideas?

Peter Huang [MSFT] · Sep 8, 2003

Hi Ron,

You may also try the code below to see if the problem persists.
Sub test()
ActiveWorkbook.RunAutoMacros xlAutoOpen
End Sub

Please have a try and let me know the result.

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

Newsgroups: microsoft.public.excel.programming
From: (e-mail address removed) (Peter Huang [MSFT])
Organization: Microsoft
Date: Mon, 08 Sep 2003 07:33:35 GMT
Subject: RE: Auto_Open not running when file opened normally
X-Tomcat-NG: microsoft.public.excel.programming
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Hi Ron,

Its working fine called from the other workbook, but on
some machines (mine and one of the testers; Windows
95), Auto_Open isn't running.

Click to expand...

In the machines the auto_open macro didnot work, what version of excel did
you install? XL95 or XL97?
I suggest you create a new excel workbook and establish a new Auto_Open
Macro in the machines above(i.e. the "problem" machines)
e.g.
Sub Auto_Open()
MsgBox "helo"
End Sub

You may have a test and let me know the result.

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

From: "Ron Ruble" <[email protected]>
Newsgroups: microsoft.public.excel.programming
Subject: Auto_Open not running when file opened normally
Lines: 32
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Date: Sat, 06 Sep 2003 21:25:05 GMT
NNTP-Posting-Host: 12.87.130.73
X-Complaints-To: (e-mail address removed)
X-Trace: bgtnsc04-news.ops.worldnet.att.net 1062883505 12.87.130.73 (Sat, 06 Sep 2003 21:25:05 GMT)
NNTP-Posting-Date: Sat, 06 Sep 2003 21:25:05 GMT
Organization: AT&T Worldnet
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!newsfe

Click to expand...

e

d01.sul.t-online.de!t-online.de!newspeer1-gui.server.ntli.net!ntli.net!peer

Click to expand...

0

1.cox.net!cox.net!cyclone1.gnilink.net!wn11feed!worldnet.att.net!bgtnsc04-n

Click to expand...

e

ws.ops.worldnet.att.net.POSTED!not-for-mail
Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:413412
X-Tomcat-NG: microsoft.public.excel.programming

This is in Excel 97. We have an Auto_open macro in a
worksheet, which is not running when the user opens the
file from the desktop.

We had to use Auto_Open rather than WorkbookOpen,
because this workbook will sometimes be opened by another
workbook (the caller uses RunAutoMacros), and
WorkbookOpen runs too quick. The workbook is loaded
from the corporate intranet site via a URL, and
WorkbookOpen runs a bit too early; causes timing issues.

Its working fine called from the other workbook, but on
some machines (mine and one of the testers; Windows
95), Auto_Open isn't running. As a workaround, I modified
Auto_Open and WorkbookOpen to call the same function,
and use a global variable to track whether the function has
run or not, but this concerns me. I could see timing issues
happening here if WorkbookOpen and Auto_Open
could overlap.

I can't find any references to Auto_Open being suppressed
-interactively-; WorkbookOpen runs fine, on the same
machines.

Any ideas?

Click to expand...

Ron Ruble · Sep 9, 2003

Comments inline

Peter Huang said:
Hi Ron,

You may also try the code below to see if the problem persists.
Sub test()
ActiveWorkbook.RunAutoMacros xlAutoOpen
End Sub

I'll give it a shot. Connecting to the newsgroups at work
is a bit problematic.

Excel 97.

I'll give it a shot; I've been a little busy today to try things.

Peter Huang [MSFT] · Sep 9, 2003

Hi Ron,

I will appreciate your effort, this will help me identify the problem more
quickly.

If you have tested my code, please feel free to let me know the result.

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

Ron Ruble · Sep 9, 2003

One additional peculiar thing happened this morning.

I opened one copy of the workbook; as usual, Auto_Open
did not run.

I opened a second copy; Auto_Open ran in the second copy.

I have no guess why.

Dave Peterson · Sep 10, 2003

I'm not sure that this'll help, but I saw a reference/guess to timing. And I've
see some posts that suggest doing this sometimes helps with timing issues in
auto_open procedures:

Sub Auto_Open()
Application.OnTime Now, "Continue_Auto_open"
End Sub

Sub Continue_Auto_open()
' the real part of your code goes here
End Sub

(I've seen this suggested for workbook_open issues, too.)

Peter Huang [MSFT] · Sep 10, 2003

Hi Ron,

I can not reproduce the problem. Did you tried my suggest in my last post?
It is necessary for me to troubleshoot the problem, and will help me narrow
down your problem more quickly. Please have a try and let me know the
result.

Here is a link you may have a look.
http://office.microsoft.com/assistance/preview.aspx?AssetID=HA010346281033&C
TT=6&Origin=EC010553071033

The WorkBook_Open is a event which will be trigger every time the
workbook_open is opened.
while the Auto_Open is a specific macro which will be invoke by Excel.
So the time problem may be related with how many code will be executed in
the Auto_Open or WorkBook_Open macro.

I look forward to hearing from you.,

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------

Content-Class: urn:content-classes:message
From: "Ron Ruble" <[email protected]>
Sender: "Ron Ruble" <[email protected]>
References: <[email protected]>

<[email protected]>
<[email protected]>
<[email protected]>
<My#[email protected]>

Ron Ruble · Sep 11, 2003

Dave Peterson said:
I'm not sure that this'll help, but I saw a reference/guess to timing. And I've
see some posts that suggest doing this sometimes helps with timing issues in
auto_open procedures:

Sub Auto_Open()
Application.OnTime Now, "Continue_Auto_open"
End Sub

Sub Continue_Auto_open()
' the real part of your code goes here
End Sub

In fact, this is what we do in Auto_Open.
We use a third party tool that interfaces with Excel
(Essbase OLAP add in), which barfs if the workbook
that loads us is still active.

The problem is that we never hit the auto_open function
to register the OnTime function.

For debugging purposes I have a messagebox in the
Auto_Open like so:

Sub Auto_Open()
MsgBox "Hit Auto_Open"
Application.OnTime Now, "Continue_Auto_open"
End Sub

But we never get the message box.

Dave Peterson · Sep 12, 2003

Oops. I reread the original post. (I missed it in the subject line, too.)

But I don't have any other guess.

Did you try Stephen Bullen's suggestion?

Ron Ruble · Sep 12, 2003

Stephen Bullen said:
Hi Ron,

I would suggest structuring this using the following method:

Put the code to run in a standard module:

Public Sub CodeToRun()

End Sub

Then use WorkbookOpen to run the code in that workbook, for when it is
opened from the desktop.

When the workbook needs to be opened by another workbook, do the
following in that workbook:

Application.EnableEvents = False
Set oBk = Workbooks.Open("MyBook.xls")
Application.EnableEvents = True

'Later
Application.Run "'" & oBk.Name & "'!CodeToRun"

The problem is that the application that invokes this is a
"Corporate standard, tried and true, tested and debugged"
application. Making changes to is is politically delicate,
and more likely to cancel our project than get the changes
made to the other app, at this point.

We can tolerate the fact that the macro doesn't run interactively.
It's not a showstopper. However, we have had a number of
timing and focus related issues with this project, and running
into an undocumented Auto_Open problem, that nobody in
the Google archives seems to have found scares the hell
out of me.

The 'EnableEvents' lines prevent the WorkbookOpen code from running
when the workbook is opened, so you can then explicitly run the routine
you want using Application.Run

That said, the only time I've seen Auto_Open not run is when the user
has held down the shift key while the workbook is opening.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

Thanks for your suggestions.

Ron Ruble · Sep 12, 2003

Peter Huang said:
Hi Ron,

I can not reproduce the problem. Did you tried my suggest in my last post?

Yes said:
It is necessary for me to troubleshoot the problem, and will help me narrow
down your problem more quickly. Please have a try and let me know the
result.

Here is a link you may have a look.
http://office.microsoft.com/assistance/preview.aspx?AssetID=HA010346281033&C
TT=6&Origin=EC010553071033

The WorkBook_Open is a event which will be trigger every time the
workbook_open is opened.
while the Auto_Open is a specific macro which will be invoke by Excel.
So the time problem may be related with how many code will be executed in
the Auto_Open or WorkBook_Open macro.

That link has no new information for me, I'm afraid.

See the other reply. Auto_open just displays a messagebox, then registers
an OnTime function to perform the processing.

As I said in my reply to Dave Peterson:

Dave Peterson said:
I'm not sure that this'll help, but I saw a reference/guess to timing. And I've
see some posts that suggest doing this sometimes helps with timing issues in
auto_open procedures:

Sub Auto_Open()
Application.OnTime Now, "Continue_Auto_open"
End Sub

Sub Continue_Auto_open()
' the real part of your code goes here
End Sub

In fact, this is what we do in Auto_Open.
We use a third party tool that interfaces with Excel
(Essbase OLAP add in), which barfs if the workbook
that loads us is still active.

The problem is that we never hit the auto_open function
to register the OnTime function.

For debugging purposes I have a messagebox in the
Auto_Open like so:

Sub Auto_Open()
MsgBox "Hit Auto_Open"
Application.OnTime Now, "Continue_Auto_open"
End Sub

But we never get the message box.

Peter Huang [MSFT] · Sep 12, 2003

Hi Ron,

Since the Auto_Open Macro will be invoked in a new workbook, I think you
may try to add your code to the new workbook step by step, so that you can
figure out the problem.[I guess the third party tool may be a concern, you
may try to remove it first]
Can you post more information for me to reproduce the problem, so that we
can help you?

I look forward to hearing from you.

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

From: "Ron Ruble" <[email protected]>
Newsgroups: microsoft.public.excel.programming
References: <[email protected]>

<[email protected]>
<[email protected]>
<[email protected]>
<My#[email protected]>
<[email protected]>
<[email protected]>

Ron Ruble · Sep 21, 2003

longda said:
Did you put the Auto_open into a seperate module and not in the
Workbook or any sheet objects.

Yes I did.

I don't know if it should always be in a seperate module or not

Yes, it should.

Thanks, but I'm leaving this for a while. It's not critical
to the application, and I don't have the time to pursue
it right now.