Jump to content

open excel and update pivot


Recommended Posts

 

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)

 

Link to comment
Share on other sites

  • Replies 50
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

Then I'm out of ideas :(

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

 

#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)

 

Skysnake

Why is the snake in the sky?

Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

 

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?

Link to comment
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

 

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)

 

Link to comment
Share on other sites

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)

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

 

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)

 

Link to comment
Share on other sites

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.

Skysnake

Why is the snake in the sky?

Link to comment
Share on other sites

  • 10 months later...

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

Link to comment
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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


 

Link to comment
Share on other sites

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

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

 

Link to comment
Share on other sites

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

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

 

 

 

 

Error.PNG

Link to comment
Share on other sites

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

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

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...