Monday, January 16, 2017

Automate reports distribution across the corporate network using Excel VBA

During the reporting process it's vital that not only reports contain the correct data, but also that the distribution of those reports reach the right destinees..... A challenging task if your manual distribution matrix contain a lot of people/folders.  Whereas the right execution sometimes gets little attention, any human errors surely create a lot of fuss.  In such a context, an automated solution can be considered.  An investment in a good, simple but powerful mechanism ensures a quick, stable and error free task execution.
As the finance person's most favorite tool is Excel, you quickly end up writing Excel VBA code to do so.  This blog's posting focusses on file distribution via the corporate network.  Another major  method for distributing information is via email. A later posting will cover the tips and tricks for this solution.

Target audience: finance readers with VBA for Excel experience

There are some advantages in automating the task:
  • it also immediately serves as a documentation too 
  • hand-over of the task can be easily done (even due to an ad-hoc event)
  • error-free process


  • a overview/matrix of (source) files and (target) directories (we assume for simplicity sake that the filenames do not require renaming during the copying)
  • intuitive way for the end-user to alter the setup without the need for any VBA code changes 
  • visual indication in case of any errors encountered during the execution

Steps in general

  • Create a sheet in Excel that lists the files to copy and the directories to copy to. Foresee as many target columns as copies of the file you expect to need.
  • Run a check to see if the specified files and directories exist before kicking off the mechanism
  • Add your VBA to the workbook to copy the source files to the target directories
  • Check for any encountered errors

Step 1: setup a distribution matrix

Excel sheet showing files and directories
Each line on the screenshot shown above shows a single file, potentially going to multiple locations (in this example up to 3 possible copies have been foreseen).
e.g.  (row 2):  the Excel report for Belgium is copied into a folder for the European reporting team and another copy of the same report is put in the folder used by the team analyzing the Gross Margin. For this report the 3th option is not used.
The first column ("Include") is a column with a switch to activate of deactivate a certain line/file.
e.g.  (row 3) if we do not need to (re)copy the file for Germany, we could change the "Y" into a "N" and our code will skip this line from execution. For advanced use you could consider putting formulae that check the name of your running process, current date, reporting set, ..

Step 2: Foresee a graphical mechanism to easily add files and directories to the matrix

As we want to avoid that the end-user constantly needs to copy in paths from his Windows Explorer to his Excel while extending or altering his list, a file dialog to "point and pick" is needed.  A good way to tell the user what can be "done" at a certain sheet in the workbook is to have a message popup when he starts working with a specific worksheet tab.

User clicks on the Sheet tab (On worksheet activate event)

e.g. The above message explains that any double clicks in the columns between (B) and (E) will trigger a dialog to select either a file (B) or a directory (C to E).

**End of Code**

Paste in above VBA code into the worksheet object to see following message box after the double-click:

File Dialog that pops up after double-clicking anywhere in columns B:E
The choosen file is not opened but it's name and full path are written down as text in the cell from where we started our double-click.

Result in the cell after clickin on "Open"

Step 3: Make copies

Then you will have to build in your VBA to loop through the files .
You might find some inspiration in below code.
The copy action is done using following command:
filecopy file1 file2 
See Microsoft site for more info on the command: link

** Code **

**End of Code**

Step 4: Checking files and folders.

When we launch our macro it will return errors when either (1) the source file or the (2) target directory does not exist. We need to add a way to check any of those objects for existence. In case of issues with a certain source file or target directory we are going to color the cell red instead of green. I inserted the full function below.

** Code **

**End of Code**

That's all folks!

No comments:

Post a Comment