Triggering auto_open

  • Thread starter Thread starter avi
  • Start date Start date
A

avi

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
 
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
 
Without disagreeing with the wisdom previously offered, this is how you can
invoke Auto_Open:

Workbooks([Workbook name]).RunAutoMacros xlAutoOpen
 
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
Click to expand...

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
 
avi expressed precisely :





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
Click to expand...

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
Click to expand...

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>
 
Back
Top