Can I read a data on pivotcache by vba, not double-click(Showdetail)?

[neosarchizo]

Can I read a data on pivotcache by vba, not double-click(Showdetail)?

I want to read a data on pivotcache directly and fastly.

Regardless of whether its sourcetype is an internal source or an external source.

Assuming that I have a data that have 60000 rows,
Because of Autoformat after showdetail, performance is very bad.

Moreover, if possible, I want to edit a data on pivotcache directly?

Can I do that??

 

[BrianB]

The best way I know is to record a macro - which will give you the ShowDetail code.

 

[neosarchizo]

The best way I know is to record a macro - which will give you the ShowDetail code.

I already know way that you wrote.

If I record macro, just showdetail is wrote.

But, thanks.

If any other way that show detail is not there,
I want to know to disable autoformat after when showdetail print records.

 

[BrianB]

Sorry. I see what you mean. Your code should look more like this.

Sub GETDATA()
    Dim MyItem
    MyItem = "100BB00050"
    Worksheets("Journal PVT").PivotTables(1).PivotSelect MyItem, xlDataOnly
    Selection.ShowDetail = True
End Sub

 

[neosarchizo]

Sorry. I see what you mean. Your code should look more like this.

Sub GETDATA()
    Dim MyItem
    MyItem = "100BB00050"
    Worksheets("Journal PVT").PivotTables(1).PivotSelect MyItem, xlDataOnly
    Selection.ShowDetail = True
End Sub

As U know, if user do showdetail method then Excel print records of pivotcache in worksheet, and also automatically Excel autoformat that records in worksheet right away.
What I want is disabling autoformat^-^

Thanks, have a good day!!

 

[BrianB]

It is not possible to disable pivot table autoformats. I had 15 years of finance reporting and soon realised it was a waste of time trying. I then discovered that the best method was to use pivot tables as an *intermediate step* between data tables and a finished report. My "customers" did not want to see pivot tables anyway, they wanted "top level" data relating to their particular area of business laid out in a more understandable manner.

They did, however, need "drilldown". The opportunity to see the actual records making up suspicious totals. ("Exceptions" - or numbers not fitting into the normal pattern - these are what need to be highlighted). Fast. So a doubleclick on a total in a report can instantly produce a worksheet. Formatting can be applied to this with a macro, but this was rarely needed. The actual numbers were more important than how they looked. I did always add a "Total" row at the bottom to prove the report was correct.

It is very easy to link pivot tables to a report using =VLOOKUP(). So the process is basically - add new data to a table - refresh the pivot table - and the report is automatically updated. I did some experiments and discovered that I saved 90% of the time using earlier methods. (That's it. Taking 1 hour instead of 10). This is at a time at the beginning of a month when there is great pressure to produce reports according to a deadline.

We need to focus on what people really want to see rather than what Excel is doing.

Hope this helps.

 

[neosarchizo]

It is not possible to disable pivot table autoformats. I had 15 years of finance reporting and soon realised it was a waste of time trying. I then discovered that the best method was to use pivot tables as an *intermediate step* between data tables and a finished report. My "customers" did not want to see pivot tables anyway, they wanted "top level" data relating to their particular area of business laid out in a more understandable manner.

They did, however, need "drilldown". The opportunity to see the actual records making up suspicious totals. ("Exceptions" - or numbers not fitting into the normal pattern - these are what need to be highlighted). Fast. So a doubleclick on a total in a report can instantly produce a worksheet. Formatting can be applied to this with a macro, but this was rarely needed. The actual numbers were more important than how they looked. I did always add a "Total" row at the bottom to prove the report was correct.

It is very easy to link pivot tables to a report using =VLOOKUP(). So the process is basically - add new data to a table - refresh the pivot table - and the report is automatically updated. I did some experiments and discovered that I saved 90% of the time using earlier methods. (That's it. Taking 1 hour instead of 10). This is at a time at the beginning of a month when there is great pressure to produce reports according to a deadline.

We need to focus on what people really want to see rather than what Excel is doing.

Hope this helps.

First of all, plz understand my poor english skill.

I'm very very thanks to U.

But, what I want is not for specific customer but special project.

Anyway, thank for u to take a profound interest in me.

God bless U!