MS Excel sites

Dennis Wallentin — Wed, 03 Mar 2010 13:42:06 GMT

Abstract:

The following list cover many areas about Excel and what we can do with the spreadsheet-software. Take your time to visit them and take part of all the good knowledge they share with the global Excel community.

General Sites

Comments

XcelFiles

Ivan F Moala's site with lot of amazing stuff.

Colo's Junk Room

Masaru Kaji´s site with all The Cellmasters and VBA & Javascript.

OzGrid Business Applications

Dave & Raina Hawley's site where you find almost everything about MS Excel.

AJP Excel Information

Andy Pope's site with nice solutions for charting and VBA.

The Code Net

Mark Rowlinson's growing site with great stuff.

Andrew's Excel Tips

Andrew Engwirda's nice site for Excel addicts.

Scriptorium

Info about updates:

2009-08-08: New version available that support Windows XP, Windows Vista, Windows 7 and also Excel 2010

2009-08-02: The present version does not support Windows 7 RC. A new version is under development.

2007-05-28: Removed the "error" message when installing on Windows Vista and updated the installation file accordingly.

2006-07-03: A minor general update of this article.

2006-05-27: A minor update to open files with the new file extension in Excel 2007.
2006-05-25: A minor update to control add-ins created with Excel 2007.

Task

Excel Loader is a standalone utility that let the user choose which add-ins should be loaded or not when launching Microsoft Excel.

How to use Excel Loader in summary:

Start Excel Loader
Choose version of Microsoft Excel

Choose if Microsoft Excel will start with a new workb

Online resources for VSTO

Dennis Wallentin — Thu, 02 Jul 2009 01:52:56 GMT

Abstract:

General

OfficeZealot VSTO Zone

Microsoft

Blogs

Articles

Deployment for VSTO 2008 (3.0)

Professional Excel Development (PED) Second Edition

Dennis Wallentin — Sun, 07 Jun 2009 14:27:37 GMT

Abstract:

Professional Excel Development - Second Edition

Have you read all the best Excel programming books? Do you know how the Excel object model works? Are you familiar with macros and worksheet design techniques? Do you still have difficulty translating your knowledge and experience into comprehensive Excel applications? If so, Professional Excel Development is the book for you.

Professional Excel Development doesn't waste time explaining what you already know about Excel. It starts where other Excel books leave off and builds on the techniques you already know to demonstrate how professional Excel programmers create commercial quality Excel applications.

Professional Excel Development is more than just a theoretical guide to the concepts and techniques employed by professional Excel developers. As the book progresses, we build a fully functional, professional-quality timesheet reporting and analysis system that illustrates the concepts and techniques introduced in each chapter.

Summary Table of Contents

Chapters 1 to 3 provide an introduction to professional application development with Excel, explain the different styles of application we might choose to create for different circumstances, and identify some general best practices for working with Excel and VBA that will be followed throughout the book.
Chapter 4 explains how to design and structure worksheet user interfaces for data entry and analysis.
Chapters 5 and 6 introduce two specific types of application - the add-in and the dictator application, whi

SQL Tester

Dennis Wallentin — Mon, 20 Apr 2009 21:22:57 GMT

Abstract:

SQL Tester
The tool for retrieving data from databases and putting it into MS Excel

News

Version 1.2 is now available which supports Excel 2000 to 2007 on Windows Vista or Windows XP. If you have a valid license of SQL Tester then please e-mail to support@xldennis.com in order to get the updated version.

What SQL Tester is

The SQL Tester is an interactive Query Tool (add-in) for retrieving data and displaying it on the screen in an ad hoc basis from almost all kinds of databases. The output can be placed in worksheets and added to workbooks VBA-projects.

It also allows you to:

Copy the displayed data into a worksheet including connection-string and query-string

Create dynamic queries where the data can be updated on a regular basis.

Insert a complete VB

Books on VBA / VB / VB.NET & ADO / ADO.NET & VSTO

Dennis Wallentin — Tue, 07 Apr 2009 19:44:35 GMT

Abstract:

The presented list of books is based on what XL-Dennis consider to be valuable printed resources for intermediate to advanced developers and users.

VBA:

Title	Author(s)	Publisher	ISBN	Year
Professional Excel Development (2nd Edition)	John Green, Dennis Wallentin, Stephen Bullen, Rob Bovey	Addison Wesley	0321508793	2009
Managing Data with Microsoft Excel	Conrad Carlberg	QUE	0-7897-3100-2	2004
VBA and Macros For Microsoft Excel	Bill Jelen, Tracy Syrstad	QUE	0-7897-3129-0	2004
Microsoft Excel 2007 Power Programming with VBA	John Walkenbach	Wiley	0470044012	2007
Excel 2007 VBA Programmer´s Reference	John Green, Stephen Bullen, Rob Bovey and Michael Alexander	Wrox Press	978-0-470.04643-2	2007
VB A Collection of useful Excel articles Dennis Wallentin — Tue, 27 Jan 2009 02:00:48 GMT Abstract: The following list is a collection of useful articles from two great resources, MSDN Library and the Knowledge Base. The list is updated on a regular basis. Office Developer How-to Center This is the portal for all How-to articles Microsoft publish and it cover Office 2007 together with Microsoft Office SharePoint Server: How-to Center Excel 2007 For various reasons I have decided to single out articles that explicit only targeting Excel 2007 from both MSDN and Knowledge Base: General 2007 Office System: Developer Posters Walkthrough: Deploying a Document and an Assembly to a Local Folder Improving performance in Excel 2007 (written by Charles Williams) 2007 Office System Sample: Visual Studio Code Snippets for the Office Fluent UI Security
General information
	This is a free diagnostic tool from Microsoft which gives detailed information about each installed COM add-in. The tool provides a great number of output for unmanaged, managed as well as VSTO Add-Ins. For additional information please see: Using AddinSpy and AS Diagnostic Tools for Microsoft Office Add-ins

