Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Office Version
How do I determine the version of Excel one is using?
I am opening a text file and found out that the code is different for Office 97 and Office XP. So I now need an IF statement If (XLversion = 97) Then End iF If (XLversion = XP) Then End If Lance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Office Version
try:-
ver = Application.Version select case ver case 10 ' code for XP here case 9 case else end select -- Patrick Molloy Microsoft Excel MVP ---------------------------------- wrote in message ... How do I determine the version of Excel one is using? I am opening a text file and found out that the code is different for Office 97 and Office XP. So I now need an IF statement If (XLversion = 97) Then End iF If (XLversion = XP) Then End If Lance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Office Version
Try this
Sub test() If Val(Application.Version) = 8 Then MsgBox "97" Else MsgBox "2000-2003" End If End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl wrote in message ... How do I determine the version of Excel one is using? I am opening a text file and found out that the code is different for Office 97 and Office XP. So I now need an IF statement If (XLversion = 97) Then End iF If (XLversion = XP) Then End If Lance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Office Version
You received information on how to determine the version, but I would
suspect you would be better off using the code for xl97. If you have arguments for a later version and try to compile the code in xl97, you will still get an error. This is more problematic with xl2000 and xl2002 since both are VBA6 and conditional compilation can't identify the difference. -- Regards, Tom Ogilvy wrote in message ... How do I determine the version of Excel one is using? I am opening a text file and found out that the code is different for Office 97 and Office XP. So I now need an IF statement If (XLversion = 97) Then End iF If (XLversion = XP) Then End If Lance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Office Version
I guess I'm out of luck too?
I have found that there are two different strategies needed to disable Excel's built-in menu/toolbar buttons depending upon Excel version. In Excel 2002, the property CommandBars.DisableCustomize must be used to prevent re-enabling the buttons through customization, thanks to changes including the new Toolbar Options feature and loss of the "Well" command bars in xlVBA. CommandBars.DisableCustomize does not exist in earlier versions (Excel 2000) and a different strategy is needed to accomplish the same result. Unfortunately the Excel 2000 VBA compiler barfs when it sees CommandBars.DisableCustomize. Conversely (and perhaps worse), using the code that works in 2000 leaves a gaping back door open in 2002. There appears to be no way to identify the Excel or Office version in conditional compilation directives to prevent this. Or have I missed something? Thanks. Jim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Office Version
I've lost the original post in this thread, but you could check the
application.version to see what version of excel you're running: Option Explicit Sub test() If CDbl(Application.Version) = 10 Then Call SubToDisableCustomize Else 'do your earlier version stuff End If End Sub Then in a module that contains nothing but these lines: Option Explicit Sub SubToDisableCustomize() Application.CommandBars.DisableCustomize = True End Sub The second module won't compile in earlier versions of excel, but it won't be run either--so it doesn't need to. And Peter Beach had a neat idea to use late binding to a similar question: http://groups.google.com/groups?thre...40TK2MSFTNGP12 Sub a() Dim o As Object Set o = Application On Error Resume Next o.CommandBars.DisableAskAQuestionDropdown = True If Err.Number = 0 Then MsgBox "2002" Else MsgBox "not 2002" End If End Sub ===== You would change that to o.commandbars.disablecustomize = true Jim Gross wrote: I guess I'm out of luck too? I have found that there are two different strategies needed to disable Excel's built-in menu/toolbar buttons depending upon Excel version. In Excel 2002, the property CommandBars.DisableCustomize must be used to prevent re-enabling the buttons through customization, thanks to changes including the new Toolbar Options feature and loss of the "Well" command bars in xlVBA. CommandBars.DisableCustomize does not exist in earlier versions (Excel 2000) and a different strategy is needed to accomplish the same result. Unfortunately the Excel 2000 VBA compiler barfs when it sees CommandBars.DisableCustomize. Conversely (and perhaps worse), using the code that works in 2000 leaves a gaping back door open in 2002. There appears to be no way to identify the Excel or Office version in conditional compilation directives to prevent this. Or have I missed something? Thanks. Jim -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Office Version
Thanks for your response, Dave.
I know about Application.Version. Problem is that CommandBars.DisableCustomize doesn't exist in Excel 2000 VBA, but I need it when the workbook is hosted in Office XP. I can force the 2000 compiler to ignore that code if I use conditional compilation with compiler directives: #If...#Then...#Else...#End If (new in Excel 2000.) Otherwise, 2000 VBA throws an error just at the sight of CommandBars.DisableCustomize, before any code in the module even executes. Unfortunately, application.version doesn't work with the compiler directives. The directives work only with hard constants, and there aren't any built-in constants for Excel/Office version that I can find. The solution I have found is somewhat convoluted, involving the use of Add-In methods to load (and later on unload) part of the VBA code from a file or a worksheet into a code module after editing it to set a conditional compilation constant based on application.version. It's not pretty, but it does meet the objective of a single code base and so far it seems to work. Jim Gross -----Original Message----- I've lost the original post in this thread, but you could check the application.version to see what version of excel you're running: Option Explicit Sub test() If CDbl(Application.Version) = 10 Then Call SubToDisableCustomize Else 'do your earlier version stuff End If End Sub Then in a module that contains nothing but these lines: Option Explicit Sub SubToDisableCustomize() Application.CommandBars.DisableCustomize = True End Sub The second module won't compile in earlier versions of excel, but it won't be run either--so it doesn't need to. And Peter Beach had a neat idea to use late binding to a similar question: http://groups.google.com/groups?threadm=% 23zeczIyqCHA.2488%40TK2MSFTNGP12 Sub a() Dim o As Object Set o = Application On Error Resume Next o.CommandBars.DisableAskAQuestionDropdown = True If Err.Number = 0 Then MsgBox "2002" Else MsgBox "not 2002" End If End Sub ===== You would change that to o.commandbars.disablecustomize = true Jim Gross wrote: I guess I'm out of luck too? I have found that there are two different strategies needed to disable Excel's built-in menu/toolbar buttons depending upon Excel version. In Excel 2002, the property CommandBars.DisableCustomize must be used to prevent re-enabling the buttons through customization, thanks to changes including the new Toolbar Options feature and loss of the "Well" command bars in xlVBA. CommandBars.DisableCustomize does not exist in earlier versions (Excel 2000) and a different strategy is needed to accomplish the same result. Unfortunately the Excel 2000 VBA compiler barfs when it sees CommandBars.DisableCustomize. Conversely (and perhaps worse), using the code that works in 2000 leaves a gaping back door open in 2002. There appears to be no way to identify the Excel or Office version in conditional compilation directives to prevent this. Or have I missed something? Thanks. Jim -- Dave Peterson . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Office Version
Jim,
You can use Conditional Compilation combined with CallByName to do what you want. For example, #If VBA6 Then If CInt(Application.Version) 9 Then ' Excel 2002 or 2003 CallByName Application.CommandBars, "DisableCustomize", VbLet, True Else ' Excel 2000 End If #Else ' Excel 97 #End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jim Gross" wrote in message ... Thanks for your response, Dave. I know about Application.Version. Problem is that CommandBars.DisableCustomize doesn't exist in Excel 2000 VBA, but I need it when the workbook is hosted in Office XP. I can force the 2000 compiler to ignore that code if I use conditional compilation with compiler directives: #If...#Then...#Else...#End If (new in Excel 2000.) Otherwise, 2000 VBA throws an error just at the sight of CommandBars.DisableCustomize, before any code in the module even executes. Unfortunately, application.version doesn't work with the compiler directives. The directives work only with hard constants, and there aren't any built-in constants for Excel/Office version that I can find. The solution I have found is somewhat convoluted, involving the use of Add-In methods to load (and later on unload) part of the VBA code from a file or a worksheet into a code module after editing it to set a conditional compilation constant based on application.version. It's not pretty, but it does meet the objective of a single code base and so far it seems to work. Jim Gross |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Currently using '97 version.... Should I buy Office 2007 or 2003?? | Excel Worksheet Functions | |||
Installing Ancient version of Office | Excel Discussion (Misc queries) | |||
Which version of Office should I buy..? | Excel Discussion (Misc queries) | |||
which office version am i running | Excel Discussion (Misc queries) | |||
Pls Help me for install different version office on one computer | Excel Discussion (Misc queries) |