Getting name of worksheet

[Norman Harker]

Hi David!

I tested on Excel 2000 (09.0.6926 SP-3) and I do get the problem.

So it looks like an operating system thing as I'm using Windows XP
Professional 5.1.2600 SP 2 v.2096 Build 2600

Funny that it should hit a Mac OS as well though!

 

[Norman Harker]

Hi David!

Also tested on Excel 97 and Excel 2002.

All showing the same problem.

 

[Peo Sjoblom]

I just tested it on win98 second edition 4.10.2222 A with excel 2002 and it
happens there too.

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Norman Harker]

Hi!

I'll summarize so that others can add and refine the issue.

Summary to date is that problems arise if there is a single worksheet
workbook where that sheet has the same name as the workbook (other
than the .xls).

The problem is one of CELL("filename",A1), returning (eg)
C:\My Files\NewsGroups\Delete2 OK.xls
[note the missing [ ] around the file name and the missing worksheet
name.


In normal situations where the sheet has a different name than the
workbook or where there are more than one sheet the formula would
return (eg)

C:\My Files\NewsGroups\[Delete2 OK.xls]Delete2 OK


This problem is appearing across all versions of Excel 97, 2000, 2002
and 2003.

It is also appearing in Windows XP, MacXL04, and Windows 98SE

The only case where the problem has not been evidenced appears to be
Excel 2000 on Windows 2000.


Severity of the problem must be rated as low. It appears to be the
first time it has been reported and I wouldn't expect many users would
ever have a single sheet workbook with the sheet having the same name
as the file name. We have a formula workaround for returning the file
name and the sheet name in all cases although the formulas are not
very friendly. VBA does not appear to have any problems with these
unusual workbooks.

 

[David McRitchie]

(other than the .xls).

Ok, that did the trick, now it fails the same as everyone else.
dsname: C:\My Files\NewsGroups\Delete2 OK.xls
worksheetname: Delete2 OK

 

[Norman Harker]

Hi David!

That's saved a walk in the rain to the computer centre where they are using
Windows 2000.

We can now report to Microsoft but it is unlikely to rank high on functions
to be patched.

I wouldn't intend to amend replies to those seeking formulas for sheet names
etc unless anyone raises a case where the problem might arise.
--

 

[Syhd]

I wish it did. Returning the above is what I desire. But it returns
instead #VALUE!
Furthermore, your solution also returns #VALUE! on my computer.
If I change the name of the worksheet so that it does not match the name of
the workbook then both your solution and my solution work, except that your
solution omits the name of the workbook in the returned value, which may be
useful in other circumstances.

Hi Jack,

I know I'm years late. I just had the problem and I found the solution to be
SAVING THE DOCUMENT FIRST, then the formula will work (won't return to #VALUE!).

As for mine, I use:

=MID(CELL("filename",A4),FIND("]",CELL("filename",A4))+1,32)

This is in case anyone's still looking for the solution to this problem. It helped with mine.

Hope it helps you.