.NET Connection Tool

Introduction 
.NET Connection is a new free .NET based tool for MS Excel to create and store connection strings in to a various number of databases and to other sources. This is my first public free .NET based tool for MS Excel and it will be followed by additional tools based on the .NET technology in the future.

It can create connection strings with both the Data Link Wizard as well as with the .NET Data Link Wizard. The .NET Data Link Wizard provides us with access to .NET Framework Data Providers to create connection strings with. These Data Providers are not available through the Data Link Wizard.

To store and handle the connection strings it uses a Microsoft Database (aka Access database). It may appear as an ‘overkill’ but in the future it will also handle additional  data of different types.

Per se it is a COM Add-in which has been developed and deployed with Add-in Express 2007 for Microsoft .NET. It is also an update of my free ADO Connection Add-In for Excel.

Requirements & Installation 

Requirements:

• Excel 2000, 2002, 2003 or 2007
• .NET Framework 2.0 and higher
• Windows 2000 SP-4, Windows XP SP-2 or Windows Vista
• (The option “Trust access to the VBA Project object model” must be checked – I still prefer that this is done by the owners of the computers then changing it through Windows Registry – code of ethics )

To install .NET Connection Tool You download it from here, save it to the hard drive and then execute the MSI-package.

The package has intentionally been kept simple and clean. It does not check if the above requirements are met or not.

Using .NET Connection Tool
After the installation of the tool a new item has been added to the menu in the VB Editor as the following screenshot shows:

When selecting the command .NET Wizard it starts with the following dialogform:

In the next step the following dialogform is showed:

The following dialogforms are similar to both Wizards which make it possible to store the created connection string with a unique name:

Please also see the startpage at MSDN for .NET Data Providers

Let me me know what You think about it and also how it can be improved.

Special note:
The .NET Connection Tool is provided “AS IS” and with no liabilities from Dennis Wallentin or XL-Dennis.

Kind regards,
Dennis 

CSV files, ListObject Controls and VSTO

Despite the strong focus on XML we still need to handle traditional file formats like CSV et al. Here in this post we will take a closer look on how to use CSV files as data sources to ListObject controls in Excel. In the example I use VSTO but the technically approach can be applied in .NET automation of Excel 2003 and 2007.

The ListObject control acts like a DataGrid and in addition to retrieving data it can manipulate data (add, delete and update data) in the data source it’s connected to. To manipulate the data in the data source we need to use a DataSet. It’s my understanding that the option to manipulate data is the main reason for why we can only use DataSets and not DataReaders together with ListObjects. 

In this post I will only view one way to retrieve data from a CSV file as it’s beyond the scope of this post to discuss any further ADO.NET. As for the case it can be argued that after populating the ListObject control in the worksheet the VSTO solution can be detached. Host controls like ListObject that are added programmatically are not persisted when closing workbooks. Instead it’s converted to a regular range and the data binding is lost.

 

In general I find the ListObject control easy to work with and as the above case shows it’s also easy to connect and disconnect ListObjects from various external data sources. Classic ADO supports CopyFromRecordset which makes it convenient to place a large amount of data in a worksheet. Using ADO.NET with the ListObject control provides the same functionality but comes with more powerful options.

The source code is available here for download.

Have You shipped a solution that leverage the ListObject control together with a database?

For an introduction to the ListObject Control please see:

• ListObject Control 
• Fill ListObject Control with Data
• ListObject.DataBoundFormat Property
• Working with Tables in Excel 2007 (VBA)

Kind regards,
Dennis