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

Required

• 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).

**Code**

	' This macro will give a file open dialog to select a file. The result of the selection is written as a text into the selected cell
	Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
	Application.ScreenUpdating = False
	'parameters
	Set myRangeWatch_F = Range("B:B")
	Set myRangeWatch_D = Range("C:E")
	' watching for new directories to add
	If Not Application.Intersect(Target, myRangeWatch_D) Is Nothing Then
	Set d = Application.FileDialog(msoFileDialogFolderPicker) ' directories dialog
	With d
	.AllowMultiSelect = False
	.Title = "Select a directory to which we will be writing the source file"
	End With
	If Target.Cells.Count = 1 Then
	If d.Show Then
	Target.Value = d.SelectedItems.Item(1)
	Cells.EntireColumn.AutoFit
	End If
	End If
	' activate this line to test 'MsgBox Target.Address & " has been double-clicked"
	' watch for files
	ElseIf Not Application.Intersect(Target, myRangeWatch_F) Is Nothing Then
	Set f = Application.FileDialog(msoFileDialogOpen) ' directories dialog
	With f
	.AllowMultiSelect = False
	.Title = "Select a source file that will be copied"
	End With
	If Target.Cells.Count = 1 Then
	If f.Show Then
	Target.Value = f.SelectedItems.Item(1)
	Cells.EntireColumn.AutoFit
	End If
	End If
	End If
	Cells.EntireColumn.AutoFit
	Application.ScreenUpdating = True
	End Sub

view raw filecopier_sheetactivate.vbs hosted with ❤ by GitHub

**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 **

	' the cell containing the filename = cell
	' the cell containing the directory name = dircell
	' check_existence is a function that will check files and directories for existance (see seperate code sample)
	'...
	If ... And UCase(cell.Offset(0, iYesNo).Value) = "Y" Then 'we check if we need to process this line
	'check for correct existence of source file
	If Check_Existence(cell.Value, "file") = True Then
	'...
	For Each dircell In rng.SpecialCells(xlCellTypeConstants)
	If Check_Existence(dircell.Value, "dir") = True Then ' directory exists
	'...
	FileCopy cell.Value, dircell.Value & "\" & Right(cell.Value, Len(cell.Value) - InStrRev(cell.Value, "\"))
	' the command is Filecopy file1 to file2, what we do for file2 is extracting the filename out of file1
	' so we can add it to the target directory. Both together serve as file2
	'...
	dircell.Interior.Color = RGB(0, 255, 0) 'Make it green
	Else 'Directory does not exist
	dircell.Interior.Color = RGB(255, 0, 0)
	lInvalidDirs = lInvalidDirs + 1 ' invalid directories + 1
	'...
	End If
	Next
	Else ' Source file does not exist
	cell.Interior.Color = RGB(255, 0, 0) 'Make it red
	lInvalidFiles = lInvalidFiles + 1
	End If
	End If
	Next

view raw filecopier_copyfiles.vbs hosted with ❤ by GitHub

**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 **

	' Created by Erik De Rouck
	' Version 1.00 from Nov/10/16
	' Checks the existence of the source file or directory
	Function Check_Existence(sFileDir As String, sType As String) As Boolean
	'Declarartion of variables
	Dim sTestStr As String
	If sFileDir = "" Then 'oeps nothing there, getting out of here
	Check_Existence = False
	Exit Function
	End If
	' Check for any slashes in the directories
	If sType = "dir" And (Right(sFileDir, 1) = "\" Or Right(sFileDir, 1) = "/") Then 'oeps there's a backslash in the directory
	Check_Existence = False
	MsgBox "Please do not specify directories with ending slashes ((/ or \)!", vbCritical, "cloudshill demo"
	Exit Function
	End If
	' make split between directories and files
	On Error Resume Next
	If LCase(sType) = "dir" Then
	sTestStr = Dir(sFileDir, vbDirectory) ' Directory testing
	ElseIf LCase(sType) = "file" Then
	sTestStr = Dir(sFileDir, vbNormal) ' File testing
	End If
	On Error GoTo 0
	If sTestStr = "" Then
	Check_Existence = False ' Problem
	ElseIf sTestStr <> "" Then
	Check_Existence = True
	End If
	Exit Function
	Errorlabel:
	Check_Existence = False
	WRITE_TO_LOG "error", "Check_Existence", Err, "The following error was encountered: " & Err & "- " & Error(Err)
	End Function

view raw filecopier_Check_Existence.vbs hosted with ❤ by GitHub

**End of Code**

That's all folks!

Using Drill Through Reports in Hyperion Smartview add-in (for metadata)

Extra metadata information needed please

On a regular basis following question pops up with Essbase users:

"We want to show metadata linked to our clients (or suppliers, employees,..) in Essbase. How can this be done? But do we need to load all of this into Essbase to do so?"

The answer is yes, it's possible to lookup  - while analyzing data in your Smartview sheet - any additional information for (a) specific dimension(s) from external tables by means of  "drill through" report. 

Interested? You might want to check out this video to see how the end-user experience looks like in Smartview before you dive into the below specified technical details.

https://youtu.be/uP4DMyaDQt0

Essbase Studio

There are a couple of possibilities to link data residing in relational tables (e.g. Oracle Database, txt files,..)  with the data or metadata stored in Essbase. This posting explains the use of Essbase Studio Console for querying additional metadata residing outside of the Essbase cubes.

Essbase Studio is the updated version of the old Integration Services but combines this concept with the possibility to do more "classic" admin actions, as we know them from the Essbase Administration Console.   You might already have heard that Oracle positions the Studio as the future standard toolbox for the Essbase administrator, so it's worth spending some time on this.

A typical setup within Studio is to make a connection to a number of tables containing either data and/or metadata. Studio can then automatically propose a star schema that can be used to populate your dimensions and to load in the data.

To Drill-Through or not, that's the question

Cube Setup

In our example we would like to see the phone-number for Adam, one of our employees. We have following information available in a table of our sample HR system:

Content of the relational table from Oracle Database visualized in an Excel sheet

We only loaded in the Employee-id, using the combined First and Last Name field as the alias.  One could off-course decide to additionally include these fields into the Essbase database and query them natively, but there might be some advantages in not doing so:

• infrequent use of the data
• ownership of the data
• or you just don't want to make this part of the process

Essbase Studio - view of the setup of the hierarchy/dimension

In our outline editor (Essbase Administration Console) this looks like this:

Essbase Administration Console - Outline Editor

Drill Through Report Setup

One of the objects that can be added in the Studio Editor is the "Drill Through Report" option we are looking for.

Essbase Studio

On the 'Context' tab, you choose the dimension(s) the report should work with..

Essbase Studio - Content Tab

 .. and in the 'Report Contents'- tab, select the columns from the table that should be shown when the drill is executed.

Essbase Studio - Report Content

Voilà, this will make our example work. The result can be seen in the video on top of this posting (if  you haven't done so).
More advanced setups may require the tuning of the SQL query with the necessary variables to link table and dimension(s).

Essbase Studio - Report Contents - Template SQL