Refreshing excel

[Liquidlogic]

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.

[Liquidlogic]

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

[water]

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

[Liquidlogic]

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:

expandcollapse popup

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

[water]

Two questions:

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

[Liquidlogic]

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

 

[water]

You could modify the _Excel_Open line to display alerts:

_Excel_Open(True, True)

 

[Liquidlogic]

Finally, error is here.

Thank you

[water]

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?

[Liquidlogic]

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

[Juvigy]

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.

[water]

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

expandcollapse popup

#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

 

[Liquidlogic]

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.

Edited March 1, 2016 by Liquidlogic
picture added

[Liquidlogic]