Access running instances of Excel in VB

When automating Excel from VB we can either create new instances or access already running instances. In different scenarios different methods may be needed in order to get workable solutions.

In the first scenario we may need to access a running instance and if not we need to create a new one as the following sample shows:

This is a rather simple method but it does the work and we can handle the situation if any error is raised.

In the second case we use a slightly different approach as the below code shows. The System.Diagnostic.Process class represents a Windows process. The sample uses the GetProcessesByName property to check if any Excel session is running or not. This approach is less ‘brutal’ then the above and offer also more options as it use the System.Diagnostic.Process (see also the other samples below):
 

In the third scenario another approach is used which is based on BindToMoniker. Unlike the above methods it works ‘botttom up’ (in the Excel’s object model) as it gets an interface pointer identified by the specified moniker, here the workbook file. The method provides the same functionality as the GetObject method in VB 6.    

In some scenarios we may want to start a new Excel session, allow the users to work with one or more workbooks and finally end the process. The following sample shows how we can achieve it:
 

Remark:
I have not tested how it works when using the Dual Core Processor and instantiate new Windows processes.

The final sample shows how we can get information for all running Excel instances with the System.Diagnostic.Process class:

I have made the class module available as a textfile which can viewed or saved from here. 

Kind regards,
Dennis

Open Excel workbooks in VB.NET Solutions

In some forums I frequent (more precisely lurking around) the question “How can we open Excel workbooks in .NET solutions?” is raised from time to time. Usually it does not refer to how to read data from closed Excel workbooks rather how to open and work with workbooks in VB.NET and in a similar manner as we can do in Excel.

The possible approaches, at least for me, to answer the question are the following:

• Using the .NET WebBrowser Control
• Using the Office Framer Control
• Using the Spreadsheet Component in Office Web Components (OWC)
• Using FarPoint Spread for Windows Forms

Which approach to use is due to how much access the users should have to Excel’s built-in features and how much to work with the data. In some cases as little as possible while in other scenarios as much as possible. To say which approach is the best to use is subject to the actual situation and what the components actually can do.

.NET WebBrowser Control
Using the the .NET WebBrowser Control is similar but not identical to open Excel workbooks through Internet Explorer. It requires that Excel is available in order to open the workbooks.

The following case is a simple example. The below image shows the output:

The following code is used to achieve the above:

The major drawback with this component is that we don’t have access to the Worksheet menu bar and that we need to write code to access functions in the other commandbars. The right click menu in the worksheet space is available when using this control. 

To create a similar solution with classic VB please see (please note that it does not work with Excel 2007):
How to use the WebBrowser control in Visual Basic to open an Office document  

Office Framer Control (aka DsoFramer Control)
MSFT has made available an ActiveX control for hosting Office documents. The KB Article Visual C++ ActiveX Control for hosting Office documents in Visual Basic or HTML provides us with additional information about it and a link to download it.  This control also requires that Excel is available in order to open workbooks.

The package that is available for download includes samples for classic VB, VB.NET and the Web. Since the source files are included we can change the code and compile our own version(s) of it.

Instead of creating my own sample I here use the included VB.NET sample. The following screenshot shows how Excel 2007 looks like (I prefer to have the Ribbon minimized and also to have the formula bar in hidden mode):

Compared with .NET WebControl the Worksheet menu bar is available and it can also handle several open Excel workbooks. The right click menu in the worksheet space is also available with this control. 

To enable Registration-Free COM for the Office Framer Control within the Visual Studio .NET 2005 project, simply right-click the unmanaged project reference, select Properties, and then on the properties panel for the reference, set “Isolated” to “True”. This will tell Visual Studio to generate a COM registration manifest for the application with the information necessary to activate and launch the unmanaged control. 

Spreadsheet Component
This component does not requires that Excel is available. On the other hand we cannot require that it will be a full substitute for Excel.

It offer some basic functions and therefore the component is a good candidate to only show data from Excel workbooks or when the users may need to change a limited amount of data. One native good tool is the AutoFilter.

