Excel 2007 - Dynamic sheet naming

[Asif]

Hello,

I am getting runtime error "1004: Application-defined or object-defined
error" with the fourth expression:

Workbooks.Open bestfile_name, UpdateLinks:=0 [no error]
ActiveWorkbook.Activate [no error]
Set bestfile = ActiveWorkbook [no error]
Set bestrange = bestfile.Sheets(best_sheet_name).Range(best_range_name) [
[error 1004]

The variables are defined as follows:

- bestrange as Range
- bestfile As Workbook
- best_sheet_name, best_range_name As String

This expression was perfectly fine in Excel 2003.

I will appreciate any help in this respect.

 

[Tim Williams]

You can re-write the first 3 lines as:

Set bestfile = Workbooks.Open(bestfile_name, UpdateLinks:=0)

As for the 4th line error: either there is no sheet with a name
matching best_sheet_name, or no named range matching best_range_name.

Note also that in XL2007 the worksheet is much larger, which means
that some range names which were fine in previous versions are now a
problem, because they could also represent an actual range address.
Eg: last column in XL03 was "IV" which meant that "IW1" was a valid
name to give a range, whereas in XL07 this represents a range address.

Tim