Grid-enabled Excel Spreadsheets - Part 1
I’ve been working on building an Excel VBA sample that demonstrates how to grid-enable an Excel spreadsheet. There are a few techniques for this. We already ship a sample.NET/DLL assembly, but I wanted to see how to distribute a VBA-only Excel 2002 spreadsheet using the Digipede COM API. My other criterion was that I needed to solve the problem in a way that would be natural for someone who is only comfortable in Excel. No Visual Studio. Ouch. I’m not an Excel programmer so the first thing I had to do was figure out how Excel applications work. The second thing I had to do was refresh my knowledge of the Excel VBA syntax.
I don’t know if my assumptions are correct but my research leads me to suspect that Excel is often used as a data store. And that many Excel programmers only write in VBA or prefer to stay in that environment. Anyone have any thoughts on that?
So to solve this problem I created a Master.xls file which communicates with the Digipede Network and creates the job. Worker.xls files are created (one for each task) in a shared directory and each Task object launches an instance of Excel with a Worker.xls file as input. Worker.xls executes and stores the generated information on the spreadsheet in a cell. Master.xls opens each Worker.xls file when the Task completes and extracts the information. When the Job completes the final calculations are performed and then results output to the Master.xls.
This exercise will be written up in more detail next week along with debugging tips and tricks I learned. The information will then be posted to the Digipede Forums. I think that the older VSTO models are going to be around for a long time and that folks are going to want solutions that require minimum development impact for them. When I finish this write up I have a few other Excel models I want to build. I’m sure that people will appreciate being able to see something that is similar to what they are already doing.