Worksheet Code Name question

[Guest]

I'm doing some copying and pasting into a PowerPoint presentation and have a
problem with the following:

Set XLSheet = XLBook.Sheets(sName)

and I get an error on only one value of sName. I have sName dimensioned as
a string. What I want sName to be is the sheet code name, but I think it's
behaving like the actual sheet name. What do I need to change in this code
so that it sees sName as the code name and not the sheetname?

Thanks

 

[acampbell012]

Set XLSheet = XLBook.Sheets(sName) .codename

 

[Don Guillett]

MsgBox Sheets(Sheet1.Name).Range("a1")

 

[Guest]

That's not working either. Any other suggestions?

 

[Guest]

I'm not clear on how this will help me. I'm not using Sheet1.name. I want
sname to be the what you show as Sheet1.name.

 

[Bob Phillips]

Barb,

Try this

sExcelName = xlBook.VBProject.VBComponents(sName).Properties("Name")
Set xlSheet = xlBook.Worksheets(sExcelName)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

How would I dimension sExcelName?

Barb,

Try this

sExcelName = xlBook.VBProject.VBComponents(sName).Properties("Name")
Set xlSheet = xlBook.Worksheets(sExcelName)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Got it to work. Thanks so much!

Barb,

Try this

sExcelName = xlBook.VBProject.VBComponents(sName).Properties("Name")
Set xlSheet = xlBook.Worksheets(sExcelName)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

s for String <g>

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Bob,

I sent this workbook/presentation combination to someone to test drive and
it didn't resolve this line

sExcelName = XLBook.VBProject.VBComponents(sName).Properties("Name")

Any idea why?

Barb

 

[Bob Phillips]

Barb,

can you send it to me?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

If I could I would. It's got proprietary info in it. Any other suggestions?

 

[Bob Phillips]

Can you not take out the confidential stuff?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

A colleague has come up with another way to do this. Maybe he'll post it
here.

 

[Peter T]

Security, Trust access to VB Projects ?

If that's the problem an alternative -

sName = "Sheet2" ' ie codename
For Each ws In ActiveWorkbook.Worksheets
If ws.CodeName = sName Then
sExcelName = ws.Name
Exit For
End If
Next

If Not ws Is Nothing Then
MsgBox ws.Name, , sExcelName
Else
' ??
End If

Perhaps don't need to get sExcelName at all.

Regards,
Peter T