There's probably a better way of doing this, but a quick and dirty way would
be:

Dim myWorkbook As Workbook

For Each myWorkbook In Workbooks
If Left(myWorkbook.Name, 6) = "MyFile" Then
myWorkbook.Worksheets("Sheet1").Range("A2").Value = "Fred"
Exit For
End If
Next

Hope that helps.

"Andrew" wrote:

Hi

Writing code recently that selected a workbook by name, I hit a
problem. The code looked like this:

Workbooks("MyFile.xls").worksheets("Sheet1").Range ("A2").Value="Fred"

This worked fine on my machine, but not on a second one, returning
(from memory) a "Subscript out of range" error. However, on the second
machine it worked fine when I removed the .xls portion of the file
name.

I could get the other machine to accept the same code as mine, by
choosing its option in My Computer not to hide file extensions for
known file types, or get my machine to work like the second one by
choosing to hide the extensions.

My question, then, is this. How do you write code which will work no
matter what the user has set under their folder view options? Surely I
don't need to be making a call to the API to detect such settings?

Thanks
Andrew