open excel and update pivot

[PINTO1927]

 

That's right .. but still the table still does not update ..

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

$excel = _Excel_Open(False)
$foglio = "C:\Users\giorgio.pinto\Desktop\RETENTION.xls"
$lavoro = _Excel_BookOpen($excel, $foglio)

For $oQueryTable in $lavoro.ActiveSheet.QueryTables
   $bRefreshed = $oQueryTable.Refresh
   If @error Then
      ConsoleWrite("Query Table '" & $oQueryTable.Name & "' error on refresh: " & @error & @CRLF)
   Else
      ConsoleWrite("Query Table '" & $oQueryTable.Name & "' refreshed: " & $bRefreshed & @CRLF)
   EndIf
Next

$risultato = _Excel_RangeRead($lavoro, Default, $lavoro.ActiveSheet.Usedrange.Columns("A:I"), 1)
local $finale[UBound($risultato)][9]

for $1 = 0 to UBound($risultato) - 1
   $finale[$1][0] = $risultato[$1][0]
   $finale[$1][1] = $risultato[$1][1]
   $finale[$1][2] = $risultato[$1][2]
   $finale[$1][3] = $risultato[$1][3]
   $finale[$1][4] = $risultato[$1][4]
   $finale[$1][5] = $risultato[$1][5]
   $finale[$1][6] = $risultato[$1][6]
   $finale[$1][7] = $risultato[$1][7]
   $finale[$1][8] = $risultato[$1][8]
next
   _Excel_BookClose($lavoro, True)
   _Excel_Close($excel)
   $array = _ArrayDisplay($finale,'TEST',default, 32,default, Default)

 

[water]

Then I'm out of ideas

[PINTO1927]

 

there must be a way ..

[Skysnake]

Exactly the same script, there are now 3 ArrayDisplay pop ups.  Please tell us what happens:

 

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

$excel = _Excel_Open(False)
$foglio = "C:\Users\giorgio.pinto\Desktop\RETENTION.xls"
$lavoro = _Excel_BookOpen($excel, $foglio)

For $oQueryTable in $lavoro.ActiveSheet.QueryTables
   $bRefreshed = $oQueryTable.Refresh
   If @error Then
      ConsoleWrite("Query Table '" & $oQueryTable.Name & "' error on refresh: " & @error & @CRLF)
   Else
      ConsoleWrite("Query Table '" & $oQueryTable.Name & "' refreshed: " & $bRefreshed & @CRLF)
   EndIf
Next

$risultato = _Excel_RangeRead($lavoro, Default, $lavoro.ActiveSheet.Usedrange.Columns("A:I"), 1)
local $finale[UBound($risultato)][9]
_ArrayDisplay($risultato)

for $1 = 0 to UBound($risultato) - 1
   $finale[$1][0] = $risultato[$1][0]
   $finale[$1][1] = $risultato[$1][1]
   $finale[$1][2] = $risultato[$1][2]
   $finale[$1][3] = $risultato[$1][3]
   $finale[$1][4] = $risultato[$1][4]
   $finale[$1][5] = $risultato[$1][5]
   $finale[$1][6] = $risultato[$1][6]
   $finale[$1][7] = $risultato[$1][7]
   $finale[$1][8] = $risultato[$1][8]
next
_ArrayDisplay($finale)

$finale=$risultato
_ArrayDisplay($finale)

   _Excel_BookClose($lavoro, True)
   _Excel_Close($excel)
   $array = _ArrayDisplay($finale,'TEST',default, 32,default, Default)

 

[PINTO1927]

 

all array tables show the same result . not updated .

[PINTO1927]

 

good morning,

I tried to shorten the array table , always leaving the excel file source on the desktop , but it still does not update..

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

$excel = _Excel_Open(False)
$foglio = "C:\Users\giorgio.pinto\Desktop\RETENTION.xls"
$lavoro = _Excel_BookOpen($excel, $foglio, False, False, 3)

For $oQueryTable in $lavoro.ActiveSheet.QueryTables
   $bRefreshed = $oQueryTable.Refresh
   If @error Then
      ConsoleWrite("Query Table '" & $oQueryTable.Name & "' error on refresh: " & @error & @CRLF)
   Else
      ConsoleWrite("Query Table '" & $oQueryTable.Name & "' refreshed: " & $bRefreshed & @CRLF)
   EndIf
Next

$risultato = _Excel_RangeRead($lavoro, Default, "A1:I9")