Download
	The tool can be downloaded from the following location: http://code.msdn.microsoft.com/AddInSpy/

Note
	The tool is unsupported.

Last Reviewed:

2008-10-01

Keywords:

Export SQL Server data to a worksheet

Dennis Wallentin — Mon, 11 Feb 2008 13:26:45 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2000 and above.
Microsoft Visual Basic.NET 2003 and above.
The following namespaces must be imported to the project:
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Imports ADODB (And a reference to Microsoft ActiveX Data Object Library in NET for Example 3.)
External reference:
Microsoft Excel 9.0 Objects Library and above.
Task
Retrieve data from a SQL Server 2000/2005 database and write it to a worksheet.

Code

Example 1: By using ADO.NET and the SQLDataReader
'Constant Variables for the database connection.

Export Access-data to a worksheet

Dennis Wallentin — Mon, 11 Feb 2008 13:25:46 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2000 and above.
Microsoft Visual Basic.NET 2003 and above.
The following namespaces must be imported to the project:
Imports System.Data
Imports System.Data.OleDb
Imports Excel = Microsoft.Office.Interop.Excel
Imports ADODB (And a reference to Microsoft ActiveX Data Object Library in NET for Example 3.)
External reference:
Microsoft Excel 9.0 Objects Library and above.
Task
Retrieve data from an Access-database and write it to a range in a worksheet.

Code

Example 1: By using ADO.NET and a Datareader:
Constant Variables for the database connection.

.NET Co Library

Dennis Wallentin — Thu, 07 Feb 2008 22:53:11 GMT
Abstract:
Information
The information in this article applies to:
Microsoft Excel 2000 and later.
Microsoft Windows 2000 and later.
.NET Framework 2.0 and later.
Release notes:
28-12-2007:
A blogpost about the Excel 2003 & Windows Vista bug is now available.
11-12-2007:
Eliminated an error message when Excel is automated from another software or command line.
09-12-2007:
Important: The present version does not support Excel 2003 with Windows Vista.
08-12-2007:
A minor but an important update of the help file.
04-12-2007:
.NET Co Library released.

What .NET Co Library is
.NET Co Library is a free managed COM add-in for Microsoft Excel. The acronym Co in its name refers to both Connection and Code.
With the tool you can:
Store created VBA code, code snippets / procedures / modules, and SQL Queries in a well organized way enabling you to easily reuse the code in all kind of Excel VBA solutions.
Create connection strings to a various number of databases with two wizards, the .NET Wizard and the Data Link Wizard. Store the created connections strings in a structural way enabling you to easily reuse the connection strings in all kind of Excel VBA solutions.
.NET Co Library has been designed so it also can be shared within a group of VBA developers ove

Export MySQL-data to a worksheet

Dennis Wallentin — Thu, 07 Feb 2008 22:52:43 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2002 and above.
Microsoft Visual Basic.NET 2003 and above.
The following namespaces must be imported to the project:
Imports System.Data
Imports ByteFX.Data.MySqlClient (Example 1 and 2)
Imports System.Data.Odbc (Example 3)
Imports ADODB with MyOLEDB Provider installed (Example 4)
External References:
Microsoft Excel 10.0 Objects Library or above
Microsoft ActiveX Data Objects 2.5 Library and above.
Task
Retrieve data from a MySQL Database and write to a range in a worksheet.

Code

Here is only the basic part of the procedures viewed and for complete details see one of the related articles below.
Example 1: By ADO.NET and using a Datareader

Retrieve e-mailaddresses from a Notes database

Dennis Wallentin — Tue, 01 Jan 2008 14:56:14 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2000 and above.
Lotus Notes 4.x and above
External reference:
Microsoft ActiveX Data Objects 2.5 Library and above.
Lotus NotesSQL-driver for download:
NotesSQL
Task
Retrieve e-mailaddresses from a Notes database and dump the data into a worksheet.

Code

Option Explicit
Add-in Express 2007 for Microsoft .NET. It is also an update of my free ADO Connection Add-In for Excel.
Important: This tool has a limitation of storing connection string with 255 characters. If you experience this limitation the please use .NET Co Library instead.
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 Windo

MS Excel Tools & Add-ins

Dennis Wallentin — Sat, 17 Nov 2007 13:37:38 GMT
Abstract:
MS Excel Tools & Add-ins
Professional tools
All tools listed below does XL-Dennis use frequently and are recommended for professional developers.
To develop managed Shared Add-ins and VSTO solutions and Add-ins
Managed Shared Add-ins - Add-in Express 2007 for Microsoft.NET
VSTO solutions and Add-ins - Add-in Express 2007 for Microsoft VSTO
For creating all the published code the following excellent add-ins from StarPrint Limited are used:
Excel 2000 and above - vbaCodePrint
MS Visual Basic - vbCodePrint
MS Visual Basic.NET 2005 - VS.NETcodePrint
MS Visual Basic.NET 2008 - VS.NETcodePrint
For all kind of professional text edition and programming:
UltraEdit32
For rebuilding and recover workbooks:
Workbook Rebuilder
ExcelFix
For converting Excel-files to other different filetypes:
Convert XLS or WordConverterExe

Use The OWC Spreadsheet in MS VB 6.0

