ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Worksheet name and Backward compatibility (https://www.excelbanter.com/excel-discussion-misc-queries/760-worksheet-name-backward-compatibility.html)

Rich

Worksheet name and Backward compatibility
 
I'm not sure if MS programmers should be blamed for this problem or VBA
developers . . .

In any case, a VBA programmer developed a program in Excel. It, of course,
uses names of Worksheet tabs. Unfortunately, some of the worksheet tab names
have square brackets in them -- "[" and "]". (i.e., "Worksheet[1]")

Also, unfortunately, our company is upgrading most PC's to Office 2003. It
turns out that Excel 2003 doesn't like square brackets in worksheet names.
When you open a worksheet with square brackets in the tab name with 2003,
Excel gives you a warning, then proceeds to open the worksheet (without
apparently modifying the worksheet names).

However, when the worksheet is saved, it converts brackets to parens -- "("
and ")".

This e-mail boils down to two questions:
(1) Is there a way to prevent Excel 2003 from turning brackets to parens in
worksheet tabs, and
(2) if not, is the only fix doing a search and replace in the VBA code?

Thanks for any information . . .

Rich

P.S. From this problem, I learned to consider the use of indexing when
referring to Worksheets within a VBA program.



David McRitchie

It sure seems like a confusing situation since a workbook
name is included in square brackets. Some people don't
have file extensions visible, I'd certainly avoid doing it.
Similar to naming a worksheet that looks like a cellname,
or trying to set a defined name to something that looks like
a cellname something to keep clear of.

Thanks for the warning:
It sure seems like Excel 2003 is an experiment, and not
for serious use.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Rich" wrote in message ...
I'm not sure if MS programmers should be blamed for this problem or VBA
developers . . .

In any case, a VBA programmer developed a program in Excel. It, of course,
uses names of Worksheet tabs. Unfortunately, some of the worksheet tab names
have square brackets in them -- "[" and "]". (i.e., "Worksheet[1]")

Also, unfortunately, our company is upgrading most PC's to Office 2003. It
turns out that Excel 2003 doesn't like square brackets in worksheet names.
When you open a worksheet with square brackets in the tab name with 2003,
Excel gives you a warning, then proceeds to open the worksheet (without
apparently modifying the worksheet names).

However, when the worksheet is saved, it converts brackets to parens -- "("
and ")".

This e-mail boils down to two questions:
(1) Is there a way to prevent Excel 2003 from turning brackets to parens in
worksheet tabs, and
(2) if not, is the only fix doing a search and replace in the VBA code?

Thanks for any information . . .

Rich

P.S. From this problem, I learned to consider the use of indexing when
referring to Worksheets within a VBA program.





Dave Peterson

If you can get to the code, you can always:

Edit|Find
and search the "current project" for both [ and ].

And you may want to look at using the codename of the worksheet within your
code.

Next time you're in the VBE, hit ctrl-r to see the project explorer.
Expand your project.

Under the "Microsoft Excel Objects", you'll see a list of your worksheets:

Sheet1 (TheNameOnTheTab_1)
Sheet2 (TheNameOnTheTab_2)
....

Then in your code, instead of doing this:

with worksheets("TheNameOnTheTab_1")
.range("a1").clearcontents
end with

you can just use that codename

with Sheet1
.range("a1").clearcontents
end with

(The name within parentheses is the name you can see on the tab when you're in
Excel.)


Rich wrote:

I'm not sure if MS programmers should be blamed for this problem or VBA
developers . . .

In any case, a VBA programmer developed a program in Excel. It, of course,
uses names of Worksheet tabs. Unfortunately, some of the worksheet tab names
have square brackets in them -- "[" and "]". (i.e., "Worksheet[1]")

Also, unfortunately, our company is upgrading most PC's to Office 2003. It
turns out that Excel 2003 doesn't like square brackets in worksheet names.
When you open a worksheet with square brackets in the tab name with 2003,
Excel gives you a warning, then proceeds to open the worksheet (without
apparently modifying the worksheet names).

However, when the worksheet is saved, it converts brackets to parens -- "("
and ")".

This e-mail boils down to two questions:
(1) Is there a way to prevent Excel 2003 from turning brackets to parens in
worksheet tabs, and
(2) if not, is the only fix doing a search and replace in the VBA code?

Thanks for any information . . .

Rich

P.S. From this problem, I learned to consider the use of indexing when
referring to Worksheets within a VBA program.


--

Dave Peterson

Rich

Thanks guys . . . that makes sense . . .


"Dave Peterson" wrote in message
...
If you can get to the code, you can always:

Edit|Find
and search the "current project" for both [ and ].

And you may want to look at using the codename of the worksheet within

your
code.

Next time you're in the VBE, hit ctrl-r to see the project explorer.
Expand your project.

Under the "Microsoft Excel Objects", you'll see a list of your worksheets:

Sheet1 (TheNameOnTheTab_1)
Sheet2 (TheNameOnTheTab_2)
...

Then in your code, instead of doing this:

with worksheets("TheNameOnTheTab_1")
.range("a1").clearcontents
end with

you can just use that codename

with Sheet1
.range("a1").clearcontents
end with

(The name within parentheses is the name you can see on the tab when

you're in
Excel.)


Rich wrote:

I'm not sure if MS programmers should be blamed for this problem or VBA
developers . . .

In any case, a VBA programmer developed a program in Excel. It, of

course,
uses names of Worksheet tabs. Unfortunately, some of the worksheet tab

names
have square brackets in them -- "[" and "]". (i.e., "Worksheet[1]")

Also, unfortunately, our company is upgrading most PC's to Office 2003.

It
turns out that Excel 2003 doesn't like square brackets in worksheet

names.
When you open a worksheet with square brackets in the tab name with

2003,
Excel gives you a warning, then proceeds to open the worksheet (without
apparently modifying the worksheet names).

However, when the worksheet is saved, it converts brackets to parens --

"("
and ")".

This e-mail boils down to two questions:
(1) Is there a way to prevent Excel 2003 from turning brackets to parens

in
worksheet tabs, and
(2) if not, is the only fix doing a search and replace in the VBA code?

Thanks for any information . . .

Rich

P.S. From this problem, I learned to consider the use of indexing when
referring to Worksheets within a VBA program.


--

Dave Peterson





All times are GMT +1. The time now is 09:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com