_ArrayDisplay($risultato)

I also tried to insert 3 in _Excel_BookOpen but still update the content..

_Excel_BookOpen

[PINTO1927]

 

a little more detail : I noticed that , by opening the excel file manually , the table will update as I popped the command. If it is opened by autoit the excel file is opened regularly but the table does not update automatically , but instead should do it .. it's a problem if I use Office 2016?

[water]

You are setting the wrong parameter in _Excel_BookOpen to 3. It should be parameter #7 not #5.

Test with

$excel = _Excel_Open(True, True)

so you get all Excel prompts and alert messages.

[PINTO1927]

 

I tried. no message. no update..

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

$excel = _Excel_Open(True, True)
$foglio = "C:\Users\giorgio.pinto\Desktop\RETENTION.xls"
$lavoro = _Excel_BookOpen($excel, $foglio, Default, Default, 3)

For $oQueryTable in $lavoro.ActiveSheet.QueryTables
   $bRefreshed = $oQueryTable.Refresh
   If @error Then
      ConsoleWrite("Query Table '" & $oQueryTable.Name & "' error on refresh: " & @error & @CRLF)
   Else
      ConsoleWrite("Query Table '" & $oQueryTable.Name & "' refreshed: " & $bRefreshed & @CRLF)
   EndIf
Next

$risultato = _Excel_RangeRead($lavoro, Default, "A1:I9")
_ArrayDisplay($risultato)

 

[water]

Some MsgBoxes added to get more detailed information:

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

$excel = _Excel_Open(True, True)
$foglio = "C:\Users\giorgio.pinto\Desktop\RETENTION.xls"
$lavoro = _Excel_BookOpen($excel, $foglio, Default, Default, 3)
Msgbox(0, "BookOpen", @error & "-" & @extended)
Msgbox(0, "#of QueryTables", $lavoro.ActiveSheet.QueryTables.Count)
For $oQueryTable in $lavoro.ActiveSheet.QueryTables
   $bRefreshed = $oQueryTable.Refresh
   If @error Then
      ConsoleWrite("Query Table '" & $oQueryTable.Name & "' error on refresh: " & @error & @CRLF)
   Else
      ConsoleWrite("Query Table '" & $oQueryTable.Name & "' refreshed: " & $bRefreshed & @CRLF)
   EndIf
Next

$risultato = _Excel_RangeRead($lavoro, Default, "A1:I9")
_ArrayDisplay($risultato)

 

[PINTO1927]

 

I solved the case, but there is a problem . the table is always up to date, by opening the array . But when manually open the original file on the desktop , excel becomes all gray leaves only the top menu . very particular

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

$password = "michele"

$excel = _Excel_Open(False)
$foglio = "C:\Users\giorgio.pinto\Desktop\RETENTION.xls"
$lavoro = _Excel_BookOpen($excel, $foglio, False, False, $password, 3)

$excel.Workbooks(1).RefreshAll

$risultato = _Excel_RangeRead($lavoro, Default, "A1:I9")
_ArrayDisplay($risultato)

_Excel_BookClose($lavoro, True)
_Excel_Close($excel)

 

[Skysnake]

Can you try to read the actual Excel table as opposed the pivot table?  Show the data in the Array?

When we know that works, we can search further on the pivot table.  We need to make sure the scirpt actually works.

[sumandevadiga]

Hello Water,

How are you? hope you are doing fine,

am trying to open excel and create pivot table as per attached format, but  unable to update "GCI" in pivot columns instead its getting update in filter.

$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")
$oExcel.Application.ActiveSheet.PivotTables("GCI").PivotFields("GCI").Orientation = 3

 

Pivot.xlsx

[water]

Never tried Pivot Tables in Excel myself
Make sure to add a COM error handler and to check for errors after each line of code.

[Juvigy]

On 4/3/2017 at 10:01 AM, sumandevadiga said:

Hello Water,

How are you? hope you are doing fine,

am trying to open excel and create pivot table as per attached format, but  unable to update "GCI" in pivot columns instead its getting update in filter.

$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")
$oExcel.Application.ActiveSheet.PivotTables("GCI").PivotFields("GCI").Orientation = 3

 

Pivot.xlsx

Try it like this :

$xlPageField = 3
$xlRowField = 1

$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 = $xlPageField
        .Position = 1
    EndWith

 

[sumandevadiga]

