PINTO1927

open excel and update pivot

51 posts in this topic

#1 ·  Posted (edited)

 

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



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

Set the name of the Pivot table accordingly.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

This:

Sub Update()
'
' Update Macro
'

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

 

Share this post


Link to post
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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

#7 ·  Posted (edited)

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

 

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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

#11 ·  Posted (edited)

$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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

 

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
ConsoleWrite

 

does not return any result

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

 

Which is?

Share this post


Link to post
Share on other sites

 

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

 

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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

    • anusha
      By anusha
      Hi I have jus started using auto-it . Please correct me if I'm wrong.
      I need to read data from an input in text box and search in excel file and return value in next column of matched cell on GUI.
      I have written below code but i cannot use variable which has data stored. it works only when search string is hard coded.
      Please help out.
       
      Example()
      Func Example()
      Local $GuiMain = GUICreate("EXCEL TEST", 399, 180) ;creates main GUI
      ;~ Local $idOK = GUISetOnEvent($GUI_EVENT_CLOSE, "Close")
      Local $iWidthCell = 70
      Local $idLabel = GUICtrlCreateLabel("PART NUMBER", 10, 30, $iWidthCell,50)
      Local $RUN_1 = GUICtrlCreateButton("OK", 70, 70, 85, 25)
      Local $Input_1 = GUICtrlCreateInput("PART NUMBER", 100, 20, 120, 20)
      Local $sMenutext = GUICtrlRead($Input_1, 1)
      GUISetState(@SW_SHOW, $GuiMain)

          While 1
          $MSG = GUIGetMsg()
          Select
              Case $MSG = $GUI_EVENT_CLOSE
                  Exit
              Case $MSG = $RUN_1
                  Local $oAppl = _Excel_Open()

      Local $sFilePath1 = "D:\Anu_WorkFolder\Components.xlsx"
      Local $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath1, Default, Default, True)
      Local $aResult = _Excel_RangeFind($oWorkbook, $sMenutext , Default, Default, $xlWhole)
    • X_xkijux_x
      By X_xkijux_x
      Ok so im using this program. clarify for school. I would like to have a program that takes every word i type in microsoft word and searches on it in claryfi. is this possible. I would like to have the program not stopping me from typing more after that word. Like when i type a word it auto search it on clarify without making me stop typing and if i want to change i can do that and if im fine that word i can just keep typing and it will search after the next word. 
    • Miliardsto
      By Miliardsto
      Is this possible to make program works like in diagram? There is so much encryption methods and UDFs are they give security? If the $Address will be crypted with some encryption algorithm could be possible to get the value of $Address in easy way? Of course We know its easy too look into autoit code and get value of variable and so We dont want to anyone see $Address value - there are functions Could func1.au3, func2.au3.. be for example func1.php or other type of file? As we know .php files are unnable to see. For example func1 would be read by FileRead() and then Execute() Is this generally possible to do?
      Will it give secure?
      What do you think about? Maybe there is something wrong in this concept or missed.

    • JaredStroeb
      By JaredStroeb
      I need to open an application and send it keys in Session 0.  I have exe's working locally, I can see the application open in session 0 through interactive services, but my send commands do not go to the application.  The following article running-autoit-session-0, directs me to use ControlSend() however my application has no controls that can be identified by AutoIt v3 Window Info.  Get All Windows Controls also returned nothing for the application's handle.  
      What are my options?  
      Can this be done with AutoIt?
      Is there a different "relatively simple" library/tool to use? (I am familiar with C# if there is a viable library there )

       
    • Nareshm
      By Nareshm
      How to Activate Opened Excel Windows Using Class not Tittle, Because Some time opened defferent excel that have different name.
      I Tried with
      Winactivate ("[CLASS:XLMAIN]") but not working