Events not loading in Excel98 for workbooks created in Excel 2.1

  • Thread starter Thread starter aah-boy
  • Start date Start date
A

aah-boy

Hi,

I'm not sure if anyone can help me here - I think it will take a bit
of digging, but here goes...

I'm using Application OnLoad events in Excel98 and Excel2000 versions
to scan worksheets - so far so good. I'm being sent Workbooks created
in Excel 2.1 and the events don't work for the Excel98 version of the
code.

To me it seems as if these workbooks are not real workbooks at all -
they all come in at 2Kb

This doesn't seem to pose a threat to Excel2000 - it handles them as I
want it to, unfortunately Excel98 doesn't. It refuses to load the
events at all.

Can anyone point me in the direction I need to go from here please?

Dave
 
Dave,

I can confirm that the Application_WorkbookOpen event procedure
doesn't work in Excel97 when opening Excel files created in
version 4 or earlier. I don't know the reason why. Probably just
a bug that was fixed in Excel 2000.

So what to do? Once the workbook is open, the Activate event will
fire, so you can at least get notification that the workbook has
been made active. One solution would be this (untested and off
the top of my head):

1) Create a collection object that holds references to workbooks.

2) Trap the App_WorkbookActivate when any workbook is made
active.

3) Compare WB.Name to the keys in the collection, and if the key
isn't found, then you have a new workbook.

4) Add the new workbook reference to the collection, using the
name of the workbook (just the file name, not the path) as the
Key parameter of the Add method.

5) Trap the App_WorkbookBeforeClose (which does fire in earlier
versions of workbook) to remove the item from your collection.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top