Charts piling on one another .Help!!!

[lpatnaik]

Hi,
I am creating a report using excel template. I have to create charts and then put them up on a chart sheet (4 or more charts in one CHART SHEET, not worksheet). The following code creates the chart and them puts them on a chart named as in code. But whenever i open the excel file to view the report, the charts are on the fisrt worksheet of the excel file rather than on the chart sheet. I have tried out a lot on this.
KIndly help!!!!

Set cht = objBook.Charts.Add
Set cht = cht.Location(xlLocationAsObject, "DRE_Chart&quot
With cht
.ChartType = xlPie
.SetSourceData Source:=objSheet.Range(objSheet.Cells(8, 3), objSheet.Cells(10, 5))
.HasTitle = True
.ChartTitle.Text = "DRE for phase " & strPhaseName
.ApplyDataLabels (xlDataLabelsShowLabelAndPercent)
.PlotArea.Border.Weight = xlThin
.PlotArea.Border.LineStyle = xlNone
.PlotArea.Interior.ColorIndex = xlNone
.PlotBy = xlRows
.LocationxlLocationAsObject, "DRE_Chart"
'I tried to set it again like this , still it wont work
End With

 

[vb6novice]

lpatnaik,

Have you tried the last line in your With cht block as

.Location xlLocationAsNewSheet, "DRE_Chart"

 

[lpatnaik]

It unfortunately does not work

 

[vb6novice]

lpatnik,

The problem with inserting charts in a chart sheet is that you must first create the chart sheet, which appears to require adding a chart. The chart you add fills the plot area and is not an object in the sheet in the same way that subsequent charts are. The thing to do is create the chart sheet, then get rid of the chart's own objects which leave's just the plot area. Then you can add new chart objects to the chart sheet.

The following code creates four charts in a new chart sheet called DRE_Chart and sizes and positions them to fit. The source data for the charts came from a worksheet called "Data". You'll need to change that, the data range, and the chart type to whatever is right for your data source.

It works on my spreadsheet.

Sub more_charts()

Charts.Add
With ActiveChart
.Name = "DRE_Chart"
.PlotArea.Select
Selection.ClearFormats
.Axes(xlValue).Select
Selection.Delete
.Legend.Select
Selection.Delete
.Axes(xlValue).MajorGridlines.Select
Selection.Delete
.Axes(xlCategory).Select
Selection.Delete
End With

Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("Data&quot.Range("A1:T25&quot, PlotBy:= _
xlColumns
.Location Where:=xlLocationAsObject, Name:="DRE_Chart"
End With

With ActiveSheet
.Shapes("Chart 1&quot.Left = 0
.Shapes("Chart 1&quot.Top = 0
.Shapes("Chart 1&quot.ScaleWidth 0.75, msoFalse, msoScaleFromTopLeft
.Shapes("Chart 1&quot.ScaleHeight 0.75, msoFalse, msoScaleFromTopLeft
End With

Charts.Add
With ActiveChart
.ChartType = xlLineMarkers
.SetSourceData Source:=Sheets("Data&quot.Range("A1:T25&quot, PlotBy:= _
xlColumns
.Location Where:=xlLocationAsObject, Name:="DRE_Chart"
End With
With ActiveSheet
.Shapes("Chart 2&quot.Left = 0
.Shapes("Chart 2&quot.Top = 250
.Shapes("Chart 2&quot.ScaleWidth 0.75, msoFalse, msoScaleFromTopLeft
.Shapes("Chart 2&quot.ScaleHeight 0.75, msoFalse, msoScaleFromTopLeft
End With

Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("Data&quot.Range("A1:T25&quot, PlotBy:= _
xlColumns
.Location Where:=xlLocationAsObject, Name:="DRE_Chart"
End With
With ActiveSheet
.Shapes("Chart 3&quot.Left = 300
.Shapes("Chart 3&quot.Top = 0
.Shapes("Chart 3&quot.ScaleWidth 0.75, msoFalse, msoScaleFromTopLeft
.Shapes("Chart 3&quot.ScaleHeight 0.75, msoFalse, msoScaleFromTopLeft
End With

Charts.Add
With ActiveChart
.ChartType = xlLineMarkers
.SetSourceData Source:=Sheets("Data&quot.Range("A1:T25&quot, PlotBy:= _
xlColumns
.Location Where:=xlLocationAsObject, Name:="DRE_Chart"
End With
With ActiveSheet
.Shapes("Chart 4&quot.Left = 300
.Shapes("Chart 4&quot.Top = 250
.Shapes("Chart 4&quot.ScaleWidth 0.75, msoFalse, msoScaleFromTopLeft
.Shapes("Chart 4&quot.ScaleHeight 0.75, msoFalse, msoScaleFromTopLeft
End With

End Sub

 

[lpatnaik]

Hi,
First of all i am getting an error "Select method of plotarea failed" when i comment this out, the same type of error occurs for Axes, Legend and Category. After i proceed further, i found the charts piled up again on the first worksheet and not in DRE_Chart.
According to me, its some kind of version problem. I am working in office 2000 and i have added the references:
microsoft excel 9.0 object library
microsoft scripting runtime
microsoft ado 2.7 object librarry.

 

[vb6novice]

lpatnik,

The code I sent you is from Office 97, which may be why it gives you errors. I suggest you record your own macro to do the same thing, and let VBA generate the code for you (which is what I did, then I took out some extraneous code and modified other parts of it to do what I wanted).

Here's how I did it.

Ensure you have a sheet (or sheets) with the data you want charts for.
On the menu bar click 'Tools', 'Macro', 'Record New Macro'.
On the menu bar, click 'Insert', 'Chart', then follow the wizard steps to create the chart in a new chart sheet that you name DRE_Chart (don't worry about what data goes in this first chart because you won't keep this chart).
Individually select the objects which make up the chart (chart area, legend, grid lines, axes) and delete them.
Click the plot area.
On the menu bar, click 'Insert', 'Chart', then follow the Wizard steps to create a new chart as an object in DRE_Chart.
Move the chart to the upper left corner of the plot area.
Size the chart so others will fit.
Repeat the chart creation steps for the other charts.
Stop the macro recording.

Change the code so that it creates the charts the way you want them by adjusting the values it uses for the location and size of the charts. When I recorded the macro, it gave me .Shapes("Chartx&quot.MoveLeft and .MoveTop as a referential position from the place the chart was positioned automatically by Excel. I changed those to .Shapes("Chartx&quot.Left and .Top and changed the values for absolute positioning. You could do the same for .ScaleWidth and .ScaleHeight.

Please give this a try.