Dennis Wallentin — Fri, 02 Nov 2007 21:23:37 GMT
Abstract:
The information in this article applies to:
Microsoft Visual Basic 6.0 SP 6.0
Active-X Control(s) in use:
Microsoft Office Spreadsheet 11.0
External References:
Microsoft Excel x.x Object Library
Info
For more information about the Office Web Components (OWC) please see
Q10177 - HOWTO: Articles about Office Web Components (OWC)
Task

Populate an OWC Spreadsheet with data from an Excel workbook and if any data have been changed write back data to the worksheet.
Due to a bug in the property dirty of the Spreadsheet Component it's not possible to use it to check if data have been changed or not. Whenever the cursor is moved around in the worksheet the property dirty is set to true.
Below is a workaround enabling to check if data have been changed or not.
Please note that in the source worksheet it exist a column (Fieldname Changed and it's placed last) that is used to give the status "C" whenever any changes are made in the records.

Code

Create connection strings to databases

Dennis Wallentin — Mon, 06 Aug 2007 15:13:51 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2000 and above.
External References:
Microsoft ActiveX Data Objects x.x Library
Microsoft OLE DB Service Component 1.0 Type Library
Task

One of the more important steps when accessing databases via ODBC and ADO is to create the connectionstring correct. In this article code is presented for creating strings for setting up the connections.
In addition a free add-in is available for download which include as well a procedure that automatically place the string into the clipboard. When installing the add-in a new button is available on the standard commandbar in the VB-editor.

Code

Option Explicit

Creating and Deploying Managed COM add-ins with VB.NET 2005 - Part VI

Dennis Wallentin — Fri, 27 Jul 2007 16:36:43 GMT
Abstract:
Preparation of the managed COM add-in and some house cleaning
Before moving on to the next step we need to prepare the managed COM add-in and also do some ‘house cleaning’.
#1 Remove dependencies
Open the folder ‘Detected Dependencies’ and exclude all files except Microsoft .NET Framework. The easiest way is to right click on each file and select the option Exclude. By default shared add-ins refer to some namespaces which may or may not be used. However it’s beyond the scope of this post to discuss it in more detail. The unused references can be viewed through the property window and via the button ‘Unused references’.

#2 Uncheck the option ‘Register for COM Interop’
Right click on the project name in the Solution Explorer window and select ‘Properties’. On the Compile tab scroll down to the bottom and uncheck the option.

#3 Sign the assembly
Select the Signing tab in the properties window and apply or create a new strong name key file (I will cover Strong Names in the next post).

#4 Make sure that the final version of the add-in has been saved and compiled.
The following picture shows how the project looks in the Solution Explorer windows on my developing computer:

"The mscoree.dll hell"
Traditional COM add-ins, for instance developed with VB 6.0, are fully referenced in the Windows registry while managed COM add-ins are not. By default for all managed COM add-ins the InprocServer32 enter for each of them in the HKEY_CLASSES_ROOT points to one file - mscoree.dll. We don’t need to dig into the registry to get a confirmation of it. Via Excel’s UI it b

Database sites

Dennis Wallentin — Sat, 28 Apr 2007 16:30:40 GMT
Abstract:
The following list of useful online resources cover several areas of database related information.

Add-in Express.NET for Microsoft .NET

Dennis Wallentin — Wed, 25 Apr 2007 20:57:55 GMT
Abstract:
Components and tools
I’m a power user of several third-party components and tools like:
ComponentOne (Both ActiveX controls and .NET Components)
Iocomp (ActiveX controls)
Software FX (Chart FX Client Server ActiveX control)
UltraEdit32 (Text Editor)
In my experience, these components and tools have the following in common:
They are robust and rather easy to use.
Well documented with helpfile(s), examples and additional information at the vendor’s sites.
Good support with easy access and fast service.
Offer yearly subscriptions which give regular updated components.
The main reasons for me to use third-party components and tools are:
Cost effiency for my customers, i e less time is used in the development cycle and I can focus on the main task.
Makes me more competitive in bidding procedures.
Provide the customers with an attractive and end user friendly interface in my solutions.
Recently I have added a new tool to my toolbox - The Add-in Express 2007 for Microsoft .Net from Add-in Express Ltd. For the last weeks I’ve been working extensively with this tool and in my opinion it’s a great utility. It shares the common aspects with the other mentioned components and tools.
What is the Add-in Express .NET?
Add-in Express .NET is a developing tool for Visual Studio.NET to develop:
Office COM Add-ins (Excel 2000 and later)
Excel Automation Add-ins (Ex

Creating & Deploying Managed COM Add-ins with VSTO 2005 SE - Part V

Dennis Wallentin — Mon, 23 Apr 2007 22:46:28 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2007 and later.
Microsoft Visual Studio Tools for the Microsoft Office System 2005 Second Edition (VSTO 2005 SE)
.NET Framework 2.0 and later.
Database Comments
Database eLearning Introduction to databases and SQL.
VB/MySQL.com For VB-developers
Foxite MS Visual FoxPro
The Access Web FAQ for MS Access and more.
Tony´s Main Microsoft Access Page Tips & Tricks
SWUGG Articles and more for SQL Server, Oracle and DB2.
Carl Prothman.NET FAQ for ADO, ADO.NET, ADOX, connection strings to all kind of databases and more.
Prerequisites
In order to run VSTO created managed COM add-ins there are certain requirements, in terms of prequisites components, that need to be met. The basic components are the following:
.NET Framework
VSTO Runtime
Office 2003 and / or Office 2007
For Office 2003 SP-1 and later
It can be discussed whether all the prerequisites components, when missing on the targeting computers, should be part of the VSTO solution’s MSI (Microsoft Installer) file or not. The installation process starts to check if the target computer contains the prerequisites or not. If not, then the missing components are first installed and then as a final stage of the process the VSTO solution is installed.
Installing all the required components involves several installation steps and if anything goes wrong then it can be difficult to track down the error(s) and also to rollback the installation process, i e to restore the computer to its previously state. The alternative approaches we have to choose among are:
Install all the required components as stand

VSTO COM Add-ins series

Dennis Wallentin — Sun, 11 Mar 2007 16:16:09 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2007 and later.
Microsoft Visual Studio Tools for the Microsoft Office System 2005 Second Edition (VSTO 2005 SE)
.NET Framework 2.0 and later.
Creating and deploying VSTO COM add-ins

Here is all the articles, written by Dennis Wallentin, that are part of the series about creating and deploying VSTO COM add-ins with VB.NET 2005. The series has previously been published at XL-Dennis blog.
Introduction
Manipulation of Ribbon and customized images
All the procedures for the case
Deployment, prerequisites and Code Access Security (CAS)
Prerequisites
Code Access Security Policy
Deployment
MSFT has recently updated their articles about deployment of VSTO solutions:
Part I
Part II
It’s good that the articles have been updated to review Office 2007 and VSTO 2005 SE.

When it comes to deployment of managed VSTO created COM add-ins we have two alternatives to consider. The add-ins can be stored on the local hard disks or in network-accessible locations (although the later require some additional work).
If we develop general free and commercial add-ins the only option is to install them on the targeting computer’s disks. For custom specific solutions network-accessible locations in the i

Creating & Deploying Managed COM Add-ins with VSTO 2005 SE - Part VI

Dennis Wallentin — Sun, 11 Mar 2007 14:48:05 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2007 and later.
Microsoft Visual Studio Tools for the Microsoft Office System 2005 Second Edition (VSTO 2005 SE)
.NET Framework 2.0 and later.
The hard part - Code Access Security Policy

Code Access Security (CAS) is the most difficult and the most challenge part of creating workable VSTO setups. It’s also the most confusing part of .NET, especially for those of us that come from a pure VBA environment. I’m the first to admit that writing this post is also difficult due to the subject.
In order to fully understand it we need to have deeper knowledge about:
.NET Framework,
.NET Framework’s security model,
VSTO’s security model,
How to work with different kind of manifests and
How to work with the available tools to create and control the security part of setups.
This article only cover CAS as part of VSTO created managed COM Add-ins’s setups. For obvious reasons it’s not possible to cover it all and the blogpost should be considered as an introduction to VSTO’s security. Compared with workbook level solutions (workbooks and templates) the security aspect for managed COM Add-ins is, in my opinion, less complex.
For a general introduction about CAS please see the following online resources:

Send worksheets to several recipients

Dennis Wallentin — Sun, 18 Feb 2007 16:51:59 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2000 and later.
Lotus Notes 7.01 and later.
Task
I have been receiving some e-mails on how to send individual worksheets to several groups of recipients. In this example each worksheet contain the list of recipients to send the actual worksheet to. The case also includes a general error handling.

Code

Option Explicit

Sub Send_Sheets

Creating & Deploying Managed COM Add-ins with VSTO 2005 SE - Part III

Dennis Wallentin — Mon, 22 Jan 2007 00:12:19 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2007 and later.
Microsoft Visual Studio Tools for the Microsoft Office System 2005 Second Edition (VSTO 2005 SE)
.NET Framework 2.0 and later.
Task

Here in the third article about managed COM Add-ins created with VSTO 2005 SE focus is set on the procedures that do all the work:
Show the About Form
Show the Help file
Create the Chart Report
Create the Pivot Table Report
Create the Data Report
A common procedure that populate the target worksheet with data (by using classic ADO)

In general it's highly recommended to use error handling although the below code does not use it.
Code

The following procedures are included in one module by the name of modData:
General information

In general for less advanced COM add-ins created with classic VB deployment is not an issue. If there are any problems the online community has usually a good knowledge about it.

For COM add-ins created with VB.NET (Shared Add-in template) the situation may be different as there may be deployment issues related to .NET Framework, PIA/IA, and the targeting Office version(s). Compared with classic VB the VB.NET deployments issues may be more complex and difficult to solve but within the online community there exist a general experience and a common good knowledge about .NET based deployment.

When it comes to VSTO created managed COM add-ins the situation is total different situation then with the above cases. Microsoft has a high development pace for VSTO and for every new version most major known problems are solved but at the same time new problems are created.

We need to be aware of two important aspects:

Creating & Deploying Managed COM Add-ins with VSTO 2005 SE - Part II

Dennis Wallentin — Thu, 04 Jan 2007 19:17:44 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2007 and later.
Microsoft Visual Studio Tools for the Microsoft Office System 2500 Second Edition (VSTO 2005 SE).
.NET Framework 2.0 and later.
Task

Here in the second article about managed COM Add-ins created with VSTO 2005 SE focus is set on:
Add and customize a Ribbon control class
Customize the underlying Ribbon XML file
Add Ribbon support to the project:
1. Choose the command Project | Add Component...
2. Select the "Ribbon support" component in the dialog.

The added class module implements the IRibbonExtensibility which enables customization of the Ribbon user interface in a straightforward way. It includes some predefined procedures and variables which need to be modified in order to suite the project. The key part here is that it use a method RequestService (see link and code below) which allow the project to implement the extensibility to manipulate the Ribbon.

In addition, a "Ribbon" XML file is added to the project which has the same prefix name as the class module. The file contains a RIbbonX code skeleton that also needs to be modified to fit into the project.
Since the project uses customized icon images (png file format) the images need to be converted which is done in a separate class module (PictureConvert.vb - see the code below).

Andrew Whitechapel has publis

Creating & Deploying Managed COM Add-ins with VSTO 2005 SE - Part I

Dennis Wallentin — Wed, 03 Jan 2007 19:26:25 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2007 and later.
Microsoft Visual Studio Tools for the Microsoft Office System 2500 Second Edition (VSTO 2005 SE)
.NET Framework 2.0 and later
Introduction

VSTO 2005 SE was released in November 2006 and it provide us with the possibility to create application level add-ins, i e managed COM add-ins, for Excel 2003 and Excel 2007.
The following requirements must be met in order to create and distribute VSTO based add-ins:
Visual Studio 2005 Professional or higher or
Visual Studio 2005 Tools for Microsoft Office System (VSTO 2005) and
Visual Studio 2005 Tools for Microsoft Office System Second Edition (VSTO 2005 SE) and
.NET Framework 2.0 and later (also on the targeting computers)
VSTO 2005 SE runtime (on the targeting computers)
Excel 2003 (certain versions only) and Excel 2007 (all versions)
Special note:
Microsoft strongly recommend to not install VSTO 2005 SE on any computer that have more than one version of Microsoft Office installed.
#Registry entries
One important aspect is that we don’t need to shim the add-ins like we need to do with Shared Add-ins (developed with VB.NET) since VSTO use an add-in loader file.
The following (Swedish) screenshot shows the entry in the HKEY_CLASSES_ROOT section and under

Using IRibbonExtensibility''s type library with COM Add-ins

Dennis Wallentin — Fri, 22 Dec 2006 02:31:32 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2000 and later.
Microsoft Visual Basic 6.0 SP 6
Task

Please see the following blogpost at XL-Dennis blog:
Creating a standalone type library for IRibbonExtensibility
Using IRibbonExtensibility's type library with COM Add-ins

Code

Option Explicit

Creating and deploying COM Add-ins with VB.NET 2005:
Here is all the articles, written by Dennis Wallentin, that are part of the series about creating and deploying COM Add-ins with VB.NET 2005. The series has previously been published at Daily Dose of Excel during August 2006.

Articles:

Introduction
Connecting
The Notes Tool - Handling workbooks
The Notes Tool - Handling worksheets
The Notes Tool - Functions

Transfer Excel '07 data to SQL Server 2005 EE with ADO.NET

Dennis Wallentin — Tue, 19 Dec 2006 16:58:49 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2007 and later.
Microsoft SQL Server 2005 EE
Microsoft VB.NET 2005 and later

External reference(s):
ADO.NET 2.0 and later
Task
Retrieve data from several identical closed worksbooks in one directory and add the retrieved data to a SQL Server 2005 EE database.

Code

'Namespaces in use.
Imports System.Da

About ExcelKB.com

Dennis Wallentin — Tue, 19 Dec 2006 16:56:11 GMT
Abstract:
About ExcelKB^®.com
First of all, most welcome to ExcelKB.com and I hope you will find the site of interest.
The purpose with ExcelKB.com is to provide good practical solutions for MS Excel and to automate MS Excel as well as other softwares.
The aim is also to promote different kind of commercial add-ins & tools, especially the add-in SQL Tester, and XL-Dennis consultancy service.
The content of ExcelKB.com reflects the owner of XL-Dennis, Dennis Wallentin's, personal areas of interest on top of the more general knowledge and skillness in MS Excel et al.
Terms of use
You may use the content here at ExcelKB.com in all kind of solutions for free. You are also allowed to set up link(s) to ExcelKB.com as well as referring to individual KB-articles via different medias.
But you are not allowed to publish anything of the ExcelKB.com's content on other sites, including public and closed forums.
Dennis Wallentin
©right; All rights reserved 1989 - 2006 XL-Dennis
The site and its content are protected by Swedish law and International law and is under control of XL-Dennis.

XL-Dennis and ExcelKB.com are not associated with Microsoft Corporation.
ExcelKB^® is a trademark by XL-Dennis.

Add signature to outgoing e-mails

Dennis Wallentin — Tue, 19 Dec 2006 16:45:16 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2000 and later.
Lotus Notes 7.01 and later.
Task

The procedure create and send an e-mail by:
Retrieving the recipients (To, CC and BCC) from ranges in the active worksheet.
Adding the active worksheet as an attachment to the outgoing e-mail.
Adding a signature to the outgoing e-mail.

Code

Option Explicit

Creating and Deploying Managed COM add-ins with VB.NET 2005 - Part IX

Dennis Wallentin — Wed, 06 Dec 2006 11:57:39 GMT
Abstract:
Troubleshooting
This is by no mean an easy area and the available utilities place a high demand on the developers who need to use them. Therefore make sure You know at least how the tools work and what can be achieved with them.
As for troubleshooting code it’s a too large area to be covered here but in general the ’standardways’ for tracking down code issues should be applied togehter with the tools .NET offers.
Installing the FIX: Add-ins, smart documents, or smart tags that you create by using Microsoft Visual Studio 2005 do not run in Office
If the .NET Framework has been installed after that the Office suite has been installed then this fix must be installed.
Error Old Format or invalid type library
This error is raised when a) The method requires an LCID (local identifier) and b) You run an English version of Excel but the regional settings for the computer are configured for a non-English language. The KB-article gives some workarounds for it.
Don't get garbaged collected by the .NET runtime
One of the most frequent issues is that clicking on created buttons doesn't execute the underlying code after the first time click. The reason for it is that the button object has been collected and released by the Garbage Collector.
The best solution is to simple declare the object at the classmodule level like the following:
Private oBtn As Office.CommandBarButton

