Refresh Excel Data Models on schedule

UPD: Excel-based version of Power Refresh (Reports Controller) is here.

I know what you think.

Yet another solution to refresh Excel files with connections and Data Model (aka PowerPivot).

Plenty of them can be found on the Internet. Different programming languages, different level of complexity

C#:

1. https://github.com/alapolloni/ExcelRefresh
2. Excel and PowerPivot refresh suite: https://excelrefresher.codeplex.com/

Perl: http://search.cpan.org/~ctbrown/Win32-Excel-Refresh-0.02/Refresh.pm

Power Shell: https://rpmachado.wordpress.com/2015/07/10/powershell-auto-refresh-excel-powerpivot-data/

VBA: http://velin-georgiev-blog.appspot.com/blog/fully-automated-excel-reporting-by-using-windows-task-scheduler-vba-and-simple-vba-functions-library/

And, of course – Power Update

http://exceleratorbi.com.au/power-update-software/

http://www.powerpivotpro.com/2015/02/introducing-power-update/

Year ago, when I decided to write my own refreshing program, Power Update was already on market, however, I couldn’t get it for myself, due to some reasons. In addition, I wanted to slightly adjust a process of refreshing and saving.

Since there is a lot of “power” around Excel last years, I had no choice and called my script “Power Refresh”.

Shortly about Power Refresh,

1. it is available on GitHub
2. It is developed by usual analyst (grown on Excel), non-professional programmer
3. It consists of mix of Visual Basic Script and VBA. VBS file contains main code.

   VBS embeds VBA into Excel VB project, and actually VBA launches RefreshAll. I went such way to have more control on Excel side during refresh to handle errors, and avoid problem when VBS waits for OLE-application too long then raise exceptions.

4. VBS and VBA are selected due to
   • requirement: user doesn’t need anything but workstation with Windows and MS Office installed (32 or 64 bit) to automate Excel-based reporting
   • fact that any advanced Excel-analyst can understand code and adjust it for own needs
   • User doesn’t need administrator rights (meaningful for corporate world)
   • No need to install any IDE (like Visual Studio), Notepad++ is enough
   • VBS can be scheduled using Windows Task Scheduler
5. It is a bit outdated (at the moment of writing this post)

As any other software, it requires support and development. This is why better to think twice about Power Update. Support and development are done by professional programmers and included into price.

However, not in case of my “temporary” solution.

Now, I hope to find time to touch Power Refresh code again.

Some changes I plan

introduce named arguments and use switches when call VBS

probably, switch for detailed log / short log / no log

Probably, integration with SAP BO Analysis automation

Run macro before / after – as switches

Short listing of ideas below.

‘ /type:

‘                R-eport or T-ransfer

‘                 Report: ThisWorkbook.RefreshAll will be used and then entire file will be saved as separate file

‘                 Transfer: expected sheet Result, which will be saved as a new workbook

‘                  PQ query with load to sheet Result (limited by rows on sheet)

‘

‘ /file_path:

‘

‘ Optional switches

‘ /t_result_folder_path:

‘                If omitted: script uses default path

‘

‘ /add_datetime

‘         script will add datetime to resulting file name. Work for both R and T. Meaningful for data transfer.

‘

‘ /scopes:

‘                 each value will be entered into named range SCOPE. File will be refreshed for that Scope

‘ * use quotes for arguments that contain spaces

‘                 if argument is omitted file will be refreshed

‘                        for T – using default Scope (do not requre SCOPE named range in workbook)

‘                        for R – list of scopes will be collected from Scopes table

‘

‘ /r_result_path:

‘                if argument is omitted file will be saved in the same folder as initial workbook

‘                For T – extension of resulting file is always xlsx

‘                For R – xlsx or xlsm (xlsb -> xlsm) (as some applications cannot consume xlsb)

‘

‘ /debug_mode

‘         Excel application will be visible and ScreenUpdating turned On

‘        always DisplayAlerts = False

‘

‘ /error_email_to:

‘                recipients of emails on errors

‘

‘ /success_email_to:

‘

‘ /log_enabled

‘                 script will write log if argument provided

‘

‘ /log_filepath:

‘                If omitted default path will be used.

‘                 Default path variable –