Using Excel as a Client Front End to Manage 2000

This is a bit of a follow-up to a previous post I wrote  about VSTO and Manage 2000  ERPBusinessObjectService. Visual Studio Tools for Office (VSTO) provides a development framework for coding behaviors into classes of Excel spreadsheets.  You can deploy them with one-click installs and users can save them as templates for creating smart spreadsheets.

Connecting VSTO code with Manage 2000 can be very easy with the ERPBusinessObjectService. A little bit of code in C# or VB.Net can read and post datasets of Manage 2000 business objects. However you may run into a lot of plumbing work moving values from business object dataset tables to the spreadsheet cells and back.  Excel ListObjects can greatly simplify this coding chore. ListObjects define mappings and databind values between columns in the worksheet and row data of columns in the business object data tables:

With this little bit of code you can synchronize the spreadsheet column cell values with a corresponding table in the business object dataset, and use the webservice to read the dataset from or post the dataset back to Manage 2000, turning Excel into a client UI for Manage 2000.

Programming Manage 2000 Access Into Excel

I was looking for a way to demonstrate Manage 2000 Data Access Components and web services outside of Manage 2000 web functions. I found VSTO, that is Visual Studio Tools for Office.

VSTO provides templates for creating Office projects like Excel WorkBooks and custom Ribbon Tabs.

You get to write .Net code to add behavior to the Excel workbook. It starts getting interesting to me when you add a web service reference to /MT/ERPBusinessObjectService/ERPBusinessObjectService.asmx.

With this combination you can create Manage 2000 awareness in Excel workbooks. This might take the form of item validation:

Or maybe Cross Referencing:

Or even reading and writing business objects:

You do have to be mindful of release levels.  Visual Studio 2008 cannot work on Office 2010 projects. Visual Studio 2012 has trouble with ASP.NET 4.5 assemblies unless you download the latest AddIn that makes it compatible with both Office 2010 and Office 2013.