My 3rd try on this question, hopefully the charm...

There was discussion last week of the advantage of referring to a worksheet
by it's code name, e.g., Sheet1 rather than it's tab name, as in
WorkSheets("mysheet") or by index, as in WorkSheets(1). My question
is how to refer to a sheet in another workbook. I've tried:

Workbooks("mybook").Sheet1 and other such variations, without success.

I can access a sheet by code name in another workbook as a VBComponent e.g.,

Workbooks("Book1").VBProject.VBComponents.Item("Sh eet1")

but then I can't, for example, refer to a range within the component.

Thanks in advance for any help a with this,

Doug

Doug,

Here's one way, but there's no gain really. Better off just to use the Sheet
Name.

Sub test()
Dim strCodeName As String, strSheetName As String

strCodeName = "Sheet1"

With Workbooks("Book2")
strSheetName =
..VBProject.VBComponents(strCodeName).Properties(" Name").Value
MsgBox .Worksheets(strSheetName).Range("A1").Value
End With
End Sub


Rob


"Doug Glancy" wrote in message
...
My 3rd try on this question, hopefully the charm...

There was discussion last week of the advantage of referring to a
worksheet
by it's code name, e.g., Sheet1 rather than it's tab name, as in
WorkSheets("mysheet") or by index, as in WorkSheets(1). My question
is how to refer to a sheet in another workbook. I've tried:

Workbooks("mybook").Sheet1 and other such variations, without success.

I can access a sheet by code name in another workbook as a VBComponent
e.g.,

Workbooks("Book1").VBProject.VBComponents.Item("Sh eet1")

but then I can't, for example, refer to a range within the component.

Thanks in advance for any help a with this,

Doug

If you have xl2002 (or higher) and you have
tools|macro|security|Trusted sources tab|trust access to VBA Project
unchecked, then the first method won't work:

Option Explicit
Sub testme01()

Dim myWks As Worksheet
Set myWks = Nothing
With Workbooks("book1.xls")
On Error Resume Next
Set myWks = .Worksheets(.VBProject.VBComponents.Item("Sheet1") .Name)
If Err.Number < 0 Then
MsgBox "something went wrong"
Err.Clear
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If
On Error GoTo 0
End With

End Sub

But you can loop through the worksheets and find a match:

Sub testme02()

Dim wks As Worksheet
Dim myWks As Worksheet

Set myWks = Nothing
For Each wks In Workbooks("book1.xls").Worksheets
If LCase(wks.CodeName) = "sheet1" Then
Set myWks = wks
Exit For
End If
Next wks

If myWks Is Nothing Then
MsgBox "not found"
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If

End Sub


Doug Glancy wrote:

My 3rd try on this question, hopefully the charm...

There was discussion last week of the advantage of referring to a worksheet
by it's code name, e.g., Sheet1 rather than it's tab name, as in
WorkSheets("mysheet") or by index, as in WorkSheets(1). My question
is how to refer to a sheet in another workbook. I've tried:

Workbooks("mybook").Sheet1 and other such variations, without success.

I can access a sheet by code name in another workbook as a VBComponent e.g.,

Workbooks("Book1").VBProject.VBComponents.Item("Sh eet1")

but then I can't, for example, refer to a range within the component.

Thanks in advance for any help a with this,

Doug

--

Dave Peterson

Thanks Dave,

There I was throwing around the phrase"code name" but never noticed it as a
property!

BTW, I had to modify one line in version 1 to make it work for me.
Otherwise only worked when code name and tab name were the same. I got the
modification below by looking at Rob's answer. I'm not quite sure what the
difference is, but ".Name" doesn't seem to be a allowed property in this
context:

Set myWks =
..Worksheets(.VBProject.VBComponents.Item("Sheet1" ).Properties("Name").Value)

Thanks again,

Doug

"Dave Peterson" wrote in message
...
If you have xl2002 (or higher) and you have
tools|macro|security|Trusted sources tab|trust access to VBA Project
unchecked, then the first method won't work:

Option Explicit
Sub testme01()

Dim myWks As Worksheet
Set myWks = Nothing
With Workbooks("book1.xls")
On Error Resume Next
Set myWks =
..Worksheets(.VBProject.VBComponents.Item("Sheet1" ).Name)
If Err.Number < 0 Then
MsgBox "something went wrong"
Err.Clear
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If
On Error GoTo 0
End With

End Sub

But you can loop through the worksheets and find a match:

Sub testme02()

Dim wks As Worksheet
Dim myWks As Worksheet

Set myWks = Nothing
For Each wks In Workbooks("book1.xls").Worksheets
If LCase(wks.CodeName) = "sheet1" Then
Set myWks = wks
Exit For
End If
Next wks

If myWks Is Nothing Then
MsgBox "not found"
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If

End Sub


Doug Glancy wrote:

My 3rd try on this question, hopefully the charm...

There was discussion last week of the advantage of referring to a
worksheet
by it's code name, e.g., Sheet1 rather than it's tab name, as in
WorkSheets("mysheet") or by index, as in WorkSheets(1). My question
is how to refer to a sheet in another workbook. I've tried:

Workbooks("mybook").Sheet1 and other such variations, without success.

I can access a sheet by code name in another workbook as a VBComponent
e.g.,

Workbooks("Book1").VBProject.VBComponents.Item("Sh eet1")

but then I can't, for example, refer to a range within the component.

Thanks in advance for any help a with this,

Doug

--

Dave Peterson

Rob,

I like it. I was trying to use "Properties" but wasn't getting it right.
Now I understand how it works in this case.

Thanks,

Doug

"Rob van Gelder" wrote in message
...
Doug,

Here's one way, but there's no gain really. Better off just to use the
Sheet
Name.

Sub test()
Dim strCodeName As String, strSheetName As String

strCodeName = "Sheet1"

With Workbooks("Book2")
strSheetName =
.VBProject.VBComponents(strCodeName).Properties("N ame").Value
MsgBox .Worksheets(strSheetName).Range("A1").Value
End With
End Sub


Rob


"Doug Glancy" wrote in message
...
My 3rd try on this question, hopefully the charm...

There was discussion last week of the advantage of referring to a
worksheet
by it's code name, e.g., Sheet1 rather than it's tab name, as in
WorkSheets("mysheet") or by index, as in WorkSheets(1). My question
is how to refer to a sheet in another workbook. I've tried:

Workbooks("mybook").Sheet1 and other such variations, without success.

I can access a sheet by code name in another workbook as a VBComponent
e.g.,

Workbooks("Book1").VBProject.VBComponents.Item("Sh eet1")

but then I can't, for example, refer to a range within the component.

Thanks in advance for any help a with this,

Doug

Glad you got it working.

This did work for me.

Set myWks = .Worksheets(.VBProject.VBComponents.Item("Sheet1") .Name)
but this wouldn't:
Set myWks = .Worksheets(.VBProject.VBComponents.Item("Sheet1") .Value)

Any chance you mixed/matched/inserted/delete .properties() when you were
testing??


Doug Glancy wrote:

Thanks Dave,

There I was throwing around the phrase"code name" but never noticed it as a
property!

BTW, I had to modify one line in version 1 to make it work for me.
Otherwise only worked when code name and tab name were the same. I got the
modification below by looking at Rob's answer. I'm not quite sure what the
difference is, but ".Name" doesn't seem to be a allowed property in this
context:

Set myWks =
.Worksheets(.VBProject.VBComponents.Item("Sheet1") .Properties("Name").Value)

Thanks again,

Doug

"Dave Peterson" wrote in message
...
If you have xl2002 (or higher) and you have
tools|macro|security|Trusted sources tab|trust access to VBA Project
unchecked, then the first method won't work:

Option Explicit
Sub testme01()

