Excel as datasource?

0
votes

I'm facing a huge challenge with some excel sheets which was written in 90's for some financial calculations. Basically there are around 100 different excel sheets(different in structure, content, formulas,macros etc) and this needs to be integrated into one single application and thus decommission all those excels.

Some of the features of the excel sheet:

  • Every excel sheet has some defined number of sheets with formulas and macros associated with it. The macros are written in VBA and makes use of extensive addin's in the form of XLL's.

  • Every excel sheet also has some user defined work sheets, which rely upon the user input data and the predefined sheets to compute instrument prices.The most important thing is that the user inputs data to the user created sheets, which is referenced in the pre defined sheets.The predefined sheets calculates something based on this, and the result is again referenced in the user defined sheets.

We were thinking of various approaches to decommision this as below:

  • Use Excel Services with Sharepoint 2010, expose the excel sheet as a web service and write a client application which will do the user defined part and then communicate with the excel services for the result.(it will also have to write to the predefined sheets hosted in excel services)

  • Use excel automation(??)

My question to you is what would be the best feasible approach here to decommission those excels?.All I have to do is to write to excel cells, let the formulas/macros play there part to compute the result, get back the results.We are also very keen not to re create the whole excel set up i.e re create the formula's/addins which is in place, but rather consolidation of them.

Any advice would be much appreciated!!!

Cheers, -Mike

2
c#sharepoint-2010excelvba
Excel Services on Sharepoint would be the most 'career friendly' approach. There's a lot of work available for programmers with this experience. - jfrankcarr
VBA functions wont run on the server though, so you'll need to convert all those functions first. The Achilles’ heel of Excel Services adoption in my opinion. - Paul Leigh
@Paul: How do we do that? There is a lot of code which has gone into the excel sheet as VBA code, is there an easier way to convert them into something and use it? - Mike
No, Excel Services uses a REST api so you'll manually have to re-create the functions if you are to use Excel Services. This is a massive adoption barrier for finance institutions who use (and I'm not joking) millions of spreadsheets. - Paul Leigh

2 Answers

1
votes

If we ignore particular technologies, you have three basic options although combinations are possible:

  1. Keep the existing Excel workbooks in the background and provide a new front-end.
  2. Migrate the VBA to some new tool.
  3. Identify with your users, the functionality they need today and tomorrow and implement that.

I would be surprised if Option 1 did not give you the cheapest and quickest integration. The downside is that you remain dependent on those old Excel workbooks. Are they based on early versions of Excel or have they been migrated forward? If not, do they work with the latest hardware and operating systems? Will they work with tomorrow's hardware and operating systems? Microsoft makes enormous efforts to maintain backward compatibility but that does not mean they achieve 100% backward compatibility nor that they can or will maintain backward compatibility in future.

Is Option 2 feasible? Do you know what each workbook does? The VBA can be migrated/converted to a new tool but what about the Add-ins? Do you have the source? Do you have the documentation? The trouble with relying on the documentation is that some bugs become undocumented features that people use.

With Option 3 you need not worry about bringing anything forward. You pick the right tool for today and tomorrow and use it to implement the functionality you need. You have to be slightly careful. If you ask users if they need facility X, they will say yes regardless. The question to ask is: what do you use facility X for?

Each of these options has risks? Do you have the information to access the risks? Can you quantity the work and risks associated with each of these options? Only when you can will you be able to select an approach and then a technology.

1
votes

I am not familiar with Sharepoint, so can't compare, but with Excel automation it is really possible to open a sheet, enter some values there and read results. It even can be done visually as if somebody opens Excel on the screen and does something in it. I would use some modern .Net language for this if your old sheets are compatible.