Opening a specific Excel instance in VBA

Question

Hope somebody knows how to do this, i'm none too good with VBA.

I have Excel 2016 (64bit) and Excel 2010 (32bit) installed on my machine. This is because i have some older excel based tools that only function in 2010. One of these tools contains the following code:

Start:

            ThisWorkbook.Activate
            strOutputsFilePath = gStrOutputsPath
            strDMfilename = [DMFilePath]

   '2. Refresh the PowerPivot model with the new data

            'Create a new instance of excel
            Application.StatusBar = "Opening a new instance of MS Excel"

            Set appExcelApp = New Excel.Application


            'Disconnect and Re-connect to the PowerPivot Add-in (in case it has been disconnected)

            For Each comAddin In appExcelApp.COMAddIns

                If comAddin.Description = "PowerPivot for Excel" Then
                        Set comAddinPPVT = comAddin
                End If

            Next

            If Not comAddinPPVT Is Nothing Then

                comAddinPPVT.Connect = False
                comAddinPPVT.Connect = True

            End If

            'Apply the settings for the Excel application

            With appExcelApp
                    .Visible = False
                    .ScreenUpdating = False
                    .DisplayAlerts = False
                    .EnableEvents = False
            End With

What the app is supposed to do is run a new instance of Excel 2010 and open certain files in it. However, after installing 2016, the New Excel.Application command defaults to opening Excel 2016 instead of 2010. This produces an error with the PowerPivot addin because of incompatibility between data models.

Question is: Is there a way to specify in the VBA code that i want an instance of Excel 2010?

Tried to make Excel 2010 default on my system however the code still opens 2016 and errors out :\

Any help is greatly appreciated!

Thanks

Answer 1

There is no easy fix for this. Using two different versions and two different bit-ness varieties of Excel on one computer is not recommended or supported. It's rather unusual, so there is no out of the box fix.

My advice is if you don't want Excel 2016 (64 bit) to interfere with your Excel 2013 (32 bit) stuff, don't install the two versions on the same computer.

Consider using a Virtual Machine (VM) to run one version and keep the other version on the main machine. That will ensure that they don't interfere.

Answer 2

How about using the Shell function?

Private Sub OpenExcelInstance(version as string)
Dim path As String
Dim appInstance As Variant
Select Case version
    Case "Excel 2010" 
         path = "C:\Program Files (x86)\blablabla\Excel.exe" 'path to 32bit 2010 executable
    Case "Excel 2016"
         path = "C:\Program Files\blablabla\Excel.exe" 'path to 64 bit 2016 executable.
    Case default
         Exit sub
End Select

appInstance = Shell(path, 1)

'GetObject function here.
End Sub

However, you can't code against this appInstance - It's an integer returned by the shell command.

It's possible to grab the Application instance from it using GetObject afterwards, however I didn't delve into this, since I ran into the problem documented here and that has a SO-question here (not truely unanswered).