open excel and update pivot

[sumandevadiga]

Hello Water,

No, what i understand we just need to change syntax of recorded macros, so same thing i tried to do, but am getting error. 

 

 

 

 

 

[Juvigy]

Aa i see it now. Check out those 2 lines:

$oExcel.ActiveSheet.PivotTables.Add($test,$test2,"GCI")

With $oExcel.ActiveSheet.PivotTables("PivotTable4").PivotFields("GCI")

First line creates the Pivot. But you name the pivot "CGI". When you try to update the picot, you reference it as "PivotTable4". Change it to "CGI" and it will work.

[sumandevadiga]

Hello Juvigy,

yes, but when trying to add 2nd column am getting error, also there is no data displaying under GCI in pivot.

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open ("C:\AR\Deptwise AR-20.03.2017.xlsx")
$test = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"A1:Y11795",1)
$oExcel.Sheets.Add
$test2 = $oExcel.Range("A1")
$oExcel.ActiveSheet.PivotTables.Add($test,$test2,"GCI")

    With $oExcel.ActiveSheet.PivotTables("GCI").PivotFields("GCI")
        .Orientation = 1
        .Position = 1
    EndWith
     With $oExcel.ActiveSheet.PivotTables("Name").PivotFields("Name")
        .Orientation = 1
        .Position = 2
    EndWith

 

[Juvigy]

Change in this part   .PivotTables( "NAME") to .PivotTables("CGI").

$oExcel.ActiveSheet.PivotTables("Name").PivotFields("Name")

 

Remember - your Pivot Table is called CGI. You need to address it with the correct name !!!

[sumandevadiga]

Hello Juvigy,

Am able to add all header but there is no data showing in this pivot

[Juvigy]

Show me your code and attach the file here.

[sumandevadiga]

Hello Juvigy,

Code and file attached

expandcollapse popup

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open ("C:\AR\Deptwise AR-20.03.2017.xlsx")
$test = $oExcel.ActiveWorkbook.PivotCaches.Create(1,"A1:Y11795",1)
$oExcel.Sheets.Add
$test2 = $oExcel.Range("A1")
$oExcel.ActiveSheet.PivotTables.Add($test,$test2,"GCI")

    With $oExcel.ActiveSheet.PivotTables("GCI").PivotFields("GCI")
        .Orientation = 1
        .Position = 1       
    EndWith
    
    With $oExcel.ActiveSheet.PivotTables("GCI").PivotFields("Name")
        .Orientation = 1
        .Position = 2       
    EndWith
    
    With $oExcel.ActiveSheet.PivotTables("GCI").PivotFields("Credit limit")
        .Orientation = 1
        .Position = 3       
    EndWith
    
    With $oExcel.ActiveSheet.PivotTables("GCI").PivotFields("Balance")
        .Orientation = 1
        .Position = 4       
    EndWith
    
    With $oExcel.ActiveSheet.PivotTables("GCI").PivotFields("30 Days")
        .Orientation = 1
        .Position = 5       
    EndWith
    
    With $oExcel.ActiveSheet.PivotTables("GCI").PivotFields("31-45 Days")
        .Orientation = 1
        .Position = 6       
    EndWith
    
    With $oExcel.ActiveSheet.PivotTables("GCI").PivotFields("46-60 Days")
        .Orientation = 1
        .Position = 7       
    EndWith
    
    With $oExcel.ActiveSheet.PivotTables("GCI").PivotFields("61-90 Days")
        .Orientation = 1
        .Position = 8       
    EndWith
    
    With $oExcel.ActiveSheet.PivotTables("GCI").PivotFields("91-120 Days")
        .Orientation = 1
        .Position = 9       
    EndWith

 

Deptwise AR-20.03.2017.xlsx

[Juvigy]

The code works and creates the pivots and rearranges the fields as per your screenshot. I think you want the fields to be arranged in another way as the current way is useless. Arrange the fields manually in the pivot and show me how you want it to look like. And maybe put more rows as in the file there is only one row now.

[sumandevadiga]

Hello Juvigy,

I have attached manual report, i have changed actual customer name to XXX because don't want to disclose customer name

AI-17.04.2017.xlsx

[sumandevadiga]

Hello Juvigy,

Please help on this

[Juvigy]

$oExcel.ActiveSheet.PivotTables("GCI").AddDataField($oExcel.ActiveSheet.PivotTables("GCI").PivotFields("30 Days"), "Sum of 30 Days", -4157)

Ok , then i see that you have no DATA fields in your CODE , but you have in your 'manual' pivot. So if you need to add them. This is how you add a SUM field. Add all your needed fields and it will be fine.