How to create a regular chart based on PT data?

[feipezi]

Hi,

I have a PT but I don't want to do pt chart since it's not as flexible as a regular one. So I copy TableRange1 part of the pt to another location and then create a regular chart based on that data under the pt.

It worked at first but somehow I got a run-time error like :

run-time error '-2147467259 (80004005)';
Method 'SetSourceData' of object _Chart failed

When debug, it spots the statements: SetDataSource...

Then everything is frozen and I have to shut down the application and restart. Why did it work for a while and then failed?

Here is the code:


Sub CopyPartPTData()
Sheets("Sheet2").Activate
Cells(1, "ba").CurrentRegion.Clear
addr = ActiveSheet.PivotTables(1).TableRange1.Offset(1).Address
Range(addr).Copy Cells(1, "ba")
End Sub

Sub UpdateCharts()
Application.ScreenUpdating = False
Dim ChtOb As ChartObject
Dim SourceRng As Range
Sheets("Sheet2").Activate
On Error Resume Next
ActiveSheet.ChartObjects(1).Delete
On Error GoTo 0
kadr = Range(Range("ba1"), Range("ba1").End(xlToRight).End(xlDown)).Address
'kadr = ActiveSheet.PivotTables(1).DataBodyRange.Address
' MsgBox kadr
ActiveSheet.Shapes.AddChart.Select
With ActiveChart
.SetSourceData Source:=Range("Sheet2!" & kadr)
.ChartArea.Select
.ChartType = xlLine
.HasTitle = True
.HasLegend = False
.HasDataTable = True
With .DataTable
.Font.Size = 7
End With
End With

ActiveChart.Parent.RoundedCorners = True
Set ChtOb = ActiveChart.Parent
ChtOb.Height = 320
ChtOb.Width = 1000
ChtOb.Top = 116
ChtOb.Left = 2
Cells(1, 1).Activate
Application.ScreenUpdating = True
End Sub

 

[SkipVought]

Hi,

You know it's next to impossible to figure out from your code what's going on, when there is no data to relate to AND you have a named range that has no association to data...just a lot of unknowns!!!

How about posting your workbook?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

A lot of features of the PT chart can be customised, what do you need to get finally?

combo

 

[feipezi]

Lots of features of the PT chart can be customized? What about the users want to see a trend and they also need to copy the data from the PT? I don't think you can do both with a PT chart. At least I have no idea how. If a PT chart shows trend, the data (field name), month12 to month1 will be presented in a PT as a column; but the data for a regular chart, the 12 months data will be laid out in a row or multiple rows. In another word, on a regular chart, the data that the chart is based on is consistent with what the chart graphs. That's not true with PT charts. Hope I made myself understood.

Could you show me how to attach a workbook? I looked around and still don't know how.

Thanks in advance.

 

[SkipVought]

Could you show me how to attach a workbook? I looked around and still don't know how."

Search for "Attachment" Below the Submit Post window.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[feipezi]

Hi Skip,

I've attached the file. Hope you will have access to it.

Many thanks.

 

[SkipVought]

Sub UpdateCharts()
    Application.ScreenUpdating = False
    Dim ChtOb As ChartObject
    Dim SourceRng As Range
    Sheets("Sheet2").Activate
    On Error Resume Next
    [b]Dim co As ChartObject
    For Each co In ActiveSheet.ChartObjects
        co.Delete
    Next
    ''  ActiveSheet.ChartObjects(1).Delete[/b] [highlight #FCE94F]<<-- DELETE THIS STATEMENT[/highlight]
    On Error GoTo 0

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Looking at your TrendTest workbook, it seems that your source data sheet, TRENDTEST is not normalized, severely limiting the usefulness of your data. All the repeating field names like

P_metrics
C_metrics
M_metrics
S_metrics
P_dllr

...are DATA, which should be listed in a SINGLE column headed something like TestType, while the values in each respective column would be in a SINGLE column headed TestValue. A NORMALIZED test structure like this would gain you much for data analysis, making it much simpler. (If necessary another column could discriminate between test entries 1-12)

Choosing a test set NOW, means selecting a new set of headings.

Choosing a test set with a normalized table, is ONE SINGLE data selection in the Filter area.

ALSO, having a normalized source data set would enable you to bypass the Klunky PT/COPY, and replace with an MS Query of the source data directly to the chart source data.

Just a couple of observations.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[feipezi]

Thanks for the advice, which is definitely worth modifying.

The workbook that I sent you was just small part of the data, which would be accumulating. It will come up to less than a million rows next year. So I cannot afford to append those fields like what you specified. Unless resort to Access.

Would you think the run-time error that I got has something to do with the data layout?

I deleted the line and used the For loop but still got the same run-time error. Did you the same macro and come across any errors?

Thanks again.

 

[combo]

Trend line in pivot chart: change chart type from stacked to any other that allows trend lines, for instance clustered. Unfortunatelly, refreshing data removes existing lines, so they have to be added again.

combo

 

[combo]

Continuing my recent post, with vba you can add trendline every time the user refreshes pivot table. A link to code to get (also pivot) chart underlying data:

http://peltiertech.com/extract-chart-data/

combo

 

[feipezi]

Thanks Combo!

Skip, could you be slightly more specific about "replace with an MS Query of the source data directly to the chart source data"? I'll probably try that. But no idea where to start.

Thanks in advance.

 

[SkipVought]

I deleted the line and used the For loop but still got the same run-time error. Did you the same macro and come across any errors?

I ran the UpdateCharts procedure over 10 times without error in your workbook.

replace with an MS Query of the source data directly to the chart source data

I'll follow up on this later, as you have sever unstated requirements in your PivotTable design. With your data structure, you'ld be better off with a PT IMHO.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[feipezi]

Hi Skip,

Yes, I know. I ran more than 10 times and it was fine until I hit 20 to 30 times. The same thing happened to my colleagues too. It feels like there is bug in the application. If not, how come it works for some time and not some other time? Too much for me to investigate.

Now I dug out something I did a few years ago and now it's OK. It's quite simple. I replaced the 3 PageFields by Form Comboboxes and moved the whole PT away. Of course, I had to set up the dropdown lists as ListRange and LinkCells and so on. And mo more Worksheet_PivotTableUpdate event.

Thanks a lot for your help.

 

[SkipVought]

Form Comboboxes work. You can also use Data > Validation in-cell list drop downs.

Since you don't have a normalized table in Excel, there is no advantage to using MS Query.

With such a large table expected, I'd opt for an external db, like Access and THEN MS Query in Excel.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[feipezi]

Thanks Skip, I agree with you 100%.