Supporting multiple versions of Excel for automati

Question

I have an Access app, developed in Access 2013 in multi-user env, uses Excel automation to export and format an Excel file.

The normal Office/Excel 2013 (15.0) references have been made and all works well on Office 2013 machines. Does not play nicely on 2010 machines.

Using a 2010 machine, I replaced the 15.0 references with 14.0 references, and the app is happy on 2010 and 2013 machines. Upon next edit/update on my 2013 machine the 15.0 references return.

Any suggestions to more conveniently develop/operate in this multi-version environment?

Thanks!

Answer 1

The overall solution to this issue is to use late binding. The downsides to late binding are

• Dim xlApp As Object means that we don't get any IntelliSense for xlApp, and
• related constants like xlEdgeTop are not defined without the associated Reference

These issues can be mitigated by using conditional compilation in the VBA project. For development, add the required Reference to the project and define a conditional compilation argument

which you can use in your code like this

Option Compare Database
Option Explicit

Public Sub WorkWithExcel()
    #If LateBinding Then
        Dim xlApp As Object
        Set xlApp = CreateObject("Excel.Application")
    #Else
        Dim xlApp As Excel.Application
        Set xlApp = New Excel.Application
    #End If

    Debug.Print xlEdgeTop
End Sub

To avoid clutter, I would be inclined to keep the constants in a separate Module like this

Option Compare Database
Option Explicit

#If LateBinding Then
    Public Const xlEdgeTop = 8
#End If

When the code tweaking is complete, remove the Reference, set the LateBinding argument to "True" (LateBinding = -1) and compile the project. Add any constants you've missed (there always seems to be one or two) and when it compiles without the Reference you should be good to deploy.

For the next development session, set LateBinding back to "False" (LateBinding = 0) and add the Reference back in.