Trouble with 31 character sheet name

[NMatt]

I'm working on a macro that creates a couple of pie charts. It is intended to be used on a successive set of files, all with differing filenames, and each file containing a single sheet that is named the same as the file. However, the sheet name is without the .xls extension, and of course if the filename exceeds 31 characters (and sometime it does), the sheet name is truncated after 31 characters.

Here's the problem; as long as the the sheet name contains 30 characters or less, the macro works great. If it has 31 characters, it blows up with a runtime 5 error on the line where I am specifying the chart location. Here is the relevant code chunk:

<code>
' set range to prepare for chart creation
Dim rData As Range
Set rData = ActiveSheet.Range("C6", [EndCell])


' Create initial chart
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=rData, PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=MySheet
ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _
HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:=True, _
ShowValue:=True, ShowPercentage:=True, ShowBubbleSize:=False
</code>

The problem line is the one that starts with ActiveChart.Location

Further up in the code, I have assigned the active sheet name to a variable MySheet to allow me to accomodate the varying sheet names.
Here are two ideas I had:

1. Find some other way to specify the current (and only!) sheet. I have tried (ActiveSheet.Name) but it gives me a different error.
2. Find some way to automatically limit the sheet name to 30 characters

You experts out there will undoubtedly recognize that the above is a mishmash of macro recording and hand editing, but hey, if it works for sheet names with 30 characters, I can't figure out why it chokes on 31!
Apologies, I must have the wrong syntax for the code tag.

 

[Damon Ostrander]

Hi NMatt,

It chokes beyond 31 because 31 is the maximum allowed tab name in Excel. You haven't provided quite enough information to be able to give you a sure way of solving your problem. It is easy enough to limit the name to 31 characters--all you have to do is use

Name:=Left(MySheet,31)

to truncate the text string to the leftmost 31 characters. But this may cause you problems in that it doesn't guarantee that the tab names will be unique (which Excel requires they be) if you have multiple file names whose only differences are beyond the 31-character limit.

One solution that effectively guarantees unique tab names is to have your code truncate the name to, say, 24 characters, then append an automatically generated 6-digit number after that. Before naming the tab the code could check to see if the name (with appended number) already exists, and if so it could increment the number--continuing this process until it finds a number that doesn't already exist. You could tell which file the sheet was named after by also having the code write the full-length file name to a textbox or other location on the sheet.

I hope this helps.

Damon

 

[Jon Peltier]

Use a different means to create your chart. ChartObjects.Add bypasses the initial chart sheet step and doesn't have to activate the new chart. In parentheses are the position and size parameters of the chart object (Left, Top, Width, Height).

Dim cht As Chart

Set cht = ActiveSheet.ChartObjects.Add(100, 100, 250, 200)
With cht
  .SetSourceData Source:=ActiveSheet.Range("C6", [EndCell]), PlotBy:=xlColumns
  .ChartType = xlPie
  .ApplyDataLabels AutoText:=True, LegendKey:=False, _
      HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:=True, _
      ShowValue:=True, ShowPercentage:=True, ShowBubbleSize:=False 
End With

 

[NMatt]

Thanks for your help!
Note however that the code chokes at exactly 31 characters, (which should be permissible) not at >31 characters. I still have no clue why this would be true, but in this case it is.
In the meanwhile I had concocted a pretty ugly scheme to put the sheet name into a cell, truncate the cell contents at 30 characters, and then rename the sheet using the cell contents. This worked, but obviously, Damon your code is MUCH cleaner to accomplish the same thing.

Jon, thanks for the alternate approach. In fact, you may have recognized I already was using some of your code that you provided in answer to a similar question, which partially worked around the "which sheet is active" problem by performing SetSourceData using the predefined rData variable. In the future, I will probably use the approach you describe.