opening files from explorer with startup file and addin

[Anthony Ryan]

Hi there,

Our IT department have a spreadsheet which is setup in the startup
directory of Excel 97.
When the user doubleclicks a file in windows explorer and Excel is NOT
open, excel starts and the file is opened - works fine.

I have just added in some code to the Autpen and Auto_Close
subroutinues of the startup spreadsheets to look for a addin in the
addin list and if it exists then install it. The code works.
The problem is when excel is not opened and the user doubleclicks a
file in windows explorer, excel starts up, starts the startup
spreadsheets it then installs the addin if it exists BUT does not open
the file that was doubleclicked in windows explorer.
If I go back to and doubleclick the file is opens because excel is
already opened.

I have also used the Sheet_BeforeOpen event, etc as well and also have
the same problem.
If I replace the code in the auto_open, etc subroutines with something
else e.g.
make a custom toolbar it all works fine.

The problem seems to be isolated with the code in looking for and
installing the addin.

Thanks for any help

 

[keepitcool]

when opening files thru vba you'll need to explicitly run
any autorun macro's

VBA help on the OPEN method:
Workbooks.Open "ANALYSIS.XLS"
ActiveWorkbook.RunAutoMacros xlAutoOpen

workbook_open and work_close event handlers work the other way around..
you have to disableevents if you want them not to run.
(which is why I prefer to use these)

There is no such thing as a Sheet_BeforeOpen event.

Private Sub Workbook_Open()
ALWAYS goes in the object code of ThisWorkbook else it will not work.


google will find you plenty of articles about workbook_open etc.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Anthony Ryan]

when opening files thru vba you'll need to explicitly run
any autorun macro's

VBA help on the OPEN method:
Workbooks.Open "ANALYSIS.XLS"
ActiveWorkbook.RunAutoMacros xlAutoOpen

workbook_open and work_close event handlers work the other way around..
you have to disableevents if you want them not to run.
(which is why I prefer to use these)

There is no such thing as a Sheet_BeforeOpen event.

Private Sub Workbook_Open()
ALWAYS goes in the object code of ThisWorkbook else it will not work.


google will find you plenty of articles about workbook_open etc.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

Sorry about the Sheet_BeforeOpen event thing.
This isn't any problems with the auto macros running. The problem is
the code within the auto macros do not work within the startup
spreadsheets correctly.

The code I am using is this

Public Sub Autpen()

Dim intCount As Integer


For intCount = 1 To AddIns.Count
DoEvents
If AddIns(intCount).Title = "TIS Query Report" Then
AddIns(intCount).Installed = True
Exit For
End If
Next intCount
End Sub

When the excel starts the spreadsheet in the startup directory loads
up and runs this code and if the addin exists then it is installed as
well.

It works when you launch excel from a shortcut, etc.

The problem occurs when Excel has not already been started from
something else
and when the user doubleclicks a .xls file in windows explorer. Excel
starts up, starts up the .xls file in the startup directory, runs the
code and installs the addin if present, BUT does not open the file
that was doubleclicked. That is the problem.


I have changed the code in the auto macros to generare a custom
toolbar and this work fine when a file doubleclicked in windows
explorer.
It has something to do with installing a addin.

Thanks for any help

 

[keepitcool]

have you TRIED to
change the auto_open to a workbook_open event?

I would!


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >