VBA test for finding Excel version?

[9tanstaafl9]

Is there a way in vba to test to see what version of Excel is being used?
My macro would crash if their data would exceed the 65K row limit in Excel 2003. I have a message that pops up if they do that, and it handles the problem just fine, but if they are ALREADY using Excel 2007 (or whatever comes later) I don't want the message to appear.

How do I test?

Jennifer

 

[CodeWizard]

What happened to version 13???? Another MS conspiracy?!?!!?

Nope... their stuff has enough glitches, they just figured they didn't need any extra problems with unlucky numbers.

 

[vicsar]

What happened to version 13???? Another MS conspiracy?!?!!?

I know this is an old post but:

• No MS-DOS version of Excel 1.0 for Windows ever existed: the Windows version originated at the time the Mac version was up to 2.0.
• There is no Excel 6.0, because the Windows 95 version was launched with Word 7. All the Office 95 & Office 4.X products have OLE 2 capacity — moving data automatically from various programs — and Excel 7 would show that it was contemporary with Word 7.
• And finally: Version number 13 was skipped due to superstition (Triskaidekaphobia).

True story.

 

[vicsar]

I know this is an old post but:

• No MS-DOS version of Excel 1.0 for Windows ever existed: the Windows version originated at the time the Mac version was up to 2.0.
• There is no Excel 6.0, because the Windows 95 version was launched with Word 7. All the Office 95 & Office 4.X products have OLE 2 capacity — moving data automatically from various programs — and Excel 7 would show that it was contemporary with Word 7.
• And finally: Version number 13 was skipped due to superstition (Triskaidekaphobia).

True story.

-By the way here is some more information in case you are curious:
Excel Versions

 

[Rick Rothstein]

The real pity about the original question is the OP did not get the information she should have gotten. Her stated reason for wanting to know the version of Excel in use was this... "My macro would crash if their data would exceed the 65K row limit in Excel 2003". There is no need to get the Excel version number in order to write code that protects against this problem... while we don't know the exact test she would have needed to perform for her given application, using Rows.Count to find out how many rows were available in the version of Excel running her macro would surely have been enough.

 

[NigelF]

This thread has been very valuable as I was looking for how to get the Excel version number, but only because I wanted to do a test based on different versions, namely I want to create - that is objWorkbook.SaveAs <filename>,<filetype> - a file that users can access in Excel XP (2002), Excel 2003 and Excel 2007. If I am running 2007 the save-as statement works fine when I use file type 56 (2002 format). But the excel 2002 does not seem to recognise 56 as a valid parameter when I run the code in 2002 - it gives an error and does not save the file. I therefore assume that I need to know which version is being used (hence the value of this thread) and if it is 2002 user file format xlNormal (-4134, is it?) rather than 56? Would that be correct? I need the VBA to run in any of the above versions and creates a file that anyone can open. Thanks for any assistance.

 

[mikepfly2]

How would one go about getting the detailed version number (including latest upgrade?) See highlighted area below. I'm not asking how to get the release (#14) number. I want to get the whole 14.0.7128.5000. I've scoured the web, but no such luck...

I've found, especially with Office 2013, that those without the latest update pack have strange bugs (especially around printing) that are fixed when they have the latest updates.

Thanks.

 

[NigelF]

Thanks for all help with this thread - I eventually found the solution to my problem. The objExcel.version parameter returns 10 for Excel 2002, 11 for 2003 and 12 for 2007. I used this as follows to determine the version being used and setting the correct parameter in the SaveAs to get the format I wanted. I hope it helps someone else. (Note: LF01+LF03 represents the file name in the package we are using)
' check the version of excel being used by this user
' if ver 10 (2002) save the file in Normal format (-4143)
' if ver not 10 (assume 11, 12; 2003 or 2007) save file in 2002 format (56)
If objExcel.version="10.0" Then
objWorkbook.SaveAs LF01+LF03,-4143
Else
objWorkbook.SaveAs LF01+LF03,56
End If

 

[mikepfly2]

Any thoughts on this? I have to assume it's possible to get this value? Do I have to get into registry values? Any ideas are welcome! Thanks

How would one go about getting the detailed version number (including latest upgrade?) See highlighted area below. I'm not asking how to get the release (#14) number. I want to get the whole 14.0.7128.5000. I've scoured the web, but no such luck...

I've found, especially with Office 2013, that those without the latest update pack have strange bugs (especially around printing) that are fixed when they have the latest updates.

Thanks.

 

[Greg Truby]

Any thoughts on this? I have to assume it's possible to get this value? Do I have to get into registry values? Any ideas are welcome! Thanks

Came across this thread here during a search to solve the same problem, i.e. how to get more specific info. Found an answer at StackOverflow. I don't know who this Maxime Porté chap is, but in my estimation he's a stud.

I realize it's been almost three years since mike asked this question, but thought I'd share the solution in case mike or anyone else doesn't come across the solution at stackoverflow.

'——————————————————————————————————————————————————————————————————————————————
'// solution taken from here:
'// https://stackoverflow.com/questions/31718490/finding-ms-office-revision-and-build-version-using-vba

Public Sub TestBuildVersion()
    Dim version As String
    Dim chkref As Object, Major$, MajorUp$, Minor$, MinorUp$

    ' List of references
    For Each chkref In ThisWorkbook.VBProject.References
        version = RetrieveDllVersion(chkref.FullPath)
        Major = RetrievePart(version, 0)
        MajorUp = RetrievePart(version, 1)
        Minor = RetrievePart(version, 2)
        MinorUp = RetrievePart(version, 3)
        MsgBox chkref.Name & " : " & Major & "." & MajorUp & "." & Minor & "." & MinorUp, vbInformation
    Next
End Sub

Private Function RetrieveDllVersion(ByVal dll As String) As String
  Dim fso As Object 'Scripting.FileSystemObject
  Set fso = CreateObject("Scripting.FileSystemObject")
  On Error Resume Next
  RetrieveDllVersion = fso.GetFileVersion(dll)
  If Err.Number <> 0 Then Let RetrieveDllVersion = "00.00.00.00"
End Function

Private Function RetrievePart(ByVal version As String, ByVal pos As Integer) As String
    RetrievePart = Split(version, ".")(pos)
End Function

Oh, and BTW, in case anyone should need them, when I'm testing just the main versions, I just use my own set of enumerated constants:

Public Enum ge_XLVersions
    verExcel5 = 5           '//    released 1993
    verExcel95 = 7          '//    released 1995  (there is no Excel version 6)
    verExcel97              '//  8 released 1997
    verExcel2000            '//  9 released 1999
    verExcel2002            '// 10 released 2001
    verExcel2003            '// 11 released 2003
    verExcel2007            '// 12 released 2007
    verExcel2010 = 14       '//    released 2010  (there is no Excel version 13)
    verExcel2013            '// 15 released 2012
    verExcel2016            '// 16 released 2016
End Enum