Add hyperlinks for e-mail addresses and URLs

Dennis Wallentin — Sat, 18 Nov 2006 17:30:25 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2000 and later.
Lotus Notes 4.5 and later.
Task

Add hyperlinks to e-mail address(es) and to URL(s) in outgoing e-mails with Lotus Notes.
Important:
When viewing a created e-mail in Lotus Notes the hyperlink(s) are not showed but they will be available for the recipients of the e-mails.

Code

Option Explicit
'In order to add attachment to e-mails in Lotus Notes.
Const
Task

Add customized images to toolbars for COM add-ins.
It exist two approaches to populate the button objects in toolbars with customized images. One is based on using a resource file and add the wanted images to it. In order to use this approach the images must be of the BMP format. The other one use a hidden form where Pictureboxes contain the customized images and then via code retrieve the images from these 'containers'.
As for the images themselves they need, at least, have the sizes of 16x16 pixels and 32x32 pixels and if they are intended to be included in resource files then use the colourdepth of 16.

As for using resource files it exist a major issue: To get the images transparent (see the first toolbar picture below)
If we would like to solve the transparency issue the following KB-article is useful:
How To Create a Transparent Picture For Office Commandbar Buttons
No matter which approach we use we still need to use the clipboard to 'transfer' the images from the sources to the buttons.

