Is Excel Distributable?
Dom Yarnell - May 7, 2017
As an ardent proponent of using Excel to improve your work experience, I’ve heard a wide range of criticisms, one of which is that Excel is not distributable. However, once you learn how to pair a few lines of VBA with some basic Excel functionality, I think you’ll see that it’s perfectly suited for distributing both Excel files and any other files on which your users depend.
Let’s consider a situation I frequently encounter: You develop an Excel file that needs to be tested and ultimately used by several people in your company, and you decide to attach it to an email with instructions on how to use it. The problem occurs when you’ve gathered feedback from users and implemented improvements in the next version of the file that you want users to test. Sending a second email with a second Excel file almost inevitably creates uncertainty in the minds of users: Am I using the latest version? Was there another email I missed? In theory, users should be able to keep track of emails from developers and quickly determine whether they are using the latest version of the file—but in practice, everyone is busy, and most people have more important things to do than manage emails from developers. It’s not hard to understand why IT (and others) often prefers to deploy tools via an internal website, where users always go to the same URL and use the latest version of a tool. But that same benefit can be implemented more easily and extensively through Excel.
In the Example above, instead of emailing your Excel file to users, you could instead place your file in a folder that’s accessible to your users and email them the path to that folder, like, “N:\Rating Models\Rating Model XYZ.xlsm”. This method solves one problem but creates another: What if users save the Excel file with some of their own entries, or accidentally delete some ranges (assuming you haven’t protected the file yet), effectively corrupting the template? In order to develop a solution to this pitfall, we’ll first discuss a very handy object we can reference in VBA: the FileSystemObject.
The FileSystemObject (FSO) enables you to manage files and folders using very intuitive VBA code, but in order to use it you must reference the appropriate DLL file. From your VB Editor, go to Tools->References…, scroll down and check the reference for ‘Microsoft Scripting Runtime,’ and click OK.
Dom Yarnell - May 7, 2017
As an ardent proponent of using Excel to improve your work experience, I’ve heard a wide range of criticisms, one of which is that Excel is not distributable. However, once you learn how to pair a few lines of VBA with some basic Excel functionality, I think you’ll see that it’s perfectly suited for distributing both Excel files and any other files on which your users depend.
Let’s consider a situation I frequently encounter: You develop an Excel file that needs to be tested and ultimately used by several people in your company, and you decide to attach it to an email with instructions on how to use it. The problem occurs when you’ve gathered feedback from users and implemented improvements in the next version of the file that you want users to test. Sending a second email with a second Excel file almost inevitably creates uncertainty in the minds of users: Am I using the latest version? Was there another email I missed? In theory, users should be able to keep track of emails from developers and quickly determine whether they are using the latest version of the file—but in practice, everyone is busy, and most people have more important things to do than manage emails from developers. It’s not hard to understand why IT (and others) often prefers to deploy tools via an internal website, where users always go to the same URL and use the latest version of a tool. But that same benefit can be implemented more easily and extensively through Excel.
In the Example above, instead of emailing your Excel file to users, you could instead place your file in a folder that’s accessible to your users and email them the path to that folder, like, “N:\Rating Models\Rating Model XYZ.xlsm”. This method solves one problem but creates another: What if users save the Excel file with some of their own entries, or accidentally delete some ranges (assuming you haven’t protected the file yet), effectively corrupting the template? In order to develop a solution to this pitfall, we’ll first discuss a very handy object we can reference in VBA: the FileSystemObject.
The FileSystemObject (FSO) enables you to manage files and folders using very intuitive VBA code, but in order to use it you must reference the appropriate DLL file. From your VB Editor, go to Tools->References…, scroll down and check the reference for ‘Microsoft Scripting Runtime,’ and click OK.
Now you can use VBA to determine whether the folder, “N:\Rating Models\”, exists by using the following code:
Dim FSO as New FileSystemObject
If FSO.FolderExists(“N:\Rating Models\”) Then...
You can also list the names of all the files in a folder by using a loop and an FSO as follows:
Dim FSO as New FileSystemObject, fdrTemp as Folder, filTemp as File, lngI as Long
Set fdrTemp = FSO.GetFolder(“N:\Rating Models\”)
For Each filTemp In fdrTemp.Files
ActiveCell.Offset(lngI,0) = filTemp.Name
lngI = lngI + 1
Next filTemp
The code above will list every file in the “N:\Rating Models\” folder, starting in the cell currently selected. (Note: Using “ActiveCell” in your code is generally not a good idea, and I only use it here for illustrating the functionality of the FSO. As a rule, VBA should populate named ranges in Excel.)
The last bit of VBA code is used to open an Excel file in Read-Only mode:
Workbooks.Open “N:\Rating Models\Rating Model XYZ.xlsm”, , TRUE
The Open procedure of the Workbooks object has several optional arguments, and by entering TRUE in the third argument, we’re telling VBA to open the target file in Read-Only mode. That way it is unlikely that your users will accidentally save over the template.
Now that we know how to use VBA to list names of files in a folder and how to open a file in Read-Only mode, implementing this functionality with Excel is accomplished through named ranges and validation. The details of how to use range names and validation will not be reviewed here (there are countless sources on the web), but you can review a completed version of the Portal File we're describing by following the link at the end of this article. You will probably notice that the downloadable Portal File contains a lot more code than I review above, but the additional code is mostly for formatting and error handling; important to know, but not necessary for understanding the key concept.
Another problem that may creep up with the implementation of the distribution method described above: What if users just save their files in the same directory, and the Portal file lists a growing number of files, making your template increasing difficult to find? A handy solution to this problem lies, once again, with the FSO. Rather than opening the template directly from its folder, you can use an FSO to copy the folder to a local directory of your choosing, preferably one that you create using the FSO. You can check whether a folder exists using the FolderExists function, create a folder using the CreateFolder method, check the modified date of the template using the DateLastModified function, and copy the template to the local directory using the CopyFile method. Once the (relatively small) amount of code is in place, your users won’t accidentally save their files to the network directory, as they will be opening copies of the templates from a local directory—in fact, users have no reason to know what the network directory is. Added bonus: If the network is down, your users will have a copy of the template on their local directory as long as they used the Portal File once.
So far we’ve discussed opening only Excel files, accessing them with the Open method of the Excel Workbooks collection in the code above. But we can generalize the process to any file type with the following code:
Dim appObj as Object
Set appObj = CreateObject(“Shell.Application”)
appObj.Open (“C:\Rating Models\Rating Model Documentation.docx”)
Now, in addition to distributing your Excel files, your Portal file can be used to distribute Word documents, PDF files, or anything else.
It should be clear now that Excel can easily distribute any files on which your colleagues depend. Furthermore, the addition of some relatively simple database functionality (also accessible via a Reference in the VB Editor) enables you to develop a robust file management system around Excel.
I hope you’ll experiment with the code above (and more), but most of all I hope that you’ll remember this article when you hear about the alleged shortcomings of Excel. Every application has limitations, and Excel is no exception, but I've come to learn that most people are poor judges of what exactly those limitations are when it comes to Excel.
Dim FSO as New FileSystemObject
If FSO.FolderExists(“N:\Rating Models\”) Then...
You can also list the names of all the files in a folder by using a loop and an FSO as follows:
Dim FSO as New FileSystemObject, fdrTemp as Folder, filTemp as File, lngI as Long
Set fdrTemp = FSO.GetFolder(“N:\Rating Models\”)
For Each filTemp In fdrTemp.Files
ActiveCell.Offset(lngI,0) = filTemp.Name
lngI = lngI + 1
Next filTemp
The code above will list every file in the “N:\Rating Models\” folder, starting in the cell currently selected. (Note: Using “ActiveCell” in your code is generally not a good idea, and I only use it here for illustrating the functionality of the FSO. As a rule, VBA should populate named ranges in Excel.)
The last bit of VBA code is used to open an Excel file in Read-Only mode:
Workbooks.Open “N:\Rating Models\Rating Model XYZ.xlsm”, , TRUE
The Open procedure of the Workbooks object has several optional arguments, and by entering TRUE in the third argument, we’re telling VBA to open the target file in Read-Only mode. That way it is unlikely that your users will accidentally save over the template.
Now that we know how to use VBA to list names of files in a folder and how to open a file in Read-Only mode, implementing this functionality with Excel is accomplished through named ranges and validation. The details of how to use range names and validation will not be reviewed here (there are countless sources on the web), but you can review a completed version of the Portal File we're describing by following the link at the end of this article. You will probably notice that the downloadable Portal File contains a lot more code than I review above, but the additional code is mostly for formatting and error handling; important to know, but not necessary for understanding the key concept.
Another problem that may creep up with the implementation of the distribution method described above: What if users just save their files in the same directory, and the Portal file lists a growing number of files, making your template increasing difficult to find? A handy solution to this problem lies, once again, with the FSO. Rather than opening the template directly from its folder, you can use an FSO to copy the folder to a local directory of your choosing, preferably one that you create using the FSO. You can check whether a folder exists using the FolderExists function, create a folder using the CreateFolder method, check the modified date of the template using the DateLastModified function, and copy the template to the local directory using the CopyFile method. Once the (relatively small) amount of code is in place, your users won’t accidentally save their files to the network directory, as they will be opening copies of the templates from a local directory—in fact, users have no reason to know what the network directory is. Added bonus: If the network is down, your users will have a copy of the template on their local directory as long as they used the Portal File once.
So far we’ve discussed opening only Excel files, accessing them with the Open method of the Excel Workbooks collection in the code above. But we can generalize the process to any file type with the following code:
Dim appObj as Object
Set appObj = CreateObject(“Shell.Application”)
appObj.Open (“C:\Rating Models\Rating Model Documentation.docx”)
Now, in addition to distributing your Excel files, your Portal file can be used to distribute Word documents, PDF files, or anything else.
It should be clear now that Excel can easily distribute any files on which your colleagues depend. Furthermore, the addition of some relatively simple database functionality (also accessible via a Reference in the VB Editor) enables you to develop a robust file management system around Excel.
I hope you’ll experiment with the code above (and more), but most of all I hope that you’ll remember this article when you hear about the alleged shortcomings of Excel. Every application has limitations, and Excel is no exception, but I've come to learn that most people are poor judges of what exactly those limitations are when it comes to Excel.
