Challenging cross-version code (Ribbon)

[Jaafar Tribak]

Suppose we have a workbook with the FileFormat xlExcel8 which is the file format that is fully compatible with excel 97-2003.

Now , let's say the file runs some code to disable the Save commandbar control.

There are two possible scenarios :

1- If the workbook happens to be opened in excel 2003 or earlier then the code to disable the Save control is easy as follows:

Application.CommandBars.FindControl(, ID:=3).Enabled = False

2- If the workbook is however opened in excel 2007 then the code to disable the Save Control is XML based and the above code will obviously not work.

Question :

Is there a way to have some generic code that will detect in advance the current excel version and then run the code to disable the Save control accordingly ?

 

[Sektor]

[COLOR="Blue"]Function[/COLOR] AppVersion() [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    AppVersion = Val(Application.Version)
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Jaafar Tribak]

[COLOR=Blue]Function[/COLOR] AppVersion() [COLOR=Blue]As[/COLOR] [COLOR=Blue]Integer[/COLOR]
    AppVersion = Val(Application.Version)
[COLOR=Blue]End[/COLOR] [COLOR=Blue]Function[/COLOR]

Thanks Sektor.

Maybe I didn't phrase my question properly.

I am wanting to have a generic code that will disable the Save control depending on the current excel version.

ie: If xl 97-2003 then run the one-liner code I posted above - If xl 2007 then run the XML code.

 

[Sektor]

Select Case Val(App.Version)
    Case 12, 14 'Excel 2007/2010
    Case Is < 12 'Excel 2003 and older...
End Select

 

[yytsunamiyy]

Sektor, the trouble is that xl <12 does not support xml and you cant acess xml from vba as far as Im aware so something like:

Select Case Val(App.Version)
    Case 12, 14 'Excel 2007/2010
        'do some xml stuff to control the ribbon appearance
    Case Is < 12 'Excel 2003 and older...
        'do some vba stuff to control the commandbars
End Select

is not going to fly AFAIK. I tend to work around out by releasing versions for xl <12 and xl =>12 seperately.

I then use version check as described by you to disable all functionality and pop up a message box if a xl=>12 version is opened in a xl<12 installation.

eg:

workbook.open

    If Val(Application.Version) < 12 then 'Excel 2003 and older...
        Messagebox ("This file is designed for XL 2007 and higher. The file will now close.",vbokonly)
        Thisworkbook.saved = true
        thisworkbook.close
    end if
End sub

 

[Jaafar Tribak]

Sektor, the trouble is that xl <12 does not support xml and you cant acess xml from vba as far as Im aware so something like:

Select Case Val(App.Version)
    Case 12, 14 'Excel 2007/2010
        'do some xml stuff to control the ribbon appearance
    Case Is < 12 'Excel 2003 and older...
        'do some vba stuff to control the commandbars
End Select

is not going to fly AFAIK. I tend to work around out by releasing versions for xl <12 and xl =>12 seperately.

I then use version check as described by you to disable all functionality and pop up a message box if a xl=>12 version is opened in a xl<12 installation.

eg:

workbook.open

    If Val(Application.Version) < 12 then 'Excel 2003 and older...
        Messagebox ("This file is designed for XL 2007 and higher. The file will now close.",vbokonly)
        Thisworkbook.saved = true
        thisworkbook.close
    end if
End sub

Thanks.

Yes XL<12 doesn't support xml which is the main problem here.

I was thinking to somehow dynamically and temporarly converting the workbook to xlsm and adding the xml file to the workbook at run time - all on the fly. It will be a lot of work but I have this feeling that it could be done.

 

[yytsunamiyy]

I was thinking to somehow dynamically and temporarly converting the workbook to xlsm

I don't think you could do that without saving in-between, which means you'd have to re-save as an <12 version at closing time.

adding the xml file to the workbook at run time.

At this point I'm out. I'm not even sure if you can edit xml-data directly using vba. I'm happy if I manage to get the xml right using an editor at the 3rd try.

It will be a lot of work but I have this feeling that it could be done.

I shall watch this thread closely. :D

 

[Jaafar Tribak]

yytsunamiyy

I am still relatively new to this ribbon stuff so still experimenting. According to MS,the file format for xl 97-2003 should be fully compatible with well xl 97-2003 but the truth is that one cannot code the commandbars when the workbook is opened with excel 2007 which is kind of contradictory and very annoying.

like you said the workbook fileformat must be changed back to xl 97-2003 upon close . If we apply brute force coding , I think, we should be able to update the xml via VBA.

 

[mikerickson]

Have you looked at conditional compilation?
http://msdn.microsoft.com/en-us/library/gg264614.aspx

I use it for Mac/Windows

#If Mac Then
    Rem code for Mac
#Else
    Rem code for windows
#End If

Also about the HTML coding, I don't know the version numbers, but

If Application.Version < keyNumber Then
    Rem this is pre-2007 code, it will be compiled, but not run, in a post-2007 environment
Else
    Rem this is post-2007 code, it will be compiled, but not run, in a pre-2007.
End If

 

[Jaafar Tribak]

Have you looked at conditional compilation?
http://msdn.microsoft.com/en-us/library/gg264614.aspx

I use it for Mac/Windows

#If Mac Then
    Rem code for Mac
#Else
    Rem code for windows
#End If

Thanks Mike but that is not the issue here. I just want to have some generic code for xl 97-2003 compatible workbooks to update the commandbars/ribbon whether the workbook is open with xl 97-2003 or with xl 2007.