Jump to content

open excel and update pivot


Recommended Posts

 

hello guys, I'm doing one excel pivot connected to a mysql script to display it in a table array . I need you to open the excel file , the pivot is updated .

 

where do I fix it?

 

Quote

 $excel = _Excel_Open(False)
 $foglio = "*** PATH ***"
 $lavoro = _Excel_BookOpen($excel, $foglio, Default, True, True)
 $risultato = _Excel_RangeRead($lavoro, Default, $lavoro.ActiveSheet.Usedrange.Columns("A:I"), 1)

...

...

 

Edited by PINTO1927
Link to comment
Share on other sites

  • Replies 50
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

$lavoro.ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Set the name of the Pivot table accordingly.

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

Hi water,

 

The name of the pivot And this ?

 

despite the inclusion of the string the array table is not updated with the correct values

$excel = _Excel_Open(False)
                  $foglio = "*** PATH ***"
                  $lavoro = _Excel_BookOpen($excel, $foglio, Default, Default, 3)
                  
                  $lavoro.ActiveSheet.PivotTables("Tabella__192.168.1.206_GAT_RETENTION_Sintetico_Giorgio").PivotCache.Refresh
                  
                  $risultato = _Excel_RangeRead($lavoro, Default, $lavoro.ActiveSheet.Usedrange.Columns("A:I"), 1)

 

Immagine.png

Link to comment
Share on other sites

I started the Excel macro recorder, updated the Pivot table, closed the macro recorder and checked what it had recorded.

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

Unfortunately this only refreshes the selected table. How many tables do you have on this worksheet?

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

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 them all , but The worksheet is still not catch up to the moment of the opening array ..

$excel = _Excel_Open(False)
$foglio = "*** PATH ***"
$lavoro = _Excel_BookOpen($excel, $foglio)
$excel.Application.Calculate
$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,*** TITLE ***,default, 32,default, default)

$excel.Application.Calculate

and

$excel.Worksheets(1).Calculate

....

Link to comment
Share on other sites

This should refresh all pivot tables in the active worksheet of the opened workbook.

For $oPivotTable in $lavoro.ActiveSheet.PivotTables
    $oPivotTable.RefreshTable
Next

 

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

$excel = _Excel_Open(False)
$foglio = "*** PATH ***"
$lavoro = _Excel_BookOpen($excel, $foglio)

For $oPivotTable in $lavoro.ActiveSheet.PivotTables
$oPivotTable.RefreshTable
Next

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

 

Still nothing .. mystery ..

Edited by PINTO1927
Link to comment
Share on other sites

We need to add some error checking:

$excel = _Excel_Open(False)
$foglio = "*** PATH ***"
$lavoro = _Excel_BookOpen($excel, $foglio)

For $oPivotTable in $lavoro.ActiveSheet.PivotTables
    $bRefreshed = $oPivotTable.RefreshTable
    If @error Then 
        ConsoleWrite("Pivot Table '" & $oPivotTable.Name & "' error on refresh: " & @error & @CRLF)
    Else
        ConsoleWrite("Pivot Table '" & $oPivotTable.Name & "' refreshed: " & $bRefreshed & @CRLF)
    EndIf
Next

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

 

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 just noticed from your path specification that the Excel workbook seems to be on a Sharepoint server. The Excel UDF is untested in combination with sharepoint.
Can you copy the workbook to a local drive and run the script with the copy of the workbook?

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

 

It is the file is present in a shared nas .

I tried anyway to copy the file to desktop, changed path in the script, but the array table does not update the file ...

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

For $oPivotTable in $lavoro.ActiveSheet.PivotTables
$bRefreshed = $oPivotTable.RefreshTable
If @error Then
ConsoleWrite("Pivot Table '" & $oPivotTable.Name & "' error on refresh: " & @error & @CRLF)
Else
ConsoleWrite("Pivot Table '" & $oPivotTable.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

Then there are no Pivot tables in the active sheet :(

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

 

excel in the table is also checked the item being open file , the table is updated ..

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

but when I do this: the data reported by the array are not updated ..

Link to comment
Share on other sites

Seems we are not talking about Pivot tables but Query tables. So I think the code should look like:

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

 

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...