Databinding best practices for Excel (Christin Boyd)

I read a question on the forum about databinding in an Excel solution.  The developer was asking about Excel 2003, but the answer I wrote will work for both Excel 2003 and Excel 2007.  Here’s the question:

As a best practice you can create one dataset in ThisWorkbook designer and then programmatically reference it from your code in other sheets.

Here’s how:

Add new Data Source of Data Base type, by specifying a database connection and selecting tables, views and columns.

Next open ThisWorkbook.vb in the designer.  It will look like it’s a big gray box with two sentences that say “This is the workbook designer….”  Now from the Toolbox, under the Data section, select a DataSet and drag it onto the design surface.  When you drop it, you will get a dialog that asks if you want a typed or untyped dataset.  Select Typed.  It should prepopulate an edit box with <your namespace>.MyDataSet.  Click OK.  On the design surface you should now see an icon next to MyDataSet1.  In C# you will want to set the modifier to Internal or Public on the data set in the property browser.

Next open the code behind ThisWorkbook.vb.  Inside the ThisWorkbook_Startup  procedure, you should fill your MyDataSet1 instance with code like this:

VB Sample

  DimpatientAdapter As New MyDataSetTableAdapters.PatientsTableAdapter

  Dim rxAdapter As New MyDataSetTableAdapters.PrescriptionsTableAdapter

C# sample

  MyDataSetTableAdapters.PatientsTableAdapter ta =
    new MyDataSetTableAdapters.PatientsTableAdapter();

Next you can open Sheet1 designer.  Now select “Add New Data Source” from the data menu and this time choose to add an “Object” not a database!  You will get a dialog that shows the hierarchy and this time DO NOT select the table adapter!  Open the name of your project, then the namespace, then you should see MyDataSet. Select it then click OK.  This action will create an Object in your Data Sources window.

Now you can select from the Data Sources Window, you should see something called MyDataSet.  This object can be used to create a databinding in your sheets.  So you can drag from this universal object into any one of your sheets.  Grab it and drag it onto Excel Sheet1 and it created an empty table. In order to finish setting it up, select the table and in the property grid click on the arrow next to DataMember property and select the data table that you want to be bound to the table. Try it, and see how it creates a new BindingSource object for each sheet, but you still have just the one DataSet instance. 

In each Sheet, you need to add the following line of code for Each table:

  Me.PatientsDataTableBindingSource.DataSource = Globals.ThisWorkbook.MyDataSet1

Please let me know through comments on this blog if this explanation works for you.  Thank you!