Referring to a member of the workbooks collection by name.
 

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

Referring to a member of the workbooks collection by name.
 

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

Referring to a member of the workbooks collection by name.
 

What operating system(s) (and version) are the two computers using?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Andrew"

wrote in message
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

Referring to a member of the workbooks collection by name.
 

Both using WinXP SP2...

Andrew


On May 9, 6:49 pm, "Jim Cone" wrote:
What operating system(s) (and version) are the two computers using?
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"Andrew"

wrote in message
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

Referring to a member of the workbooks collection by name.
 

Andrew,
With WinXP SP2, (XL 2002) I cannot duplicate the problem.
"FileName.xls" works whether file extensions are hidden or not.
"FileName" only works when file extensions are hidden.

Are you sure that the error is not coming from the worksheet name?
Also, a workbook must be saved in order for it to have a file extension.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Andrew"
wrote in message
Both using WinXP SP2...
Andrew


On May 9, 6:49 pm, "Jim Cone"

wrote:
What operating system(s) (and version) are the two computers using?
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware



"Andrew"

wrote in message
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

Referring to a member of the workbooks collection by name.
 

Hi Jim

Thanks for the input. Only difference is that I'm using XL2003 but I
can't imagine that making any odds.

Anyway, as is always the way with these things, I've moved onto other
things, and am now also unable to re-create the situation... :-)

My guess is that you're onto something with the idea that the file may
not have been saved.... (wasn't my file!)

Thanks for the input
Andrew


On May 10, 6:31 pm, "Jim Cone" wrote:
Andrew,
With WinXP SP2, (XL 2002) I cannot duplicate the problem.
"FileName.xls" works whether file extensions are hidden or not.
"FileName" only works when file extensions are hidden.

Are you sure that the error is not coming from the worksheet name?
Also, a workbook must be saved in order for it to have a file extension.
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"Andrew"
wrote in message
Both using WinXP SP2...
Andrew

On May 9, 6:49 pm, "Jim Cone"

wrote:



What operating system(s) (and version) are the two computers using?
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
"Andrew"

wrote in message
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- Hide quoted text -

- Show quoted text -