The case I have set up here is very simple. The following image shows how we can manually set the data source (here a workbook with 2003 XML Spreadsheet file format is used) for a worksheet in design mode: 

The next image shows how it looks when we are using the spreadsheet component. It also shows that we can edit functions in run mode: 

For an introduction to the component with classic VB please see:
Introduction to the OWC Spreadsheet Control
Use The OWC Spreadsheet in MS VB 6.0
The Spreadsheet Control as datasource to the Chart Control

FairPoint Spread for Windows Forms
This is the ultimated .NET spreadsheet component. FarPoint Spread does not require having Excel available but can open and work with native Excel workbooks. 

In my opinion its simple the best component for spreadsheeting outside Excel and other dedicated spreadsheet tools (which is also reflected in the price list). For more information please see FarPoint Spread where you also can download a trial version of it. 

To sum up
- It exist two types of controls that we can use. Those controls that requires Excel to be installed and controls without this requirement.
- Among the two controls that require Excel to be installed the Office Frame Control is the best option.
- Among the two other controls the FarPoint Spread is the best control. But since it’s a very expensive the Spreadsheet Component may therefore be more attractive (free).

Finally, it’s the actual business needs that stipulate which control to use and not.

Kind regards,
Dennis

Office Open XML - OOXML

One of the items on my To-do list is to learn more about RegExpression. Another item is Office Open XML (OOXML). Whenever I decide to teach me more about a specific topic I usually start to “google” to locate relevant online resources. If the subject is very interesting I also try to locate books on the subject as I still like to read printed books.

The file format XML has been in focus for the last couple of years. The main reason is due to its platforms independence. Of course, whenever a “new kid is in town” it turns quickly to be a buzzword and XML is no exception.

In order to understand and to work with Office Open XML it requires that we got the basic knowledge of XML.

Open XML has been widely discussed for the last couple of months. Mainly because of MSFT’s intention to make it to an ISO standard file format. Except for that, it’s the XML file format to work with MS Office’s files (with and without MS Office suite) which also explain why it’s named as Office Open XML or for short OOXML. 

In the end of 2005 I read the book “Office 2003 XML” from O’Reilly which helped me to get a kick start with what Excel 2003 can do. Important to know is that it’s not the same file format which Excel 2007 use but Excel 2007 can read the older XML file format.

Most Office users view the Office suite as tool set to get the work done. Since this is the primarily task with Office it’s difficult to disagree on that. But when discussing the OOXML file format we need to view Office as general-purpose interfaces to information.

OOXML files can be created without Excel and other platforms like Visual Studio.NET, SQL Server, SharePoint Server et al as well as other non-MSFT platforms & tools can read and update OOXML files. In this context the real power of the OOXML file format is exposed and the OOXML files become more of ‘containers’ for information then just Excel’s files.

Before moving on with the online resources I would like to highlight Stephane Rodriquez excellent work. He is ‘the man’ when it comes to Excel’s file formats and I’m very impressed by his strong commitment to the field. 

Check out the following excellent work by Stephane:

• OOXML is defective by design (article)
• Office 2007 .bin file format (article)
• xlsgen - Home of Stephane’s commercial tool ‘xlsgen’ and it also contains all (?) information about Excel’s object model COM interface.

At present I have found the following online resources for OOXML to be of high interest:

• Wikipedia - A general good starting point and easy introduction.
• OpenXMLDeveloper.org - The home of OOXML
• Open XML Explained - A PDF file of the book that gives a basic introduction to OOXML 
• Brian Jones: Open XML Formats  - The SpreadsheetML section

I will  get back on the subject next year but right now I just want to learn more about it. 

Does anyone else have information about additional useful online resources and perhaps about other books about OOXML? 

Kind regards,
Dennis

.NET Co Library - Part V

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

• Part I
• Part II
• Part III
• Part IV