Dim myWks As Worksheet
Set myWks = Nothing
With Workbooks("book1.xls")
On Error Resume Next
Set myWks =
.Worksheets(.VBProject.VBComponents.Item("Sheet1") .Name)
If Err.Number < 0 Then
MsgBox "something went wrong"
Err.Clear
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If
On Error GoTo 0
End With

End Sub

But you can loop through the worksheets and find a match:

Sub testme02()

Dim wks As Worksheet
Dim myWks As Worksheet

Set myWks = Nothing
For Each wks In Workbooks("book1.xls").Worksheets
If LCase(wks.CodeName) = "sheet1" Then
Set myWks = wks
Exit For
End If
Next wks

If myWks Is Nothing Then
MsgBox "not found"
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If

End Sub


Doug Glancy wrote:

My 3rd try on this question, hopefully the charm...

There was discussion last week of the advantage of referring to a
worksheet
by it's code name, e.g., Sheet1 rather than it's tab name, as in
WorkSheets("mysheet") or by index, as in WorkSheets(1). My question
is how to refer to a sheet in another workbook. I've tried:

Workbooks("mybook").Sheet1 and other such variations, without success.

I can access a sheet by code name in another workbook as a VBComponent
e.g.,

Workbooks("Book1").VBProject.VBComponents.Item("Sh eet1")

but then I can't, for example, refer to a range within the component.

Thanks in advance for any help a with this,

Doug

--

Dave Peterson


--

Dave Peterson

Dave,

Here's what I observe. In Book1 I changed the tab name of "Sheet1" to
"tester". My goal would then have the code look at the codename Sheet1 and
return the tab name "tester." with these two lines of code:

Debug.Print .VBProject.VBComponents.Item("Sheet1").Name
Debug.Print
..VBProject.VBComponents.Item("Sheet1").Properties ("Name").Value

I get this in the immediate window:

Sheet1
tester

So it looks like the .Name property returns the code name of the
VBComponent, while the "Name" property returns the tab name of the sheet.
But I find this stuff pretty challenging (to say the least!) so I'm not
sure.

What do you think?

Thanks again,

Doug

"Dave Peterson" wrote in message
...
Glad you got it working.

This did work for me.

Set myWks = .Worksheets(.VBProject.VBComponents.Item("Sheet1") .Name)
but this wouldn't:
Set myWks = .Worksheets(.VBProject.VBComponents.Item("Sheet1") .Value)

Any chance you mixed/matched/inserted/delete .properties() when you were
testing??


Doug Glancy wrote:

Thanks Dave,

There I was throwing around the phrase"code name" but never noticed it
as a
property!

BTW, I had to modify one line in version 1 to make it work for me.
Otherwise only worked when code name and tab name were the same. I got
the
modification below by looking at Rob's answer. I'm not quite sure what
the
difference is, but ".Name" doesn't seem to be a allowed property in this
context:

Set myWks =

..Worksheets(.VBProject.VBComponents.Item("Sheet1" ).Properties("Name").Value)

Thanks again,

Doug

"Dave Peterson" wrote in message
...
If you have xl2002 (or higher) and you have
tools|macro|security|Trusted sources tab|trust access to VBA Project
unchecked, then the first method won't work:

Option Explicit
Sub testme01()

Dim myWks As Worksheet
Set myWks = Nothing
With Workbooks("book1.xls")
On Error Resume Next
Set myWks =
.Worksheets(.VBProject.VBComponents.Item("Sheet1") .Name)
If Err.Number < 0 Then
MsgBox "something went wrong"
Err.Clear
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If
On Error GoTo 0
End With

End Sub

But you can loop through the worksheets and find a match:

Sub testme02()

Dim wks As Worksheet
Dim myWks As Worksheet

Set myWks = Nothing
For Each wks In Workbooks("book1.xls").Worksheets
If LCase(wks.CodeName) = "sheet1" Then
Set myWks = wks
Exit For
End If
Next wks

If myWks Is Nothing Then
MsgBox "not found"
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If

End Sub


Doug Glancy wrote:

