Cell function

[Guest]

I'm trying to reference the sheet name of the workbook I'm using.

When I type =Cell("filename") into a cell I get this,

C:\documents and settings\chris\desktop\test.xls

How do I extract just the word test(without the .xls)?

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.xlFAQ0002.html

--
HTH

Bob Phillips

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

 

[Guest]

Bob, there is no "]" in my file path.

See http://www.xldynamic.com/source/xld.xlFAQ0002.html

--
HTH

Bob Phillips

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

 

[Bob Phillips]

There must be, the book name is always within [...]

--
HTH

Bob Phillips

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

Bob, there is no "]" in my file path.

See http://www.xldynamic.com/source/xld.xlFAQ0002.html

--
HTH

Bob Phillips

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

 

[Guest]

I had it working a few hours ago where I saw [ ] surrounding the book name
but for some odd reason when I type cell("filename"), I'm not getting any [
].

This is exactly what I'm getting:

C:\Documents and Settings\Chris\Desktop\Test.XLS

There must be, the book name is always within [...]

--
HTH

Bob Phillips

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

Bob, there is no "]" in my file path.

See http://www.xldynamic.com/source/xld.xlFAQ0002.html

--
HTH

Bob Phillips

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

I'm trying to reference the sheet name of the workbook I'm using.

When I type =Cell("filename") into a cell I get this,

C:\documents and settings\chris\desktop\test.xls

How do I extract just the word test(without the .xls)?

 

[Guest]

Bob I figured out why Excel isn't using brackets when I use
cell("filename")..... If the workbook has only 1 sheet it will not use
brackets and only show you the path and book name. If the workbook has more
than 1 worksheet, it will display the path,book name, and sheet name.

This complicates things because I need to be able to extract the sheet name,
yet all my files only have 1 sheet........

 

[Bob Phillips]

I juts tried that Chris, and a one sheet workbook has all the parts.
Something odd is afoot.

--
HTH

Bob Phillips

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

 

[Dave Peterson]

It's even worse than that.

The name of the sheet has to be the same as the workbook (without the
extension).

Worksheet name = Test
workbook name = Test.xls
means no []'s.

I'd use a couple of extra cells.

This will return that test.xls in your situation.

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"\","^^",LEN(A1)
-LEN(SUBSTITUTE(A1,"\","")))))

then
=left(cellwithformula,len(cellwithformula)-4)

=======
Depending on your filenames and worksheet names, you may want to use a few
cells--one to look for the []'s to see how the name is returned.

ps.

=cell("filename")
will evaluate to the workbook name that was active when excel recalculated.

=cell("filename",a1) will return that workbook/worksheet for the cell with the
formula.