Based on input from some testers I decided to implement a dedicated Windows form to only insert code into modules with. However, all other ideas and suggestions may only be considered to be implemented in future updates of .NET Co Library.

Insert code
In one of the previously blogpost (.NET Co Library - Part II) the main form for managing code (delete, update and insert code) was presented. Now I have added a new option to insert code in present code modules and also to add code to new added code modules (class and general modules).

The following image shows the new added options in the menus:

Based on which main group that is selected the Windows form is populated with the related subgroups. The following image shows the form when “Insert Snippet Code” has been selected:

Since I have had (and still have) issues with making Windows forms modeless in the VB Editor I have solved it by setting its property TopMost to True (Actually, the problem is that Excel crash when closing it due to the modeless status). The drawback with this solution is that the Windows Forms always stay at the front even if Excel is not the active program.

It always seems to be a conflict between design of the UI and the wanted functions. In my experience there is also necessary to make a compromise in order to get a workable solution. In this context I have made it possible to expand each row so the whole code for an entry can be viewed by clicking on the row in the Code column. But in order to restore the row heights I was forced to add a button (Reset Row height) which reset all rows height. The following image shows expanded code rows:

Another “issue” to decide on was if only single entries or multiply entries can be selected. I decided to go with the later as the following image shows:

The final aspect I considered was to how to solve if there is a larger group of entries how could we locate the wanted code in an easy way. I solved it by using a built-in Filter functions in the grid. It use the extra row below the fieldnames as the following image tries to show:

Finally, the Export button export all code to a RTF file. At present I’m pleased with it but I may in an upcoming update make it possible to export selected code to a RTF file.

That’s it! I will now set focus to write the complete helpfile for this utility.

Kind regards,
Dennis

UAC Compliant

UAC stands for User Account Control and is the new technology in Windows Vista to provide users with different level of administrative rights and privileges. UAC main purpose is to support a more secured environment then what Windows XP offers.

Microsoft has a good white paper that covers UAC in detail and therefore I have no intention to cover it here:

• The Windows Vista and Windows Server 2008 Developer Story: Windows Vista Application Development Requirements for User Account Control (UAC)

Most developers I know have intentionally disabled UAC because they found it to be rather annoying, time consuming and too restrictive.

Although I can agree with these opinions I try to have it enable as much as possible simple because that will be the most likely scenario for many of my customers. In addition, from a general point of view I support it because by default all users (except Guests) are logged on to Windows Vista as standard users and get extended rights only when needed.

Since we can disable the UAC we may believe that we can solve most issues when installing different kind of softwares. However, in my own experience it may or may not be true.

In my case, I got some old ActiveX controls and .NET controls where they are either no longer updated or my subscriptions have expired and I have not renewed them. The errors I have faced when installing these controls as well as older softwares are:

Error 2738
This error occurs when the installation package needs to run a VBScript file and it cannot find the vbscript.dll. The error message is something like “The installation script shipped with the package needs access to an earlier version of VBScript”. In order to resolve it we need to locate the vbscript.dll on the targeting computer and registrate it with regsvr32.exe. But if Windows Script Host (WSH) is not available on the targeting computer then it must be installed prior installation of the wanted softwares. This issue is not related to whether UAC is disabled or not.

Speaking about Script Languages and Tools check out the Script Center at Microsoft.

Error 1720
The error message that popups during the installation is like “There is a problem with this Windows Installer package.” The only workable solution in this case is simple to disable UAC, install the software, and finally enable UAC again.

Of course, the best would be if the vendors can provide us with so called UAC compatibility applications.

Windows Vista and UAC Compatibility Applications
The above referenced white paper includes a stepwise process to analyze and to create Windows Vista and UAC compatibility applications. It also includes links to analyze tools which are useful to test if applications meet the requirements.

For my MSI (Microsoft Installer Packages) with customized add-ins and solutions this is too advanced. Honestly, I did find the last chapters of the white paper to be “far over my head”. 

Anyway, the key question is the same: 

