Problem Creating Excel Charts w/VBA

[cashr23523]

I am working on a project that consists of importing several CSV files into an Excel workbook, then for each worksheet creating custom charts based off the numerical data in the worksheet. Everything works fine until I encounter a CSV file with a name longer than 31 characters in which I use to name the appropriate worksheet in Excel. After I truncate the name to make it less that 31 characters, add a new worksheet with that truncated name, and then try to add a custom chart on that same page it never works properly. However, if the string name doesn’t need to be truncated it works properly and pastes the chart correctly on the sheet. I don’t remember ever having this problem before but it is the only logical reasoning I can find that is causing this error. The code I am using to create the chart is:

ActiveSheet.Range(ExcelSht.Cells(top, 1), ExcelSht.Cells(bot, varNewLastCol)).Select
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:=tpName
ActiveChart.SetSourceData Source:=Sheets(name).Range(ExcelSht.Cells(top, 1), ExcelSht.Cells(bot, varNewLastCol)), PlotBy:=xlRows

ActiveChart.Location Where:=xlLocationAsObject, name:=name code fails here; name is the same as the worksheet name being passed to the procedure. Works for sheets whose name < 31 caharacters and doesn’t need to be truncated but not for those > 31 whose name has already been truncated and worksheet has been created before calling this line of code.

With ActiveChart
'.HasTitle = False
'.ChartTitle.Characters.Text = UCase(ActiveSheet.name) & " - CPE Per Feature Chart 1"
'.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = varChart1(0)
'.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = varChart1(1)
.Axes(xlValue, xlSecondary).MinimumScale = lmin
.Axes(xlValue, xlSecondary).MaximumScale = lmax
.Axes(xlValue, xlSecondary).TickLabels.NumberFormat = "$#,##0"
.Axes(xlValue, xlPrimary).MinimumScale = rmin
.Axes(xlValue, xlPrimary).MaximumScale = rmax
End With

What happens is the chart is created properly on a different worksheet as an object but never pastes onto the proper worksheet because the code fails at the point mentioned above. Anyone had any similar experiences with this or is there another solution? Any help is greatly appreciated.

 

[xlbo]

Please post your code that is truncating the name plus a few lines above and below so we can see it in context

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cashr23523]

I was using something like this but once I changed 31 to 30 it worked fine.

// If str > 31 then it is passed to this function which returns the truncated string
Public Function truncName(ByVal name As String) As String
name = Mid(name, 1, 31) <-- Had to be changed to 30
truncName = name
End Function

Thanks for your help. I wouldn't have found that if I hadn't tried replying to your comment to explain the problem a little further. I guess no explanation is needed now.

 

[xlbo]

No probs - thought it might be something along those lines...and no - no explanation needed ;-)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question