Detecting version of Excel upon opening
Posted by Tuc on May 17, 2001 9:34 PM
Does anyone have a elegant way to detect the version of Excel upon opening a workbook?
I'm moving back and forth between Excel 97 and Excel 2000 (Don't ask). I spent a few hours today working on a macro that uses the VBA IDE. I'm copying code modules between workbooks. (See Tip #43 at http://www.mrexcel.com/tip043.shtml) I was trying to be efficient and use Early Binding, I set a reference to the VBA IDE (Microsoft Visual Basic fo Applications Extensibility 5.3) and then took it from XL2K to XL97. Then I took it back and... well it got hosed. So bad that when you open the file it opens, then closes everything, including Excel. So what I am thinking is that maybe I could prevent the file corruption if I detected the version of XL first. Then maybe I could set the reference dynamically.
Here are my thoughts:
I could use the Auto_Open event in Excel (or workbook_Open in XL 2000) to see if the version property of the Application object is 9.0 (for XL2k) and then throw up a splash screen and then exit gracefully. Anyone got anything better?
BTW - I got around the VBIDE problem by removing the reference and declaring my CodeComponent objects as objects (i.e. I used Late Binding). It works fine, but I rather like the features of the IDE when I'm coding.
Thanks for your time in reading this.
Posted by Dave Hawley on May 17, 2001 9:43 PM
Hi Tuc
Private Sub Workbook_Open()
MsgBox Application.Version
End Sub
BTW Excel 97 also supports Workbook_open
Dave
OzGrid Business Applications
Posted by Tuc on May 18, 2001 7:07 AM
Well not exactly...
The documentation says that it supports Workbook_open, but I have had some issue with it working every time. Auto_Open works every time in Excel 97. I would use workbook_Open if the solution was Excel 2000.
Posted by Tuc on May 18, 2001 7:07 AM
Well not exactly...
The documentation says that it supports Workbook_open, but I have had some issue with it working every time. Auto_Open works every time in Excel 97. I would use workbook_Open if the solution was Excel 2000.
Posted by Dave Hawley on May 18, 2001 7:41 AM
Re: Well not exactly...
Tuc, if you haved saved the file as 97 version the Workbook_Open will work. What issues have you had? Perhaps you had Events to False unknowingly?
Dave
OzGrid Business Applications
Posted by Tuc on May 18, 2001 8:16 AM
Re: Well not exactly...
Dave,
Great minds think alike! I purposedly avoided using the EnabledEvents property of the application object so that would not be an issue. As for saving it as an Excel 97 document, it was developed initially only on the Excel 97 platform (Excel 97 SR-2(h)).
It is my understanding that there is not supposed to be any differences in the file formats of Excel 97 and Excel 2000. Is this not true?
I reviewed my development notes (I did not have them with me last night). This mainly for the weirdness of the solution (not my design). In this instance, when the workbook was being opened inside of an ActiveX control the Workbook_Open event wasn't firing, but the Auto_Open event was. To insure that I could develop correctly and test in the environment, I simply added the Auto_Open event and called it from the Workbook_Open event, covering both bases.
Posted by Dave Hawley on May 18, 2001 8:38 AM
Re: Well not exactly...
Tuc, they say there is no file format issues between 2000 and 97, but I have already run into an issue with the two vesrions. I developed a program for a client recently (on Excel 2000) that made use of the Workbook_Activate Event among others. As soon as it was Opened or Activated on Excel 97 it gave the dreaded "illegal operation" message. The solution (by a lot of trial and error) ended up being opening the file (with the Shift key held down) and then simply retyping one line of code and saving it as 97 only. I have no idea why this worked.
ActiveX controls do have some issues. possibly the main one being that they Retain Focus once clicked (by Default) you need to set the "TakeFocusOnClick" Property to False. I tend to use the CommandButton from the Forms toolbar if I only need to run a macro.
Dave
OzGrid Business Applications
Posted by Tuc on May 18, 2001 9:26 AM
Sounds good. Follow up question...
Tuc, they say there is no file format issues between 2000 and 97, but I have already run into an issue with the two vesrions. I developed a program for a client recently (on Excel 2000) that made use of the Workbook_Activate Event among others. As soon as it was Opened or Activated on Excel 97 it gave the dreaded "illegal operation" message. The solution (by a lot of trial and error) ended up being opening the file (with the Shift key held down) and then simply retyping one line of code and saving it as 97 only. I have no idea why this worked. ActiveX controls do have some issues. possibly the main one being that they Retain Focus once clicked (by Default) you need to set the "TakeFocusOnClick" Property to False. I tend to use the CommandButton from the Forms toolbar if I only need to run a macro.
Dave,
Yes, I understand. I'll look into the TakeFocusOnClick property.
What other issues come to mind when working with ActiveX controls drawn directly on the worksheet? I am using checkboxes and command buttons to initiate actions (one to change state, and the other to execute a procedure) I'm finding inconsistencies when I copy the worksheet. For example in XL97 if I named the check box to a "meaningful" name, for example chk_Product, when the worksheet (that the control is part of) is copied, the control is renamed back to the "original" control name, in this case CheckBox1. This means that the code that I wrote behind the action of chk_Product_Click is now dead code. In XL2K, the names are preserved, although not all the time. I haven't been able to pin down the exact behaviour yet. I thought it might happen when I copy a worksheet from one workbook to another, but I still have more tests to do on that.
Tuc