• How can we make our MSI packages UAC compliant?

Included in the latest Windows SDK for Windows Vista is a new version of the Orca tool which makes it rather easy:

• Start Orca
• Open the wanted MSI file
• Select the command View | Summary Information…
• Check the option UAC Compliant as the following screenshot shows (and then save the updated MSI file):

If you only want to use this new version of the Orca tool and not install the whole SDK you may download it from here. 

Of course, my own favorite tool for creating managed add-ins  - Add-in Express .NET - has also this possibility as the following image shows:

As You note, there also exist an option to make the setup compatible with standard users. This is convenient as we, when using it, don’t need to test if the setup will work with standard users or not. A setup that target this group of users do not place anything under the Program section or in any other section that standard users do not have elevated right to access

All in all, now I got a working main development platform where Windows Vista is the operating system.

Kind regards,
Dennis

.NET Co Library - Part IV

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

• Part I
• Part II
• Part III

Here in this post focus is set to deployment and known issues.

Deployment
Requirements
Using one of the following Microsoft Windows versions:

• Windows 2000 SP-4 and later
• Windows XP SP-2 and later
• Windows Vista and later

MS Excel
Using MS Excel 2000 with SP-3 or later versions.

.NET Framework
Prior installation of .NET Co Library .NET Framework 2.0 and later versions must be installed. If you are using Windows Vista then .NET Framework 3.0 is already installed.

MS Installer
If running Windows 2000 then MS Installer 3.1 or later version must be installed prior installing .NET Co Library. Later versions of Windows are shipped with MS Installer 3.1 and later.

MS Data Access Components (MDAC)
If running Windows 2000 then MDAC 2.6 or later version must be installed prior installation of .NET Co Library.

PIAs
.NET Co Library is shipped with version neutral IAs so .NET Co Library does not requires that any PIAs are available.

Trust Access to Visual Basic Project
In MS Excel the option “Trust Access to Visual Basic Project” must be checked
in order to use .NET Co Library.

Selecting deployment model based on the targeting group of users
In my opinion we can use different approaches to deploy solutions based on the primarily targeting groups for tools like .NET Co Library. I usually use the following categories although it’s not so easy to define them:

• End Users 
  This group should be supported well so they can start to use the tools immediately without any issues. It means that any prerequisites should be included in the installation package, well writen helpfiles and that as much as possible are done with automation.
• Power Users
  This group should also be supported in a similar way as with the group of End Users but we can require that they can make some more ‘advanced’ configuration settings. Other requirements can include being able to check out that their platform(s) can meet specific requirements before installing tools. It also can include to downloading and installing prerequisites from Internet and from other medias.
• Unofficially VBA-Developers
  This group is quite similar to the group of Power Users and should require the same support. The difference between these two groups is that VBA-developers develop solutions where VBA is included to a higher degree then in solutions from the group of Power Users.
• Professional VBA-Developers
  This group includes external consultants and officially VBA developers in corporate. Compared with other groups this group tends to want more information on how deployment processes effect their computers and settings. The helpfile should include a list of all files and their location on the computers as well as links or references to more technical information (if necessary). On the other hand we should require that they can do error trapping and make their own decisions when it comes to install hotfixes etc. 
  In my own experience I know that members of this group tend to skip to read the helpfiles.

Since .NET Co Library targets primarily the groups of unofficially/officially VBA-developers I have excluded any requirement’s checks during installation of .NET Co Library. It also means that any prerequisites are not part of the installation package. (In addition, by excluding the prerequisites it does not matter if the setup.exe is executed or the MSI package.)

Speaking about helpfiles, I will make the file for .NET Co Library available in the same location as the installation package will be available for download. It enables everyone to read the helpfile before installing the tool. 

Installation
In order to install .NET Co Library it requires administrative rights in Windows 2000, Windows XP and in Windows Vista.

Except for the Access db all files are per default installed in the main folder c:\Program Files\XL-Dennis\NET Co Library (which can be changed during the installation process). The following screenshot shows the list of files being installed:

