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?

--
Ron Ruble

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: (Peter Huang [MSFT])
Organization: Microsoft
Date: Mon, 08 Sep 2003 07:33:35 GMT
Subject: 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.

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"
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:
Date: Sat, 06 Sep 2003 21:25:05 GMT
NNTP-Posting-Host: 12.87.130.73
X-Complaints-To:
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!newsfee d00.sul.t-online.de!newsfe
e
d01.sul.t-online.de!t-online.de!newspeer1-gui.server.ntli.net!ntli.net!peer
0
1.cox.net!cox.net!cyclone1.gnilink.net!wn11feed!w orldnet.att.net!bgtnsc04-n
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?

--
Ron Ruble

Comments inline

"Peter Huang [MSFT]" wrote in message ...
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.

<snip

In the machines the auto_open macro didnot work, what version of excel did
you install? XL95 or XL97?

Excel 97.

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

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

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.
--------------------
From: "Ron Ruble"
Newsgroups: microsoft.public.excel.programming
References:


Subject: Auto_Open not running when file opened normally
Lines: 30
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID:
Date: Mon, 08 Sep 2003 23:42:44 GMT
NNTP-Posting-Host: 12.87.163.2
X-Complaints-To:
X-Trace: bgtnsc04-news.ops.worldnet.att.net 1063064564 12.87.163.2 (Mon,
08 Sep 2003 23:42:44 GMT)
NNTP-Posting-Date: Mon, 08 Sep 2003 23:42:44 GMT
Organization: AT&T Worldnet
Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed 00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!wn14fee d!wn13feed!wn11f
eed!worldnet.att.net!bgtnsc04-news.ops.worldnet.att.net.POSTED!not-for-mail
Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:413953
X-Tomcat-NG: microsoft.public.excel.programming

Comments inline

"Peter Huang [MSFT]" wrote in message
...
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.

<snip

In the machines the auto_open macro didnot work, what version of excel
did
you install? XL95 or XL97?

Excel 97.

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

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

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.

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.)

Ron Ruble wrote:

-----Original Message-----

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.

Other people are reporting similar things; sometimes it
works, sometimes it doesn't. Could it be a timing problem?

I hope not; we went to Auto_open to -prevent- a timing
problem.

--

Dave Peterson

"Dave Peterson" wrote in message ...
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.

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 wrote:

"Dave Peterson" wrote in message ...
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

"Stephen Bullen" wrote in message ...
Hi Ron,

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.

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.

"Peter Huang [MSFT]" wrote in message ...
Hi Ron,

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

Yes, I did. see msg id:

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/assistan...010346281033&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" wrote in message ...
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.

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"
Newsgroups: microsoft.public.excel.programming
References:








Subject: Auto_Open not running when file opened normally
Lines: 62
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:
Date: Fri, 12 Sep 2003 09:51:02 GMT
NNTP-Posting-Host: 12.87.131.193
X-Complaints-To:
X-Trace: bgtnsc04-news.ops.worldnet.att.net 1063360262 12.87.131.193 (Fri,
12 Sep 2003 09:51:02 GMT)
NNTP-Posting-Date: Fri, 12 Sep 2003 09:51:02 GMT
Organization: AT&T Worldnet
Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed 00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!news-out1.nntp.be!propa
gator2-sterling!news-in-sterling.nuthinbutnews.com!cyclone1.gnilink.net!wn 11
feed!worldnet.att.net!bgtnsc04-news.ops.worldnet.att.net.POSTED!not-for-mail
Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:415243
X-Tomcat-NG: microsoft.public.excel.programming


"Peter Huang [MSFT]" wrote in message
...
Hi Ron,

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

Yes, I did. see msg id:

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/assistan...010346281033&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" wrote in message
...
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.

"longda" wrote in message ...
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.