Cell function
 

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)?

Cell function
 

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

--
HTH

Bob Phillips

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

"Chris" wrote in message
...
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)?

Cell function
 

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

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

"Chris" wrote in message
...
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)?

Cell function
 

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

--
HTH

Bob Phillips

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

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

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

"Chris" wrote in message
...
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)?

Cell function
 

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


"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

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

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

"Chris" wrote in message
...
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)?

Cell function
 

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........

"Chris" wrote:

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)?

Cell function
 

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)

"Chris" wrote in message
...
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........

"Chris" wrote:

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)?

Cell function
 

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.

Chris wrote:

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........

"Chris" wrote:

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)?

--

Dave Peterson