The Access db is installed in the following locations:

• Windows 2000 and Windows XP:
  c:\Documents and Settings\UserName\My Documents\NET Co Library
• Windows Vista:
  c:\Users\UserName\Documents\NET Co Library

Why is the Access database separated from the other files? The answer is two folded:

• Write rights in Windows Vista exclude the whole Program Files section.
• Easy to locate, for instance when opening it in MS Access. In general the user’s application data folder (AppData) is recommended to place user’s data in but since this folder is usually in hidden mode the Access db is more available in other folders.

Known Issues
In general it is always recommended to make sure that you run the latest service packs for Windows, the Office suite and for .NET Framework.

The most known issue is the KB908002. If you run Windows 2000 or Windows XP with Office 2003 or earlier version of Office then this issue will probably appear. When Excel is launched an error message is showed with some ‘geeks’ information.

A close known issue to KB908002 is KB907417. It can be installed in a similar way as with KB908002. In my experience the KB908002 usually fix any issues with managed COM Add-ins so KB907417 should only be installed after KB908002 fix has been installed and tested with a negative outcome.

When installing .NET Co Library a subfolder (’Hotfix’) is created under the main folder which includes subfolders for each above ‘KB issues’. 

In my opinion the above approach support a better stepwise troubleshooting then ‘just in case’ install the KB fixes. 

Last words
All in all, nowadays I find it rather easy to work with managed COM Add-ins and with the deployment processes. But I can understand that other find it to be a rather complex area and far away from the ‘one Excel file installation’.

Now I will finalize the helpfile for .NET Co Library and collect the last comments from individuals who have been tested it.

Kind regards,
Dennis 

.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

Providing Form-Based help for Managed COM Add-ins

All my managed COM Add-ins solutions extensively use Windows forms. With .NET it’s very easy to create help that are directly associated with Windows forms which I hope this blogpost also will show.

For all my solutions I use complete helpfiles (.chm) created and compiled with RoboHelp  which is also used here. A compiled helpfile consists of several HTML files and the common term for these files is ”topics”. Usually I place the helpfiles in the same directory as the solutions are placed in.

Basically Form-based help means that a specific topic is associated with a specific Windows form.

The following screenshot is from RoboHelp and shows the list of topics for a help project. For the case here I will be using the topic “Move Database” (its filename is Move_database.htm).

In order to add Form-based help we need to add an instance of the HelpProvider to the Windows form. Like the ErrorProvider and other providers the HelpProvider is added to the Windows form’s component tray as the following screenshot shows:

When adding the component to the Windows forms five new properties are added to the form’s properties:

• Help button:
  True if we want to add a help button to the Windows form’s caption bar to the right.
• HelpKeyword on HelpProvider:
  Determines the Help keyword associated to the form or to the control. Here we refer to the topic filename as the below screenshot shows.
• HelpNavigator on HelpProvider:
  Determines the kind of Help associated with the form or the control. Here we set it to Topic.
• HelpString on HelpProvider:
  Determines the Help string associated with the form or the control. Here we leave it empty.
• ShowHelp on HelpProvider:
  Determines if help is available or not for the form or for the control. This property should always be set to True. 

The following screenshot shows the first four properties’s settings:

When the Windows form has been loaded and has the focus the HelpProvider component starts to monitor the Windows form. When the end users press the F1 key the named topic is loaded in its own window. 

In the property window for the HelpProvider we also need to add the filename to the HelpNamespace:

What should be notable is the following:

• No code is used to get the above solution.
• In .NET we can refer to topics and are not forced to use TopicIDs.
• No code or any settings are used to hook the help to the F1 key.
  It can be discussed whether the use of F1 key is the best approach or not. A help button can be a better solution under some other conditions.
• For Windows forms that have multiply purposes the Form-based help approach should be replaced with a Control-based approach.

To see the final result of the above please see helpaction.png

Kind regards,
Dennis