Macro written on Excel 2010, breaking in 2007; researching the issue but would appreciate a quick response

[pawest]

Hello Excel and VBAers,
I wrote a macro that works great in 2010. A colleague needs to use it and it breaks on his 2007 workbook.

I call a file that's 97-2003 format or ".xls", this works great in 2010, but when I try running the exact same macro on his 2007 excel, it breaks. It seems like it could be a compatibility issue because I can't think of anything else at the moment.

Here's the code that I break on:

Sub ProcessFile()

Dim fileSpec As String, _
    fileDir As String, _
    FileList() As String
    
Dim i As Integer, _
    foundFile As Integer
    
Dim lastRow As Integer, _
    dataInputRow As Integer
    
    
    tDate = Application.Workbooks("Report.xlsm").Worksheets("Main").Range("D5")
        
    ' setting the fileSpec variable
    fileSpec = "L:\ST-" & tDate & ".xls"
                
    fileDir = Dir(fileSpec)
        
        ' Was a file found?
        If fileDir <> "" Then
            foundFile = 1
            ReDim Preserve FileList(1 To foundFile)
            FileList(foundFile) = fileDir
        Else
            MsgBox "There is no file that matches: " & fileSpec
            Exit Sub
        End If
    
        'Loop through the files and process them
        For i = 1 To foundFile
    
            'Open the workbook
            Workbooks.Open Filename:=fileSpec
            
           
            '********  Breaks on the line below  ********
            '********  Breaks on the line below  ********
            '********  Breaks on the line below  ********

                Workbooks("ST-" & tDate).Activate

I appreciate the community's consideration to help on this issue! This is an important project for work and I will continue to research.

 

[Andrew Poulsom]

Maybe you need the file extension in:

Workbooks("ST-" & tDate & ".xls").Activate

That will depend on your Windows setting, but it's always better to include it.

That said, your code has just opened the workbook so there is no need to Activate it (it's already active).

 

[pawest]

Andrew,
Good suggestion. Looks like it was a Windows setting issue (which I'm not familiar with). After putting the .xls, the file macro works.

I choose to activate the file because there are various layers of file opening and closing and for good practice I activate it each time the file is referenced. I am keeping my code consistent because there are other times when it's not the active workbook. I appreciate your feedback because even if there wasn't workbook flip-flopping, I probably would have unnecessarily included it.

Thanks, again!

 

[Andrew Poulsom]

The setting is 'Hide extensions for known file types' on the View tab of the Folder Options dialog in Control Panel. More here:

File Extensions In VBA