Jump to content
PINTO1927

open excel and update pivot

Recommended Posts

PINTO1927

 

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

Share this post


Link to post
Share on other sites
water
$lavoro.ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Set the name of the Pivot table accordingly.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
PINTO1927

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

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
PINTO1927

This:

Sub Update()
'
' Update Macro
'

'
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

 

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
PINTO1927

it is just a table structured in sql and loaded into Excel.

If you only want to update the worksheet ? or load the mysql query directly in the script ..

Edited by PINTO1927

Share this post


Link to post
Share on other sites
water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
PINTO1927

 

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

....

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
PINTO1927
$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

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
PINTO1927

 

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)

 

 

Share this post


Link to post
Share on other sites
PINTO1927
ConsoleWrite

 

does not return any result

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
PINTO1927

 

Which is?

Share this post


Link to post
Share on other sites
PINTO1927

 

the content present in the excel spreadsheet has a connection to external data in the spreadsheet has been imported a view created in mysql ... could this be the problem ?

Share this post


Link to post
Share on other sites
PINTO1927

 

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

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
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

  • Similar Content

    • mar3011
      By mar3011
      hello, I want to set in the inputbox field random key from the keyboard to which the function will be assigned, for example F4 function start notepad ALT + A function start notepad for one function. Thank you for any help.  
    • JuanFelipe
      By JuanFelipe
      Cambiar a español Hello friends, I need help with a problem I have, I want to know if there is any way to update a GUI already running, that is I have a GUI of a certain size and with a button I would like to enlarge it:
      example: $ form1 = GUICreate ("", 200,100,0,0)   When you press the button, the gui increases its size, and actuates the new size.
    • Ahmed101
      By Ahmed101
      I have more than 12 workbooks opened together, if i wanted to attach to the last workbook opened it will take more than 1 minute !
      Is there any solution for that ?
    • BogdanNicolescu
      By BogdanNicolescu
      While 1 / / / A whole bunch of codes found in help and here: https://www.autoitscript.com/autoit3/docs/ / / / WEnd OR:

       
      HotKeySet("{ESC}", "Terminate") While 1 MouseClick("Right",674,422) MouseClick("Left",673,447) Sleep(2000) / / / A whole bunch of codes found in help and here: https://www.autoitscript.com/autoit3/docs/ / / / MouseClick("Left",675,339) SLeep(3000) WEnd Func Terminate()     Exit 0 EndFunc  
      Sorry if i should't let this here to be found by newbies like me -.-'
    • santoshM
      By santoshM
      Dear friends i am facing a problem , i have created a window in autoit some time it is moving with the mouse curser even if iam not clicking the window 
×