I recently started at a company as a Automation Developer. My job is to automate periodic reports (in Word) for all of our customers (~50), one for each. Each report is slightly different, so I am going to have develop code for each company. The position is new and there are no automation in place; all reports are compiled by copying data from Excel, pasting, and formatting in Word.
However, the data in all these reports all come from just 2 IT systems. I can set up the system to automatically send me Excel reports for each customers on.
Here is my automation process for generating each report.
- Create a template of the report which are striped away of tables, dates, and statistics.
- Collect all data into one Excel workbook. (Different tables are in different worksheets.)
- Ensure all tables are in a standard format.
- Run a VBA script to compile all tables into a Word doc and populate dates and statistics.
Any recommendations for improving my current process?
What best practices should I follow when using VBA in Excel to automate reports?
How best to handle future changes?
Best way to transfer code amongst different users? And my users are not VBA-savvy; I will have to document how to automate these reports step-by-step.