Hello,

With VBA I open a file which has am Auto_open macro but it looks that
it is not triggered as it would be if the file is opened directly (not
with VBA)

Is the a way to trigger it while opening through VBA?

Thanks
Avi

You can use the Workbook_Open event, which goes in the ThisWorkbook module.

Auto_Open is a legacy throwback, and rarely used anymore anyway.

HTH

Instead of Auto_Open in a general module place the code in
Thisworkbook module as workbook_open event


Private Sub Workbook_Open()
MsgBox "hello"
End Sub


Gord Dibben Microsoft Excel MVP

On Tue, 26 Jul 2011 13:31:03 -0700 (PDT), avi
wrote:

Hello,

With VBA I open a file which has am Auto_open macro but it looks that
it is not triggered as it would be if the file is opened directly (not
with VBA)

Is the a way to trigger it while opening through VBA?

Thanks
Avi

Without disagreeing with the wisdom previously offered, this is how you can
invoke Auto_Open:

Workbooks([Workbook name]).RunAutoMacros xlAutoOpen

"avi" wrote in message
...
Hello,

With VBA I open a file which has am Auto_open macro but it looks that
it is not triggered as it would be if the file is opened directly (not
with VBA)

Is the a way to trigger it while opening through VBA?

Thanks
Avi

avi expressed precisely :
Hello,

With VBA I open a file which has am Auto_open macro but it looks that
it is not triggered as it would be if the file is opened directly (not
with VBA)

Is the a way to trigger it while opening through VBA?

Thanks
Avi

Avi,
When you open a workbook using Sub Auto_Open manually it behaves as
expected. When you open a workbook via automation you need to tell the
workbook (and Excel) to use Sub Auto_Open as in the following example.

Dim sPath As String
sPath = ThisWorkbook.Path
If Right$(sPath, 1) < "\" Then sPath = sPath & "\"

Workbooks.Open sPath & g_sAPP_FILE
Workbooks(g_sAPP_FILE).RunAutoMacros xlAutoOpen

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On 27 juil, 01:00, GS wrote:
avi expressed precisely :

Hello,

With VBA I open a file which has am Auto_open macro but it looks that
it is not triggered as it would be if the file is opened directly (not
with VBA)

Is the a way to trigger it while opening through VBA?

Thanks
Avi

Avi,
When you open a workbook using Sub Auto_Open manually it behaves as
expected. When you open a workbook via automation you need to tell the
workbook (and Excel) to use Sub Auto_Open *as in the following example.

* Dim sPath As String
* sPath = ThisWorkbook.Path
* If Right$(sPath, 1) < "\" Then sPath = sPath & "\"

* Workbooks.Open sPath & g_sAPP_FILE
* Workbooks(g_sAPP_FILE).RunAutoMacros xlAutoOpen

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks to all of you

Looks that Garry's approach is the right one

Best regards
Avi

It happens that avi formulated :

Thanks to all of you

Looks that Garry's approach is the right one

Best regards
Avi

Avi,
Thanks for the feedback! Glad it worked for you.

<FYI
Not meaning to discount any of the advice given by others (who are
cleary more experienced than me), but I've run into too many projects
that fail miserably using code under ThisWorkbook whenever the
slightest thing that can go wrong does go wrong. Not saying it's code
errors per se, just that for some reasons known only to Excel the files
get corrupted and things go awry. I was advised to use the older
Auto_Open/Auto_Close routines by Rob Bovey some years back and so have
never had an issue ever since. Also, every client project I fix that
way also has never had their issues repeat.

So while the RunAutoMacros method may indeed be legacy, IMO it still
works better than the newer event procedures.
</FYI

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc