Workbook reference out of range in Excel 2010 but works in 2013

[cpu97]

Hi,

I have a macro which runs a routine on a number of files. I wrote it in Excel 2013 and have been using it successfully on that version for close to a year.

However, today I tried to run it on a different computer which has Excel 2010. For some reason when the macro opens a workbook, the code which references the open workbook now causes a Subscript out of Range (Error 9) break.

For example, this works in Excel 2013 without issue but breaks in 2010 (on the Do line), as the program can't seem to find the specified workbook (even though it just opened it):

Current_Name = "ABC"

Do Until Workbooks(Current_Name).Sheets("Table").Cells(Calc_Row, Date_Col).Value = ""
        Calc_Row = Calc_Row + 1
Loop

I tested a few ideas and discovered that adding the file extension to the code does work in Excel 2010.

Workbooks(Current_Name & ".xlsx")

The weird thing is I have been using Excel/VBA for 12 years and have never had to refer to a workbook using its file extension.

Has anyone encountered this before? Any ideas for an easy fix? I'd rather not have to add & ".xlsx" to every workbook reference!

Thank you for your consideration.

 

[Warship]

I tested on Win7 Excel2007 and it did require the file extension as you suggested.
Help in the VBE also verifies the need for the file extension.
How are getting the value for the Current_Name variable?
Perhaps there would be an easier fix when setting Current_Name value?

 

[Peter_SSs]

Has anyone encountered this before?

Yes I have, but it is not related to Excel versions but the user's Windows Explorer settings. In particular, whether they have the "Hide file extensions" setting on or off.

Any ideas for an easy fix? I'd rather not have to add & ".xlsx" to every workbook reference!

Here is one way

Dim wb As Workbook, wbCurr As Workbook
Dim Currnet_Name As String

Current_Name = "ABC"
For Each wb In Workbooks
  If Split(wb.Name, ".")(0) = Current_Name Then
    Set wbCurr = wb
    Exit For
  End If
Next wb
If Not wbCurr Is Nothing Then
  Do Until wbCurr.Sheets("Table").Cells(Calc_Row, Date_Col).Value = ""
          Calc_Row = Calc_Row + 1
  Loop
End If

Some more information here: File Extensions In VBA

 

[cpu97]

Thank you, Peter_SSs and Warship. Turning on hiding file extensions in windows-> folder options as Peter_SSs suggested solved the issue perfectly.

 

[Peter_SSs]

Thank you, Peter_SSs and Warship. Turning on hiding file extensions in windows-> folder options as Peter_SSs suggested solved the issue perfectly.

Glad you got a successful outcome. Thanks for letting us know.

 

[Warship]

Yes I have, but it is not related to Excel versions but the user's Windows Explorer settings. In particular, whether they have the "Hide file extensions" setting on or off.

Never woulda thunk of this.
Learn something new everyday.
Thx Peter!