Problem with multiple Auto_Open Macro's

[F. Henderieckx]

Hello,

I have following piece of code: Purpose is to open the AUTO_OPEN
macro's of 2 excel-sheets in sequence.

Workbooks.Open FileName:="G:\Timesheets\Code\Excel-TMT.xls"
ActiveWorkbook.RunAutoMacros xlAutoOpen
Workbooks("Excel-TMT.xls").Activate
ActiveWorkbook.Close SaveChanges:=True

Workbooks.Open FileName:="G:\Timesheets\Code\Excel-PTL.xls"
ActiveWorkbook.RunAutoMacros xlAutoOpen
Workbooks("Excel-PTL.xls").Activate
ActiveWorkbook.Close savechanges:=True

Problem is that apparently only the first AUTO_OPEN macro is started.
The next AUTO_OPEN macro is just skipped.
When I put a comment mark before the first
ActiveWorkbook.RunAutoMacros xlAutoOpen command, the second AUTO_OPEN
macro is started!

So it seems you can only run an AUTO_OPEN macro 1 time.

How can I get around this?

Filip Henderieckx

 

[Sharad Naik]

Try like this

Dim myBook As Workbook
Set myBook = Workbooks.Open( FileName:="G:\Timesheets\Code\Excel-TMT.xls")
With myBook
.RunAutoMacros xlAutoOpen
.Close SaveChanges:=True
End With


Set myBook = Workbooks.Open (FileName:="G:\Timesheets\Code\Excel-PTL.xls")

With myBook
.RunAutoMacros xlAutoOpen
.Close SaveChanges:=True
End With

Set myBook = Nothing

Sharad

 

[Tom Ogilvy]

I don't see where Sharad's suggested solution does anything substantially
different from what you are already doing. If it solves the problem, please
post back and let us know that Sharad solved it. If not, then try this
modification of his code which does do some things differently than your
original:

Dim myBook As Workbook
Set myBook = Workbooks.Open( FileName:="G:\Timesheets\Code\Excel-TMT.xls")
With myBook
Application.Run mybook.Name & "!Auto_Open"
.Close SaveChanges:=True
End With


Set myBook = Workbooks.Open (FileName:="G:\Timesheets\Code\Excel-PTL.xls")

With myBook
Application.Run mybook.Name & "!Auto_Open"
.Close SaveChanges:=True
End With

Set myBook = Nothing

This is the second method suggested in this article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;157308
XL97: Calling RunAutoMacros Method More than Once Fails

 

[BuZzzy FF]

Hello Mr. Sharad Naik,

Thanks for your answer.

I tried to change my code like you mentioned, but I still got the same
behaviour.

So, I had a Macro which tries to repeatedly open Excel Sheets and
running the AUTO_OPEN Macro of these sheets. I have experienced now that
such a repeated action only works for the 1st, the 3th, the 5th etc…
Excel Sheet. The 2nd, 4th etc… AUTO_OPEN Macro's are just skipped. I
didn't find out why, so I decided to give the problem a nasty solution
by making sure that the real AUTO_OPEN macro's that are meant to be run,
are on the 1st, 3th, 5th... place, while on the even places a Dummy
AUTO_OPEN Macro is called.
This seems to work.
If anybody can give me a good explanation of this behaviour, I would
appreciate this.

kind regards,

Filip Henderieckx

 

[Tom Ogilvy]

I gave you an answer and a solution.

 

[BuZzzy FF]

Hello Tom,

It seems our messages crossed somehow (I'm sorry, it's probably me being
completely new on this kind of discussion forums...)

Anyway, I tried your suggestion and it seems to work! However, I figured
out that I also have to make sure that the names of the Excel Files
don't contain a "-", because then the code you suggested doesn't work
(the AUTO_OPEN macro is not found).

Conclusion:
I have 2 solutions for the problem now:

1) Use the object method like Mr. Naik suggested and use the
.RunAutoMacros xlAutoOpen statement like I did originally, but insert
also dummy calls since apparently only the 1st, 3th, 5th etc... call are
succesful (the others are just skipped)

2) Use the Object Method like Mr. Ogilvy suggested, but make sure that
the file names of the Excel files don't contain a "-".

Thanks for your help!

kind regards,

Filip Henderieckx

 

[Peter T]

Anyway, I tried your suggestion and it seems to work! However, I figured

out that I also have to make sure that the names of the Excel Files
don't contain a "-", because then the code you suggested doesn't work
(the AUTO_OPEN macro is not found).

Where workbook names include dash's, spaces and some other characters, for
many operations the name needs to be embraced with apostrophes. Working with
Tom's code, try changing the line:

Application.Run mybook.Name & "!Auto_Open"
to
Application.Run Chr(39) & mybook.Name & "'!Auto_Open"

It won't do any harm to include the apostrophes even if not required.

Regards,
Peter T

 

[BuZzzy FF]

Hello Peter,

Thanks for your help: This works.

Regards,

Filip

 

[Peter T]

You're welcome, but it is of course Tom's suggestion and code that works for
you. My bit was trivial, to adapt to your particular needs. I imagine Tom
would have told you something similar on his return.

Regards,
Peter T

 

[David Kemp]

Hi All,

Hope some of you are still around!
I tried using this as well but got an error.

-----------------------------------------------------CODE
Sub OpenWorkbooks()
Dim myBook As Workbook

Set myBook = Workbooks.Open(Filename:="C:\Documents and
Settings\kempdav1\My Documents\FBR\FBR Report v5 7075005.xls")
With myBook
Application.Run myBook.Name & "!Auto_Open"
.Close SaveChanges:=True
End With

Set myBook = Workbooks.Open(Filename:="C:\Documents and
Settings\kempdav1\My Documents\FBR\FBR Report v5 7051008.xls")
With myBook
Application.Run myBook.Name & "!Auto_Open"
.Close SaveChanges:=True
End With

Set myBook = Nothing
End Sub

--------------------------------------------------ENDCODE

I get the error:- run time error 1004
The macro 'FBR Report v5 7075005.xls!Auto_Open' cannot be found.

I am not big on coding or excel so its probably something obvious, any
ideas would be much appeciated.

Many thanks,

David