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

pawest

Board Regular
Joined
Jun 27, 2011
Messages
105
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:
Code:
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.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Jun 27, 2011
Messages
105
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top