Hello Juvigy, 

Thanks, if there are mutilple columns need to add in row field how orientation and position will work? 

[sumandevadiga]

Hello Juvigy,

Sorry i have replied to wrong thread, pls ignore that and consider this message only

I have recorded the the Macros and applied same in my Autoit script after changing the syntax, but some how this is not working

$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("PivotTable4").PivotFields("GCI")
        .Orientation = 1
        .Position = 1
EndWith

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

expandcollapse popup

ActiveWindow.LargeScroll ToRight:=-1
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R170C24", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Sheet2!R3C1", TableName:="PivotTable4", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("GCI")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Name")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Credit limit"), "Sum of Credit limit", xlSum
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Sum of Credit limit")
        .Caption = "Min of Credit limit"
        .Function = xlMin
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Balance"), "Sum of Balance", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("30 Days"), "Sum of 30 Days", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("31-45 Days"), "Sum of 31-45 Days", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("46-60 Days"), "Sum of 46-60 Days", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("61-90 Days"), "Sum of 61-90 Days", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("91-120 Days"), "Sum of 91-120 Days", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields(">120 Days"), "Sum of >120 Days", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields(">60 Days"), "Sum of >60 Days", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False
    Range("J4").Select
    ActiveSheet.PivotTables("PivotTable4").PivotFields("GCI").AutoSort xlDescending _
        , "Sum of >60 Days", ActiveSheet.PivotTables("PivotTable4").PivotColumnAxis. _
        PivotLines(9), 1
    With ActiveSheet.PivotTables("PivotTable4")
        .InGridDropZones = True
        .DisplayFieldCaptions = False
        .RowAxisLayout xlTabularRow
    End With
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Invoice").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Date").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("File").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("GCI").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Name").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Credit limit").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Balance").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("30 Days").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("31-45 Days").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("46-60 Days").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("61-90 Days").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("91-120 Days").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields(">120 Days").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields(">60 Days").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Days").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Ar Date").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Dept").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Branch").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Foreign Balance"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Foreign Currency"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("HBL").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("EDI Status").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Last EDI Status Date"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Last EDI Status Reason"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("I10").Select
    ActiveWorkbook.Save
End Sub

 

[Juvigy]

What exactly is not working? It works on my PC. Excel 2013 and win7x64

[sumandevadiga]

Hello Juvigy,

I have created below script attached is the error.

$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("PivotTable4").PivotFields("GCI")
        .Orientation = 1
        .Position = 1
EndWith

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

Below complete Code copied from Macros recorder

expandcollapse popup

Sub Pivot1()
'
' Pivot1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    ActiveWindow.LargeScroll ToRight:=-1
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R170C24", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Sheet2!R3C1", TableName:="PivotTable4", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("GCI")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Name")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Credit limit"), "Sum of Credit limit", xlSum
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Sum of Credit limit")
        .Caption = "Min of Credit limit"
        .Function = xlMin
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("Balance"), "Sum of Balance", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("30 Days"), "Sum of 30 Days", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("31-45 Days"), "Sum of 31-45 Days", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("46-60 Days"), "Sum of 46-60 Days", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("61-90 Days"), "Sum of 61-90 Days", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("91-120 Days"), "Sum of 91-120 Days", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields(">120 Days"), "Sum of >120 Days", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields(">60 Days"), "Sum of >60 Days", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False
    Range("J4").Select
    ActiveSheet.PivotTables("PivotTable4").PivotFields("GCI").AutoSort xlDescending _
        , "Sum of >60 Days", ActiveSheet.PivotTables("PivotTable4").PivotColumnAxis. _
        PivotLines(9), 1
    With ActiveSheet.PivotTables("PivotTable4")
        .InGridDropZones = True
        .DisplayFieldCaptions = False
        .RowAxisLayout xlTabularRow
    End With
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Invoice").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Date").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("File").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("GCI").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Name").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Credit limit").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Balance").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("30 Days").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("31-45 Days").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("46-60 Days").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("61-90 Days").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("91-120 Days").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields(">120 Days").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields(">60 Days").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Days").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Ar Date").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Dept").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Branch").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Foreign Balance"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Foreign Currency"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("HBL").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("EDI Status").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Last EDI Status Date"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Last EDI Status Reason"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("I10").Select
    ActiveWorkbook.Save
End Sub

 

 

 

 

[water]

Means you need to shorten the With-statement. Example:

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