Allowing for Different Versions of Excel in VBA Code

[jeffspringer]

I have a macro that works for the 2011 version of Excel for Mac, but causes a Subscript out of Range error on the 2016 version of Excel.

The error occurs on the following line:

Windows("METRCMJF Reconciliation.xlsm").Activate

After some experimentation, I'm pretty sure that it's because the 2011 version of Excel for Mac requires the extension (".xlsm") but the 2016 version does not. I wish I could just remove the extension, but the macro also needs to work on the PC version of Excel. The PC version of Excel requires the extension.

Due to the differences in file structure between Mac and PC, I've had to test for the operating system with the following code:

    Dim TheOS As String

    TheOS = Application.OperatingSystem
    If Left(TheOS, 7) = "Windows" Then
        Workbooks.Open Filename:=ThisWorkbook.Path & "\RawData\PlantsInventoryReport-1.xls"


    Else
        Workbooks.Open Filename:=ThisWorkbook.Path & ":RawData:PlantsInventoryReport-1.xls"

    End If

So, my first thought was to create the same if/then statement for the line that's causing the error. However, there are 13 different files this macro needs to reference and select throughout the run. So, I thought to simply define variables for each of the files and set the variables at the beginning based upon the operating system.

But then, I thought, "What happens when the next version of Excel comes out and everything breaks again?

So, I'm hoping someone might have a more elegant way of handling this problem.

Thank you!

 

[Dryver14]

Can you not save the excel workbook as xlsm?

 

[jeffspringer]

I can, Dryver14. Unfortunately, if I write: Windows("METRCMJF Reconciliation.xlsm").Activate the code will work for PC and Excel 2011 for Mac, but won't work for Excel 2016 for Mac.

If I write: Windows("METRCMJF Reconciliation").Activate, the code will work for Excel 2016 Mac, but not Excel 2011 for Mac or PC.

 

[V_Malkoti]

Can you try this and see if following works for you?

To activate the file:

For Each w In Application.Workbooks
    If w.Name Like "METRCMJF Reconciliation*" Then w.Activate 
Next w

As for creating file path with right type of separator:

sPath = Replace(ThisWorkbook.Path & "^Folder1^Folder2^FileName.xls", "^", _
            Application.PathSeparator, 1, -1, vbTextCompare)

 

[jeffspringer]

Excellent solution, V_Malkoti. Thank you so much!