ivanbond / power-refresh Goto Github PK
View Code? Open in Web Editor NEWSchedule refresh of any Excel file using open-source scheduler & refresher written on VBA
Home Page: https://excel.city/excel-automation/
License: MIT License
Schedule refresh of any Excel file using open-source scheduler & refresher written on VBA
Home Page: https://excel.city/excel-automation/
License: MIT License
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.
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.
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.
Add Send Test Email button on list with SMTP configuration.
Add new parameters - Password for Opening workbook, Password for Saving workbook.
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.
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.
Currently Reports Controller does not write any log to external file. Everything is done through Debug.Print.
Initialize data model on wb opening (if exist)
Currently RC updates tracking file at each cycle - each minute. OneDrive sometimes can't handle such frequency, so need a configurable delay-parameter.
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.
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
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
Idea: two new columns:
Limit Frequency of notifications
Add report ID into notification
Accumulate name of reports that cannot be started
Highlight TERMINATED status
Add support for vsb and bat files - run cscripts for them
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.