Excel Migration Part II: Calling a .Net Process

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/excel_migration_part_ii_calling_a_net_process.htm]

In my previous blog I discussed extending Excel with VBA vs. re-writing the app. I said I wanted to create a hybrid solution. Here I'll propose a solution for using .Net to improve how you view and share the final data with clients.

Essentially we want to create a "thin" report of our final data. This report is thin in the sense that it only contains the data and the means to present it. It does not contain calculation logic, validation, user interactions, security, etc... The context is that you've used your Excel sheet to calculate the data needed for a widget, and now you want to send that data to a client. You don't want to send them the entire workbook, as this is:

• Platform Dependent - it requires that they have not just Excel, but the correct version of Excel.
• Bulky - It physically takes up more space (you store all the rows, and all the calculations for each row)
• Messy - while you can format things nicely in Excel (such that it prints okay), this is extra work to do. It is also much harder when you only want to display one row.
• Excess - Suppose you're using the same sheet to manage widgets for 10 different clients, you only want to send one (or several) rows of output - not every row in your sheet.
• Unsecure - Say you only want to send your output, not all the calculations you used. Your calculations may even be intellectual property that can't be distributed. Sure you could put a password on the Excel sheet, but that is an extra step and still gives away your calculations - albeit in a locked format. Wouldn't it be ideal to not even send the calculations at all?
• Changeable - suppose you're sending this to a manager - do you really want to risk that manager "accidentally" fiddling with your calculations or breaking the Excel sheet? Ideally you could give them an idiot-proof document with no "movable parts" that cannot be broken.

One solution that solves all of this is to have a tool generate an Html report from only the Excel output that you want. Html would have the benefits:

• Platform Independent - it has no need for Excel. Anyone in the world can read HTML
• Light weight - a small, text-based file.
• Easily formatted
• Minimal - only send the output you want
• Secure - it only includes the results, none of the proprietary calculations used to get those.
• Unchangeable - a pure Html doc has no "movable parts". It is read only and impossible even for the most clumsy user to mess up.

How we would implement this:

1. Create a .Net Console App that takes in the necessary info to read the Excel book and create the desired Html Report
2. In Excel, have a processes to call this app via the command line.

Say this .Net console app, called CreateHtmlReportConsole,  would take four parameters: the physical path to the Excel book, the physical path to an html file that servers as a template for the report, the Excel worksheet, and the Excel row to pull data from. A sample command line may look like so (I've included the quotes in case your paths have spaces in them):

"C:\myProjects\CreateHtmlReportConsole.exe" "C:\ExcelBooks\Book1.xls" "C:\myProjects\Template.htm" 1 14

I'll show step #2 here, and discuss step#1 more in another blog. You could have a method in VBA (from a button or context menu) use the Shell command to call an external process like our .Net console app. For example:

Dim strCmd As String Dim strCurrentDirectory As String Dim strThisFileName As String 'Get current directory and filename: strCurrentDirectory = ThisWorkbook.Path & "\" strThisFileName = ThisWorkbook.Name 'Create a command line like: ' "C:\myProjects\CreateHtmlReportConsole.exe" "C:\ExcelBooks\Book1.xls" "C:\myProjects\Template.htm" 1 14 strCmd = strCurrentDirectory + "CreateHtmlReportConsole.exe """ + _     strCurrentDirectory + strThisFileName + """ """ + _     strCurrentDirectory + "Template.htm"" 1 " & intRow Shell (strCmd)

Next step: how add dynamic context menus in Excel, from which you could call this code.