Auto_Open not running when file opened normally

[Ron Ruble]

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 Autpen 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), Autpen isn't running. As a workaround, I modified
Autpen 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 Autpen
could overlap.

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

Any ideas?

 

[Peter Huang [MSFT]]

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: Autpen 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), Autpen 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 Autpen
Macro in the machines above(i.e. the "problem" machines)
e.g.
Sub Autpen()
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: Autpen 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

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!worldnet.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 Autpen 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), Autpen isn't running. As a workaround, I modified
Autpen 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 Autpen
could overlap.

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

Any ideas?

 

[Ron Ruble]

Comments inline

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]]

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]

One additional peculiar thing happened this morning.

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

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

I have no guess why.

 

[Dave Peterson]

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 Autpen()
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]]

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 Autpen 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 Autpen 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]

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 Autpen()
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 Autpen.
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
Autpen like so:

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

But we never get the message box.

 

[Dave Peterson]

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]

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 Autpen 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 Autpen 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]

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

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 Autpen()
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 Autpen.
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
Autpen like so:

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

But we never get the message box.

 

[Peter Huang [MSFT]]

Hi Ron,

Since the Autpen 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]

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.