PINTO1927

open excel and update pivot

32 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-02-03 - Version 1.4.7.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
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-02-03 - Version 1.4.7.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
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-02-03 - Version 1.4.7.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
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-02-03 - Version 1.4.7.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
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-02-03 - Version 1.4.7.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
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-02-03 - Version 1.4.7.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
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-02-03 - Version 1.4.7.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
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-02-03 - Version 1.4.7.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
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-02-03 - Version 1.4.7.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
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

    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning
      I would like to know if I can use the Excel UDF to manipulating a .csv file without having Office installed on the PC I'm going to work...
      I read somewhere that it could be done, but I'm here to ask and be sure of what I remember... 
      I'd like to post another question...
      How can I retrieve the handle of a windows from a PID of an .exe?
      I have my script that does a ShellExecute ( which returns the PID of the .exe ), and then, switching a parameter read from a .ini file, adapt the Window on the screen ( Maximize, Minimize, On Top )...
      I tried, but without having success with this:
       
      #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Outfile_x64=prova.exe #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> #include <WinAPIEx.au3> #include <Array.au3> Local $sFileConfigurazione = @ScriptDir & "\configurazione_exe.ini" If(FileExists($sFileConfigurazione)) Then Local $aSezioniIni = IniReadSection($sFileConfigurazione, "CONFIGURAZIONE_EXE") If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante l'apertura del file: " & @CRLF & $sFileConfigurazione & @CRLF & "Errore: " & @error) Else ; Lancio dell'applicativo indicato nel file di configurazione Local $iPID = ShellExecute($aSezioniIni[1][1]) Local $hWnd If($iPID <> 0) Then Local $aWinList = WinList() For $i = 1 To $aWinList[0][0] If(WinGetProcess($aWinList[$i][1] = $iPID)) Then $hWnd = $aWinList[$i][1] EndIf Next Switch($aSezioniIni[2][1]) Case $aSezioniIni[2][1] = "MIN" WinSetState($hWnd, "", @SW_MINIMIZE) Case $aSezioniIni[2][1] = "MAX" WinSetState($hWnd, "", @SW_MAXIMIZE) Case $aSezioniIni[2][1] = "TOP" WinSetOnTop($hWnd, "", $WINDOWS_ONTOP) EndSwitch EndIf EndIf EndIf It just set on top the .exe I'm launching...
      Thanks
    • Swatcat
      By Swatcat
      Hi All,
      So I need Help.
      I am developing code to handle Authentication Popup For IE. The issue I am facing is that this code runs smoothly on unlock desktop but fails on VM machine and when machine is locked. So could you suggest what should I do to make it work. My automation Tool is a Selenium Based tool and is able to launch the script, but the AutoIT Script is unable to pass the username and password on the authentication popup.
      Below is the code.
      I am passing the values through Command line arguments.
      #include <Array.au3> Local $browser, $BAuthMode, $BWinAuthUserName, $BWinAuthPassword, $BChromeClaimsURL, $BChromePolicyURL, $BIEPolicyURL, $BIEClaimsURL; $browser = $CMDLine[1] $BAuthMode = $CMDLine[2] $BWinAuthUserName = $CMDLine[3] $BWinAuthPassword = $CMDLine[4] $BChromeClaimsURL = "" $BChromePolicyURL = "" $BIEURL = "" ;$BIEClaimsURL = "" If $browser = "chrome" And $BAuthMode = "m" Then If WinExists($BChromeClaimsURL,"") Then WinWaitActive($BChromeClaimsURL) ; set control to the window for proxy authentication Sleep(500) Send("{ESCAPE}") ElseIf WinExists($BChromePolicyURL,"") Then WinWaitActive($BChromePolicyURL) ; set control to the window for proxy authentication Sleep(500) Send("{ESCAPE}") EndIf ElseIf $browser = "ie" And $BAuthMode = "m" Then If WinExists($BIEPolicyURL,"") Then WinExists($BIEPolicyURL) ; set control to the window for proxy authentication Sleep(500) Send("{ESCAPE}") ElseIf WinExists($BIEClaimsURL,"") Then WinWaitActive($BIEClaimsURL) ; set control to the window for proxy authentication Sleep(500) Send("{ESCAPE}") EndIf ElseIf $browser = "ie" And $BAuthMode = "w" Then WinWaitActive($BIEURL,"","20") if(WinExists($BIEURL)) Then Sleep(500) Send($BWinAuthUserName & "{TAB}") ; send username and press TAB Sleep(100) Send($BWinAuthPassword & "{ENTER}") ; send the password and press enter Sleep(100) EndIf ElseIf $browser = "chrome" And $BAuthMode = "w" Then If WinExists($BChromeClaimsURL,"") Then WinWaitActive($BChromeClaimsURL) ; set control to the window for proxy authentication Sleep(500) Send($BWinAuthUserName & "{TAB}") ; send username and press TAB Send($BWinAuthPassword & "{ENTER}") ; send the password and press enter ElseIf WinExists($BChromePolicyURL,"") Then WinWaitActive($BChromePolicyURL) ; set control to the window for proxy authentication Sleep(500) Send($BWinAuthUserName & "{TAB}") ; send username and press TAB Send($BWinAuthPassword & "{ENTER}") ; send the password and press enter EndIf EndIf WinSetState("[ACTIVE]", "", @SW_RESTORE) Sleep(500) WinSetState("[ACTIVE]", "", @SW_MAXIMIZE)  
       
       
       
    • Gowrisankar
      By Gowrisankar
      Dear members of the forum,
      I'm new to AutoIt and I'm having a good time exploring and enjoying the features of AutoIt.
      For one of my tasks, I've to open few particular mails from outlook based on subject of the mails and then print the mails. Can someone please guide me how to do it?
      My question could be very silly for experts. But I'm just a beginner. Kindly advice.
      Thanks and regards,
      Gowrisankar R.
    • Valnurat
      By Valnurat
      I have a
      Global $aResult[0][2] For $i = 0 to UBound($aNames) -1 Local $aUserInfo = _AD_GetObjectsInOU($aNames[$i],"","","displayName,distinguishedName") _ArrayDisplay($aUserInfo) _ArrayConcatenate($aResult, $aUserInfo, 1) _ArrayDisplay($aResult, 'AD ' & UBound($aResult)) Next I do receive empty info that's added to my array.
      How can I delete empty records?
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning
      I'd like to know if someone else here has already tried to use an AutoIt script which work on the same DB ( i.e. SQLite ) on different Computers.
      If yes, please answer here, because I'd like to develop it, and I don't know where to start ( i.e. , I don't know how the script would work if 2 or more users are writing/reading in the same moment... )  
      Thanks!