.NET Co Library – Part III

To read previously blogposts about the .NET Co Library please see the following entries:

• Part I
• Part II

Here in the third blogpost on the subject it will cover the part that create and store connection strings. This part is based on the .NET Connection Tool with some improvements. It uses the following main Windows form:

There is not so much to discuss when it comes to this part. It uses both the .NET Connection Wizard as well as the Data Link Wizard. To some extend these two wizards overlap each other but together they represent the past, the present and the future. The limit of characters for connection strings are now > 32.000 while the limit for aliases are 15 characters and no spaces are allowed.

In the next blogpost about .NET Co library I will be discussion requirements, known issues and also how my strategy for deploying solutions like this one.

Kind regards,
Dennis

.NET Co Library – Part II

The managed COM Add-in .NET Co Library is introduced in the following blogpost: .NET Co Library – Part I 

The Code Part 

In order to add VBA code to the library we simple select the wanted code in the code window and choose one of the following commands under the Code section:

In the next step the following Windows form will become available where we create an alias for the code and add a comment if wanted:

The Code Library is organized in four predefined (and fixed) main groups, Code Snippet, Procedure, Module and finally the SQL Query group. Under each main group we can create customized subgroups. Under each subgroup we can add code to the library where each piece of code is saved under a customized unique alias.

The following screenshot shows the main Windows form, i e the core of the Code Library:

As You can see we can easily select main group, subgroup and a specific alias to which the code is associated with. We can both update and delete entries but the key function is to select wanted code to be inserted in the code window. We can either select a single code to be copied to the Window’s clipboard or we can add several code pieces and then copy all code to the clipboard.

The more code we store the larger the Access database will be (2 GB is the limit how large an Access database can be). Therefore a search function is available to, in a convenient way, locate the wanted code. The present search function is based on two similar SQL expressions, LIKE and NOT LIKE, where both of them can be used with “%” and “_”.  The following screenshot shows the Windows form for the search function:

In the next blogpost on the subject I will cover the Connection part of the add-in. 

Kind regards,
Dennis

.NET Co Library – Part I

This is the first post about my new (coming) free managed COM Add-in “.NET Co Library” for MS Excel 2000 – 2007.

What .NET Co Library is

For a longer time I have used a similar tool (for internal use only) to store created VBA code and to reuse all the VBA code. Originally I used the CodeLibrary Access database that was shipped with the Microsoft Office Develop Edition (MODE). After some time I developed my own tool for it and at the same time I ported it to a SQL Server 2000 database. 

The Add-in is also a revised and updated version of the  .NET Connection Tool. When .NET Co Library is shipped then this tool will no longer be available for download.

The acronym “Co” in its name refers to Code and to Connection.

With the tool we can:

• Create connection strings to a various number of databases via two wizards, the .NET Wizard and the Data Link Wizard.
• Store the created connection strings in a structural way enabling us to reuse the connection strings in all kind of VBA solutions.
• In an organized way store VBA code, code snippets / procedures / modules, and SQL Queries which enabling us to reuse the code when wanted.

The Add-in uses an Access database to store all the data in. However, I have an idea about allowing using another database (SQL Server, MySQL et al) in a future update.

When the Add-in is installed a new menu item is added to the standard toolbar in the VB Editorn. The three main parts of the tool are:

• Code

• Connection 

• Maintenance

 Code

The following screenshot shows the commands available under the Code section.

To add a code snippet to the database we first select the wanted code and then select the command Code Snippet. In the next step a wizard guide helps us further.

In the active Code Window a new sections is added to the menu – Code – when we right-click in the window. The following screenshot shows the available commands:

Connection

The Connection section contains the .NET Connection Tool’s solution (where the 255 limit no longer exist for connection strings – More than 32000 characters are now allowed for the connection strings). The following screenshot shows the available commands:

Maintenance

This section includes tools to maintenance the database and to make some general settings for the Code Library. The tools also makes it possible to move the database to a new location. For instance, the database can be moved to a network location where it can be shared by a group of developers. The following screenshot shows the available commands:

 

 

Another aspect that I may consider in the future is to make it available for other Office programs. Since most MS Office programs (except MS Outlook) expose the same VBE object model the work to implement it in MS Word,  MS PowerPoint, MS Access, MS Project and MS Visio is rather simple. But first of all I like to make it available for MS Excel.

Finally, the development tools I use for .NET Co Library are:

• Microsoft Visual Studio 2005 SP-1

• Add-In Express 2007 for Microsoft .NET

• Component One Studio Enterprise

• MZ-Tools 2oo5 for Visual Studio 2005

• RoboHelp

This blogpost (hopefully) present the .NET Co Library from a general point of view which I also hope is understandable. In the next blogpost on the subject focus will be set to the Code section of the tool.

As usual, comments are welcome.

Kind regards,
Dennis