The below approaches can als

Send formatted data from a range or chart as part of an e-mail's body

Dennis Wallentin — Sat, 21 Oct 2006 15:43:51 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2000 and above.
Lotus Notes 4.x and above.
Task
Insert a pre-defined formatted range of data to be inserted as part of the bode in an outgoing e-mail.

Code

Option Explicit

'Function for finding the first top level window in the windows list
'that meet the criteria.
Task
Let the end-users select Excel-file(s) to be open.

Code

Option Explicit

Sub Select_Open_Files()
D

Customize Ribbon UI for managed COM Add-ins (VB.NET 2005)

Dennis Wallentin — Thu, 21 Sep 2006 20:09:11 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2007 and later.
Microsoft Visual Basic.NET.
Note: This example has been developed with VB.NET 2005, .NET Framework 2.0 and with the B2TR of Excel 2007.
Task
To make a managed COM add-in available by customizing the Ribbon UI in Excel 2007.

In the example a separate XML file is used (NotesRibbon.XML) but it's embedded in the project. It has the following content and structure:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab id="Notes.Tab" label="Notes Tool" visible="1">
<group id="Notes.Group" label="Notes Toolset" visible="1">
<button id="wbk"
label="Send Workbooks"
screentip="Send Workbooks with Lotus Notes."
&

Customize Ribbon UI for COM Add-ins (VB 6.0)

Dennis Wallentin — Thu, 21 Sep 2006 19:43:53 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2007 and later.
Microsoft Visual Basic 6.0 SP 6
Note: This sample have been developed and tested with the B2TR of Excel 2007
Task

To make a COM add-in available by customizing the Ribbon UI.

In the example a separate XML file is used (NotesRibbon.XML) which has the following content and structure:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab id="Notes.Tab" label="Notes Tool" visible="1">
<group id="Notes.Group" label="Notes Toolset" visible="1">
<button id="wbk"
label="Send Workbooks"
screentip="Send Workbooks with Lotus Notes."
getImage="GetImage"

Creating and Deploying Managed COM add-ins with VB.NET 2005 - Part VIII

Dennis Wallentin — Sat, 26 Aug 2006 17:00:33 GMT
Abstract:
Test and preparations
Before doing anything else we need to test the 'shimmed' managed COM add-in and the generated standard COM DLL. The simplest way is to first copy the 'shimmed' managed COM DLL file (together with the help file) to the folder where the generated COM DLL file is placed and then start to debug it.
When the COM SHIM Wizard has created the files it has also registered the generated standard COM DLL file on the developing machines. Since it's a standard COM DLL it uses the regsvr32.exe.
Creating the Installation Package
We can either add a Setup project to the Com Shim generated project or create a standalone Setup project. I prefer the later for clarity in the walkthrough. The Setup Wizard can be a good start, especially if we are inexperienced with .NET.
The most important aspect is to decide which files should be included in the Setup package. The following picture shows the minimal number of files for the NotesTool and as You can see a help file is also included:
Since the NotesLoader.dll is a standard COM DLL we need to set the Register properties for it to 'vsdrfCOMRelativePath'. As for the other files settings we can leave them as they are.
The following picture shows some additional entries we can add to the Setup package:

Don't forget to add the KB908002 to the prerequisities for the Setup project. When building the Setup the package will include the installation package for KB908002 but You'll need to install it separately a

Creating and Deploying Managed COM add-ins with VB.NET 2005 - Part V