My 3rd try on this question, hopefully the charm...

There was discussion last week of the advantage of referring to a
worksheet
by it's code name, e.g., Sheet1 rather than it's tab name, as in
WorkSheets("mysheet") or by index, as in WorkSheets(1). My question
is how to refer to a sheet in another workbook. I've tried:

Workbooks("mybook").Sheet1 and other such variations, without
success.

I can access a sheet by code name in another workbook as a
VBComponent
e.g.,

Workbooks("Book1").VBProject.VBComponents.Item("Sh eet1")

but then I can't, for example, refer to a range within the
component.

Thanks in advance for any help a with this,

Doug

--

Dave Peterson


--

Dave Peterson

Oooph.

I get you. I was confused.

Sorry.

Doug Glancy wrote:

Dave,

Here's what I observe. In Book1 I changed the tab name of "Sheet1" to
"tester". My goal would then have the code look at the codename Sheet1 and
return the tab name "tester." with these two lines of code:

Debug.Print .VBProject.VBComponents.Item("Sheet1").Name
Debug.Print
.VBProject.VBComponents.Item("Sheet1").Properties( "Name").Value

I get this in the immediate window:

Sheet1
tester

So it looks like the .Name property returns the code name of the
VBComponent, while the "Name" property returns the tab name of the sheet.
But I find this stuff pretty challenging (to say the least!) so I'm not
sure.

What do you think?

Thanks again,

Doug

"Dave Peterson" wrote in message
...
Glad you got it working.

This did work for me.

Set myWks = .Worksheets(.VBProject.VBComponents.Item("Sheet1") .Name)
but this wouldn't:
Set myWks = .Worksheets(.VBProject.VBComponents.Item("Sheet1") .Value)

Any chance you mixed/matched/inserted/delete .properties() when you were
testing??


Doug Glancy wrote:

Thanks Dave,

There I was throwing around the phrase"code name" but never noticed it
as a
property!

BTW, I had to modify one line in version 1 to make it work for me.
Otherwise only worked when code name and tab name were the same. I got
the
modification below by looking at Rob's answer. I'm not quite sure what
the
difference is, but ".Name" doesn't seem to be a allowed property in this
context:

Set myWks =

.Worksheets(.VBProject.VBComponents.Item("Sheet1") .Properties("Name").Value)

Thanks again,

Doug

"Dave Peterson" wrote in message
...
If you have xl2002 (or higher) and you have
tools|macro|security|Trusted sources tab|trust access to VBA Project
unchecked, then the first method won't work:

Option Explicit
Sub testme01()

Dim myWks As Worksheet
Set myWks = Nothing
With Workbooks("book1.xls")
On Error Resume Next
Set myWks =
.Worksheets(.VBProject.VBComponents.Item("Sheet1") .Name)
If Err.Number < 0 Then
MsgBox "something went wrong"
Err.Clear
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If
On Error GoTo 0
End With

End Sub

But you can loop through the worksheets and find a match:

Sub testme02()

Dim wks As Worksheet
Dim myWks As Worksheet

Set myWks = Nothing
For Each wks In Workbooks("book1.xls").Worksheets
If LCase(wks.CodeName) = "sheet1" Then
Set myWks = wks
Exit For
End If
Next wks

If myWks Is Nothing Then
MsgBox "not found"
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If

End Sub


Doug Glancy wrote:

My 3rd try on this question, hopefully the charm...

There was discussion last week of the advantage of referring to a
worksheet
by it's code name, e.g., Sheet1 rather than it's tab name, as in
WorkSheets("mysheet") or by index, as in WorkSheets(1). My question
is how to refer to a sheet in another workbook. I've tried:

Workbooks("mybook").Sheet1 and other such variations, without
success.

I can access a sheet by code name in another workbook as a
VBComponent
e.g.,

Workbooks("Book1").VBProject.VBComponents.Item("Sh eet1")

but then I can't, for example, refer to a range within the
component.

Thanks in advance for any help a with this,

Doug

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson