ivanbond/power-refresh

Schedule refresh of any Excel file using open-source scheduler & refresher written on VBA

power-refresh's Issues

Terminate reports & force start cell doesn't get removed after pressing "Start Processing".

Hi Ivan,

First of all, a huge thank you for creating such a universal and robust solution. Using this together with BOA automation, and the whole package is a godsend. Having said that, sometimes the value in Force Start and Terminate column doesn't update after pressing "Start Processing". Also, the status doesn't change to "In-Process" either.

From what I have observed, this mostly occurs whenever I try to add more rows to the table. This in return opens multiple instances of the target workbook, as the underlying VBA code continues to run the command every 60 seconds. I am running a 64-bit version of excel.

Workbook password for Open / Save

Add new parameters - Password for Opening workbook, Password for Saving workbook.

Add triggers from MS Flow

Think of adding an option to trigger execution of certain tasks/reports from MS Flow.
Sample:
Reports Controller is running on workstation with a gateway installed on it (alternative: onedrive folder, setting in RC: path to folder with triggers)
// folder Triggers in root folder of Power Refresh
RC saves list of tasks into certain path as txt
MS Flow trigger offers dropdown list of tasks, when task is selected, MS Flow creates a "trigger" for RC.
RC scans folder "Triggers" and set "Manual Trigger" flag for corresponding tasks on ControlPanel.

64 Bit Excel

64-Bit Excel has some issues with the Kill Child Prosses VB. this error will either cause Excel to Crash completely or debug out to the code

Add support for vsb and bat files - run cscripts for them

Add support for vsb and bat files - run cscripts for them

Check project with rubberduck vba

Add delay between updates of tracking file

Currently RC updates tracking file at each cycle - each minute. OneDrive sometimes can't handle such frequency, so need a configurable delay-parameter.

Add Send Email button

Add Send Test Email button on list with SMTP configuration.

Add Restart if Terminated option

Idea: two new columns:

1. "Restart if Terminated" as Yes/No flag to tell controller if it needs to restart certain task after termination (whatever reason) (sometimes it is just enough to restart a task).
2. Restarts Limit: 1 by default.
   Support column - Current No of Restarts.
   On each scan check if Status is "TERMINATED", if so - check if "Restart if Terminated" set to Y. If so check "Current No of Restarts" vs "Restarts Limit", if less - add a word "restart" to "Manual Trigger", alternatively, send a notification to RC owner - Number of attempts exceeded.
   After successful completion of task - reset "Current No of Restarts" to 0.

Add support for scheduling by weeks and week working days

Current version supports schedule by "each X days", "Month + calendar/working days of month", but no support for "weekly" scenarios.
E.g. "each first working day of week" or "end of each last working day of week".
Or, "each 1st day of week 10, week 20, week 30" - e.g. during certain project.

Improve Controller's email notifications

Limit Frequency of notifications
Add report ID into notification
Accumulate name of reports that cannot be started
Highlight TERMINATED status

Add vbs for monitoring

Add VBS that monitors message boxes like "Microsoft Excel stopped working", "The last time you opened ...", "File in Use", "Privacy levels" , "Do you want to send more information about the problem", "Sorry, we couldn't find ..." etc.

PowerQuery File Hang

Hello, first of all, thanks so much for a robust solution to exactly what you outlined in your notes.

I'm having issues getting this to refresh a somewhat heavy PowerQuery file. Starting the scheduler successfully launches the refresher, and from there the refresher successfully opens a new instance of Excel and I can see the file begin to load, however it stops responding shortly thereafter. I have no option but to kill the task.

Typically when opening the file, it does take a bit for the data model to load, however based on your instructions, this shouldn't be an issue. Any and all help is highly appreciated. Thank you.

Pivot Table Filters are being reset after refresh

Hi Ivan,

Congratulations for this great job!

Can you help me on this? I have updated/refreshed my workbook witch as two sheets, sheet1 - get's pivotdata from sheet2 witch as Pivot Tables and it's ok, but in the end when I open the file suddenly all filters from pivot tables are missing.

Best regards

Initialize data model on wb opening (if exist)

Initialize data model on wb opening (if exist)

Starter.vbs contains refer to ScriptControl

latest code has into Starter.vbs
Set objScriptEngine = CreateObject("ScriptControl")
objScriptEngine.Language = "JScript"

if I start Startes.vbs receive an error
cannot create object ScriptControl. I see you removed ScriptControl. So, how I have to lauch Starter.vbs?

I use x64 excel

Thank you

Need better log for Reports Controller

Currently Reports Controller does not write any log to external file. Everything is done through Debug.Print.

Control Number of working Excel sessions

Add new parameter to Reports Controller that should limit number of starting Excel sessions.
Expected behavior:
If Reports Controller has many reports in schedule (e.g. 30) it shouldn't run all of them at once. Multiple Excel sessions can ear all memory and none of reports will be done.
Parameter can be set to 10 (+/- according to resources of reporting workstation), then Reports Controller create 10 sessions (taking into consideration parallel execution parameter), and skip creation of next session. Next loop will check if number of sessions is below 10, then start next scheduled report.