Dennis Wallentin — Sat, 26 Aug 2006 16:51:10 GMT
Abstract:
For all code posted here I have intentionally tried to avoid a) the core .NET-approach and b) the use of classes. Instead I have tried to focus on clarity and to use a simple classic VB/VBA -approach.
Notes Tool - The Functions
Below is all the functions and subs that are in a standard module (Notes.vb).
'Namespaces used in the module:
Imports Domino 'To access the objects in the Domino class.
Imports Excel = Microsoft.Office.Interop.Excel 'To access Excel's objects.
Imports System.Windows.Forms 'To use the Messagebox objects.
Imports System.IO 'To handle files.
Imports System.Diagnostics 'Open URL and show helpfile (.CHM)
Module NotesMod
'Friend makes the variables available for the whole project.
Friend Const g_CONST_TITLE As String = "Send e-mail with Lotus Notes"
'Excel variables.
Friend g_xlApp As Excel.Application
Friend g_xlRng As Excel.Range
Friend g_xlcell As Excel.Range
Friend g_xlwsSheet As Excel.Worksheet
Frien

Creating and Deploying Managed COM add-ins with VB.NET 2005 - Part VII

Dennis Wallentin — Sat, 26 Aug 2006 14:03:22 GMT
Abstract:
Strong Names and Digital codesign
In general I’m an advocator of software’s security and therefore I see it as important to dedicate a post on security when it comes to COM add-ins as well. For a general introduction to Office 2003 security please see the following URL: Security
Using Strong Name Signatures for 'shimmed' managed COM add-ins
I started to make a write up on the subject but had difficulties to keep the focus on the essential parts in view of the subject for the series. I then recalled that there recently was an article published in the MSDN Magazine and I was glad when I also found it on the web. The article is good in my opinion and it’s worth reading the whole article, CLR Inside Out - Using Strong Name Signatures. At the bottom of the article Mike Downen also brings up Windows Authenticode.
Digital sign generated standard COM DLLs
This can be applied for both standard COM add-ins, for instance developed with VB 6.0, and generated standard COM DLLs with the COM Shim Wizard.
In order to digital sign DLLs we need a certificate to use which can be bought from a security company like Thawte or Verisign . I use Thawte and I’m both pleased with their certificate and with their service.
To sign a DLL we need to use the SignTool.exe in the following way:
a. Locate the SignTool.exe file on the drive
(one location can be c:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin)<

Creating and deploying Managed COM add-ins with VB.NET 2005 - Part IV

Dennis Wallentin — Sat, 26 Aug 2006 12:32:33 GMT
Abstract:
For all code posted here I have intentionally tried to avoid a) the core .NET-approach and b) the use of classes. Instead I have tried to focus on clarity and to use a simple classic VB/VBA -approach.
The Notes Tool - Handle worksheets
The way the tool handles worksheets is similar to how it handles workbooks. Therefore only code behind the form that is unique for worksheets is presented. Below is the form that that provides end users with an interface to populate required parameters:

The code
Make sure that the following namespaces exist in the form:
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Windows.Forms
Imports System.IO 'To handle files.

The following snippet code is part of the Load event for the form:

'Populate the listbox of open workbooks.
For Each g_xlwbBook In g_xlApp.Workbooks
Me.ListBox_Workbooks.Items.Add(g_xlwbBook.Name)
Next

The below event is used to populate the list of worksheets:
How To Build an Office COM Add-in by Using Visual Basic .NET
The wizard also provide a step to determine the loadbehavior where load on startup of Excel is the default value, i e the value of 3 (1 = Connect + 2 = Bootload (Load on Excel startup).
Debug configuration
Before starting to debug we must make sure that we use Excel by changing the default setting of 'Start external program' under the Debug Tab and point to the Excel.exe file. The easiest way to do it is to right click on 'My project' in the Solution Explorer windows and then select the Debug tab.
The code for connection to Excel and creating the commandbar
Below is the code in the 'Connect'-class module where only the vital parts are showed:
'Make sure that all the wanted references have been set up before doing the imports.
Imports Excel = Microsoft.Office.Interop.Excel

Creating and Deploying Managed COM add-ins with VBV.NET 2005 - Part I

Dennis Wallentin — Sat, 26 Aug 2006 12:07:20 GMT
Abstract:
I thought it would be of common interest to take a closer look into what VB.NET 2005 can offer when it comes to creating and deploying COM add-ins for Excel.
In order to create a useful tool I decided to challenge how to work with the Domino object class by using early binding to control Lotus Notes. The tool itself will make it possible to attach workbooks or worksheets to outgoing e-mails with Lotus Notes.
What does ‘Managed’ stand for?
All solutions that require the Common Language Runtime (CLR - part of the .NET Framework) are considered managed, which include all solutions developed with any tool in the Visual Studio.NET suite and with Visual Studio Tools for Office System (VSTO). Solutions developed with classic VB 6.0 and Excel’s VBA are unmanaged solutions.
Since Excel per se is an unmanaged environment we are, from a strictly technical point of view, about to create a managed solution which is supposed to be implemented on an unmanaged platform.
Anyway, for those of You who want to know more about the CLR the following URL provides a good introduction: The Common Language Runtime
Required tools and configuration
In order to create the COM add-in the following tools are required:
Microsoft Excel 2003 and later.
The Primary Interop Assemblies (PIA) for Microsoft Office 2003 and later.
Lotus Notes 7.01 and later in order to use the sample tool.
Microsoft Visual Studio.NET 2005 and later, which include the template 'Shared Add-in'.
Microsoft .NET Framework 2.0 and later.
Two important settings in VB.NET are:
Option Explicit On (Status by default)
Option Strict On (Not by default which force explicit nar

Creating and deploying Managed COM add-ins with VB.NET 2005 - Part III

Dennis Wallentin — Sat, 26 Aug 2006 12:06:56 GMT
Abstract:
For all code posted here I have intentionally tried to avoid a) the core .NET-approach and b) the use of classes. Instead I have tried to focus on clarity and to use a simple classic VB/VBA -approach.
The Notes Tool - Handle workbooks
With this post I will start to cover the tool itself which target on sending workbooks or worksheets as attachments to outgoing e-mails with Lotus Notes. The first option is to send workbooks as attachments and below is the form that provides end users with an interface to populate required parameters:

The code
The following code is part of the above form where only the most vital parts are showed.
'The following namesspaces are required for the code behind the form.
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Windows.Forms
When the form is initialized the following procedure populates some items of the form:
Private Sub frmwbook_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
'Create the collection of items for the priority status.
'I have not discovered any smooth way to align items in a combobox...

Where does Windows Registry store the information about Add-ins?

Dennis Wallentin — Sat, 26 Aug 2006 12:05:03 GMT
Abstract:
The information in this article applies to:
Microsoft Excel 2000 and later.
Since Microsoft Excel can handle several different types of add-ins the best approach to try to answer the question is to use these categories.
In general add-ins can be divided into the following categories:
• XLA Add-ins (Add-ins created with Excel 2007 have the file extension XLAM)
• XLL Add-ins
• Automation Add-ins (available from version 2002 and on forward)
• COM Add-ins for Excel's user interface
• COM Add-ins for the VB-editor
XLAs
XLAs are easy to locate in Windows registry as they only appear in two places.

If they are available and activated Windows stores the information at the following place: HKEY_CURRENT_USER\Software\Microsoft\Office\<Version number>\Excel\Options
If they are available but deactivated Windows stores the information at the following place:
HKEY_CURRENT_USER\Software\Microsoft\Office\<Versionnumber>\Excel\Add-in Manager
"Version number" refer here to the internal number version of Excel like 9.0, 10.0, and 11.0.
The XLAs, that are shipped with Microsoft Excel, is not located in the Add-in Manager section when deactivated instead they can be found in the HKEY_CLASSES_ROOT like the following example:
HKEY_CLASSES_ROOT\Installer\Components\C848331AADAA4D11298D0001A58916F6
XLLs
Despite the technical differences between XLAs and XLLs Windows registry store the information about XLLs in an identical way as for the XLAs.
Automation Add-ins
Compared with XLAs and XL

Restore the Help Viewer

Dennis Wallentin — Sun, 11 Jun 2006 15:57:35 GMT
Abstract:
The information in this article applies to:
Microsoft Visual Studio.NET 2005 and the local Document Help Explorer.
Background

When I upgraded to 2005 (a clean installation) I got a problem to view the content of the local document helper. Whenever I tried to access it I received the message "Cannot find the server" and I was only able to get the search function to work.
When I was logged in under a different account I accidentaly started Visual Studio and the integrated Help worked as expected. Further investigation led me to the folder Temporary Internet Files.

Actions

1. Logoff from the account where the issue exist.
2. Login to another account which must be of the type Computer Administrator.
3. Locate the folder Temporary Internet Files for the account in step 1.
4. Delete the folder (Windows will create a new folder).
5. Logoff from the account in step 2.
6. Login to the account in step 1.
7. That's it!

Comments
The above solution works for my production computer and hopefully it can solve other developer's similar issue as well.
&nbs

Tips & Tricks for MS Excel © 1989 - 2009 XL-Dennis

MS Excel sites

Online resources for VSTO

Professional Excel Development (PED) Second Edition

SQL Tester

SQL Tester The tool for retrieving data from databases and putting it into MS Excel

Books on VBA / VB / VB.NET & ADO / ADO.NET & VSTO

A Collection of useful Excel articles

Export SQL Server data to a worksheet

Export Access-data to a worksheet

.NET Co Library

Export MySQL-data to a worksheet

Retrieve e-mailaddresses from a Notes database

MS Excel Tools & Add-ins

Use The OWC Spreadsheet in MS VB 6.0

Create connection strings to databases

Creating and Deploying Managed COM add-ins with VB.NET 2005 - Part VI

Database sites

Add-in Express.NET for Microsoft .NET

Creating & Deploying Managed COM Add-ins with VSTO 2005 SE - Part V

VSTO COM Add-ins series

Creating & Deploying Managed COM Add-ins with VSTO 2005 SE - Part VI

Send worksheets to several recipients

Creating & Deploying Managed COM Add-ins with VSTO 2005 SE - Part III

Creating & Deploying Managed COM Add-ins with VSTO 2005 SE - Part II

Creating & Deploying Managed COM Add-ins with VSTO 2005 SE - Part I

Using IRibbonExtensibility''s type library with COM Add-ins

Transfer Excel '07 data to SQL Server 2005 EE with ADO.NET

About ExcelKB.com

Add signature to outgoing e-mails

Creating and Deploying Managed COM add-ins with VB.NET 2005 - Part IX

Add hyperlinks for e-mail addresses and URLs

Send formatted data from a range or chart as part of an e-mail's body

Customize Ribbon UI for managed COM Add-ins (VB.NET 2005)

Customize Ribbon UI for COM Add-ins (VB 6.0)

Creating and Deploying Managed COM add-ins with VB.NET 2005 - Part VIII

Creating and Deploying Managed COM add-ins with VB.NET 2005 - Part V

Creating and Deploying Managed COM add-ins with VB.NET 2005 - Part VII

Creating and deploying Managed COM add-ins with VB.NET 2005 - Part IV

Creating and Deploying Managed COM add-ins with VBV.NET 2005 - Part I

Creating and deploying Managed COM add-ins with VB.NET 2005 - Part III

Where does Windows Registry store the information about Add-ins?

Restore the Help Viewer

SQL Tester
The tool for retrieving data from databases and putting it into MS Excel