Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Different versions of Excel

    At work we are transitioning from office 2007 to office 2013. This transition is taking some time during which we have users who are on different platforms. Within macros>tools>references excel appears to use the Microsoft Outlook 15.0 Object Library for Office 2013, but Microsoft Outlook 12.0 Object Library for Office 2007 users. When an Office 2013 user opens / uses the file excel auto upgrades the reference to Microsoft Outlook 15.0. However there appears to be no mechanism to re-apply the Outlook 12.0 reference if the file is passed to an office 2007 user.

    We use macros that auto send spread sheets to various managers as directed. As I cannot predict which type of user will open the file containing the macros, is there a way of writing a statement within thisworkbook>Private Sub Workbook_Open() that will auto change the reference to the correct version of the outlook object library

    Thank you

    Alex

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Check out this thread.

    http://stackoverflow.com/questions/9...ally-vba-excel

    Coupled with some code to find your excel version you can reference the appropriate library

    Sample
    Code:
    Sub ReturnExcelVersion()
        If Application.Version = "14.0" Then
            MsgBox "You are using Excel 2010."
        ElseIf Application.Version = "12.0" Then
            MsgBox "You are using Excel 2007."
        ElseIf Application.Version = "11.0" Then
            MsgBox "You are using Excel 2003."
        ElseIf Application.Version = "10.0" Then
            MsgBox "You are using Excel 2002."
        ElseIf Application.Version = "9.0" Then
            MsgBox "You are using Excel 2000."
        ElseIf Application.Version = "8.0" Then
            MsgBox "You are using Excel 97."
        ElseIf Application.Version = "7.0" Then
            MsgBox "You are using Excel 95."
        End If
    End Sub

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It is much simpler to change your code to use late binding and dispense with the references altogether, IMO.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Just for kicks,
    Code:
    Option Explicit
    
    Sub ExcelVersionCheck()
    
       Dim zVerTxt As String
       
        Select Case Application.Version
              Case "15.0"
                  zVerTxt = " Excel 2013 Ver 15."
              Case "14.0"
                  zVerTxt = " Excel 2010 Ver 14."
              Case "12.0"
                  zVerTxt = " Excel 2007 Ver 12."
              Case "11.0"
                  zVerTxt = " Excel 2003 Ver 11."
              Case "10.0"
                  zVerTxt = " Excel 2002 Ver 10."
              Case "9.0"
                  zVerTxt = " Excel 2000 Ver 9."
              Case "8.0"
                  zVerTxt = " Excel 97 Ver 8."
              Case "7.0"
                  zVerTxt = " Excel 95 Ver 7."
              Case Else
                  zVerTxt = "an unknown or extinct version of Excel!"
        End Select
        
        MsgBox "You are using: " & zVerTxt, _
               vbInformation + vbOKOnly, _
               "Excel Version Check:"
               
    End Sub  'ExcelVersionCheck
    I like Select vs multiple nested ifs when checking the same variable. It's a little more efficient since the version only has to be retrieved once and a little easier to read & modify. IMHO
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •