Liquidlogic

Refreshing excel

14 posts in this topic

Hello,

i need to check my code for refreshing excel by autoit.

I´ve got excel with external data by MSquery. Data are loading with opening workbook. I´ve got 6 tables with data and 6 pivot tables from them.

I am imaging those pivot tables to special worksheet by getcontdata. But Pivot tables are not refreshing :(

What I need - open excel, refresh all links (MS query links are automated to opening workbook), refresh all PT, refresh image, save, close.

Here is my code:

 

#include <Excel.au3>
#include <PowerPoint.au3>
#include <WinAPIFiles.au3>

; excel refresh

$prog_excel = _Excel_Open() ; open excel

$workbook = _Excel_BookOpen($prog_excel, "S:\Back Office VIP\Projekty\WB OUTBOUND\out.xlsx" , False, True, Default, Default, 3) ; open workbook, update links

$prog_excel.ActiveWorkbook.RefreshAll ; refresh all

_Excel_BookClose ( $workbook , True ) ; Close workbook, save

_excel_close ($prog_excel) ; close excel

 

another part of the code is updating link in ppt and copying ppt to external drive. It works fine.

If you want, I can send excel, but it has 5MB.

Thank you.

Share this post


Link to post
Share on other sites



Hello,

I´ve redesigned my script. All works fine, but I need help with refresh pivot table.

In my mind is workaround, after refreshing and closing, open xlsx again to refresh PT, but it is barbarian :)

#include <Excel.au3>

Local $oExcel = _Excel_Open() ; open excel

Local $oWorkbook = _Excel_BookOpen($oExcel, "S:\Back Office VIP\Projekty\WB OUTBOUND\out.xlsx" ) ; open workbook

$oExcel.Workbooks(1).RefreshAll ; refresh

_Excel_BookClose ( $oWorkbook , True ) ; Close workbook, save

_excel_close ($oExcel) ; close excel

Thank you

Share this post


Link to post
Share on other sites

Untested:

For $oPivottable in $oExcel.ActiveSheet.PivotTables
    $oPivotttable.RefreshDataSourceValues() ; Retrieves the current values from the data source for all edited cells in a PivotTable report that is in writeback mode.
    $oPivotttable.RefreshTable() ; Refreshes the PivotTable report from the source data.
 Next

See: https://msdn.microsoft.com/en-us/library/ff835831%28v=office.14%29.aspx


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

Water, thanks for your reply, I´ll try it. So, my script is working, but can you help me do it absolutely bulletproof?

Sometimes, script end in error in row of refresh excel <$oExcel.Workbooks(1).RefreshAll>. Here is all my code:

#include <Excel.au3>
#include <PowerPoint.au3>
#include <WinAPIFiles.au3>

; excel refresh

Local $oExcel = _Excel_Open() ; open excel

Local $oWorkbook = _Excel_BookOpenEX($oExcel, "S:\Back Office VIP\Projekty\WB OUTBOUND\out.xlsx" ) ; open workbook

$oExcel.Workbooks(1).RefreshAll ; refresh

;$oExcel.Worksheet(1).PivotTables ("KT4" ).PivotCache.Refresh

_Excel_BookClose ( $oWorkbook , True ) ; Close workbook, save

_excel_close ($oExcel) ; close excel



; ppt refresh

$objPPT = _PPT_PowerPointApp() ; open pwpoint

$objPres = _PPT_PresentationOpen($objPPT, "S:\Back Office VIP\Projekty\WB OUTBOUND\wallboard.ppt") ; open ppt

_PPT_UpdateLinks ($objPres) ; update links

_PPT_PresentationSaveAs($objPres, "S:\Back Office VIP\Projekty\WB OUTBOUND\wallboard.ppt") ; save ppt

_PPT_PresentationClose($objPres) ; close ppt

_PPT_PowerPointQuit($objPPT) ; close pwpoint



; ppt copy

FileCopy ( "S:\Back Office VIP\Projekty\WB OUTBOUND\wallboard.ppt", "\\WCZC038BKFM\Prezentace" , $FC_OVERWRITE) ; copy file

Thank you so much.

L.

Share this post


Link to post
Share on other sites

Two questions:

  • Which version of AutoIt do you run?
  • Can you post the error message you get?

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 am running 3.3.14.2

It is sou strange, no error when you offer help :(

Error message:

<$oExcel.Workbooks(1).RefreshAll>

<$oExcel.Workbooks(1).RefreshAll> ^ ERROR

And line 2623

 

Thank you

 

Share this post


Link to post
Share on other sites

You could modify the _Excel_Open line to display alerts:

_Excel_Open(True, True)

 


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

Finally, error is here.

Thank you

IMG_20160223_143201.jpg

Share this post


Link to post
Share on other sites

Either _Excel_Open or _Excel_BookOpenEX fail and do not return a valid object.
Can you please tell us what _Excel_BookOpenEX does as it is not part of the posted code?


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 means, that function cannot find the file?

_Excel_BookOpenEX is yours from this thread:

I´ve got the same problem with calling _Excel_BookOpen

Share this post


Link to post
Share on other sites

You can try with IsObj to check if excel open functions return valid objects. Then if not - try  FileOpenDialog and ask user to select the file manually.

Share this post


Link to post
Share on other sites

Can you insert a MsgBox statement so we see if there is an error with _Excel_Open?

#include <Excel.au3>
#include <PowerPoint.au3>
#include <WinAPIFiles.au3>

; excel refresh

Local $oExcel = _Excel_Open() ; open excel
If @error then MsgBox(0, "Error", "_Excel_Open: @error = " & @error) ; <=== Inserted line
Local $oWorkbook = _Excel_BookOpenEX($oExcel, "S:\Back Office VIP\Projekty\WB OUTBOUND\out.xlsx" ) ; open workbook

$oExcel.Workbooks(1).RefreshAll ; refresh

;$oExcel.Worksheet(1).PivotTables ("KT4" ).PivotCache.Refresh

_Excel_BookClose ( $oWorkbook , True ) ; Close workbook, save

_excel_close ($oExcel) ; close excel



; ppt refresh

$objPPT = _PPT_PowerPointApp() ; open pwpoint

$objPres = _PPT_PresentationOpen($objPPT, "S:\Back Office VIP\Projekty\WB OUTBOUND\wallboard.ppt") ; open ppt

_PPT_UpdateLinks ($objPres) ; update links

_PPT_PresentationSaveAs($objPres, "S:\Back Office VIP\Projekty\WB OUTBOUND\wallboard.ppt") ; save ppt

_PPT_PresentationClose($objPres) ; close ppt

_PPT_PowerPointQuit($objPPT) ; close pwpoint



; ppt copy

FileCopy ( "S:\Back Office VIP\Projekty\WB OUTBOUND\wallboard.ppt", "\\WCZC038BKFM\Prezentace" , $FC_OVERWRITE) ; copy file

 


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

#13 ·  Posted (edited)

Hello,

thank you, we have progress. Error is variation of this:

 

But Old name is _FilterDatabase.

If I delete msgbox function, script is running.

 

M.

stažený soubor.jpg

Edited by Liquidlogic
picture added

Share this post


Link to post
Share on other sites

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