How to automate upload of multiple Excel workbooks?
Hi,
I'm currently developping a budgeting model at a client. That client has decided to only use Board at the HQ for the time being. This means that subsidiaries don't have access to it and still use Excel templates to submit their annual budget.
The client wants to aggregate those excel in Board and compare them to the actuals.
My question is then how to set up a process so the client can "easily" upload data from all those excel files containing each multiple sheets, blank lines, blank columns,...
Exctracting each sheet to CSV would be too complicated and time consuming.
Is something feasible throught the excel add-in?
What would you do?
Many thanks!
Maxime
Answers
-
Hi Maxime,
In my opinion the easiest option would be to use Excel with some modern features like PowerQuery / Get & Transform to concatenate all the templates in one file, then convert to CSV and upload.
Some more info on this feature: Introduction to Microsoft Power Query for Excel - Excel
3 -
It sounds like a natural next step is for more users to do their planning in BOARD. Uploads and copy/paste solutions will be fine for the time being, but it sounds like the decision makers need help understanding how BOARD can be used as a collaboration and consolidation tool, rather than just a data entry tool.
5 -
Hi Maxime,
I have been in that situation as well in the past, back then I was running a Board implementation as a Customer of Board.
We ended up developing an Excel Template which was designed for the Board Add-In, it had an input sheet which was formatted according to the requirements of the users and one sheet for the upload that was taking the data from the input sheet but was designed to fit the needs of the Board Add-In. We ended up using VBA a lot more then we had originally expected, mainly for updating the input sheets with data before they were sent to the users. They were then sent back to someone who had access to Board and the Add-In installed. The process worked, but it became a much bigger workload then we had ever expected.
I agree with Bob that it is necessary that the Customer understands the great benefits of Board and what it can do for all of their employees. But as I said above I have been on the Customer site so I understand very well how hard it is to justify the costs of a license for certain users. These users you are talking about might end up using Board 3-4 times a year, because they just have to do their Budget / Forecast entries and are not going to use it for more then that.
Probably the most difficult part about this is to figure out how to update the Excel files for all users, because I assume they do not want to start with an empty file, but expect data from the last Budget / Forecast with comparison to the last year and so on. That problem was the main reason why we ended up using the Bord Add-In and did not look into any other option. No matter if you are using the Board Add-In or not, you will need Macros for this.
When it comes to the Upload to Board, my advice would be either to look into Excel Power Query which Etienne mentions above, never used that so I am not sure how powerful it is. Or get someone who is really good with VBA, my old boss was a VBA wizard and I saw a Macro of his that took around 120 Excel files (all formatted the same way and all saved in the same folder) and transferred the data to an access database, this process took a few minutes and worked beautifully. He took it even further then that later on and uploaded the data straight to a SQL Server. Anyone who is sufficient at VBA will be able to program something like this in a few days, maybe even less then that.
I hoped this helped you a bit, having to deal with a great amount of Excel files like this is always difficult. Which is one of the main reasons to get Board in the first place to replace the Excel chaos, but it is just necessary that everyone gets access to it.
Let me know if you have any questions.
Cheers,
Daniel
4 -
Hi Maxime Lefevre,
I agree with Daniel Zillmann about the challenge garnering support and resources for additional licenses. I understand you’re working with the resources you have available to do the best you can for the team. That is admirable and I wish you all the best. Daniel’s approach sounds good. If I could offer one alternative. It might work well to have one tab in the Excel file with a static name (I.e Upload) and use an SSIS process to watch for new files and import them. Here is another possible approach.
* Mangers fill out their department Excel file
* Managers copy their file to a network share
* A SQL Agent job is scheduled to run every five minutes to run one SSIS process
* The SSIS process cycles through all files in the network share, saving the data to a SQL table
* The last step of the SQL agent job runs a command line call to run the BOARD process for the data reader to load from the SQL table to BOARD
I very much appreciate all the collaboration and discussion I’m seeing in the community. I think this is becoming a valuable resource for us to share knowledge and help one another.
Good luck Maxime Lefevre
3 -
Are the files you need to connect to always the same, just with data being overwritten/added?
If so, then depending on the format of the data in your workbook, using Excel Tables and Range names you can set up a SQL Connection from BOARD to each Excel workbook which you can then read in with a SQL datareader.
The table(s) you have created in the Excel workbook will appear in the RDB list on your datareader. And through the combination of Tables and Range names they will be dynamic, so as new data is added to the excel table it will be picked up by the connection/datareader and read into BOARD.
Then you would just would need to use either formulas or VBA to ensure the data is being appended to the relevant tables in each Excel workbook.
Another option which I have experimented with mixed results (but never implemented as I've had no need) is to use the Office Add-in and VBA code to essentially 'paste' the values into a BOARD layout with 'save/undo' mode enabled.
2 -
Hi,
I use MS SQL to import multiple Excel sheets. You drop the sheets in a folder and use s stored procedure to import them all automatically. Once the data is in SQL it is easy to import it into Board.
1
