Discover COM. Realtime feed for Excel

• Download source files - 277 Kb
• Download demo project - 754 Kb

Introduction

COM add-ins present the developer with a way to extend the functionality of Office 2000 and Office XP applications for custom tasks. COM add-ins are typically used to automate Excel in response to a click of a CommandBar button, a form or dialog box, or some other event specific to Excel such as opening or closing workbooks or entering data on worksheets.

Microsoft Office 2000 and later support a new, uniform design architecture for building application add-ins to enhance and control Office applications. These add-ins are called COM add-Ins. This article will demonstrate how to build a COM Add-In using Visual Basic.

In Microsoft Excel 2000, you cannot call a function directly in a Component Object Model (COM) add-in from a worksheet cell formula. Instead, you must create a Visual Basic for Applications (VBA) wrapper for the COM Add-in function so that the function can be called indirectly. Integration of COM add-ins (called automation add-ins) in Excel 2002 has been enhanced such that COM add-in functions may now be called directly from a worksheet formula without the need for a VBA wrapper.

We want to use the power of Excel to visualize historical and online data from some tables in Excel cells and graphics. In that way, we will be possible to compute online a lot of column, to add calculations, to add graphs and computing fields, to look online at graphics changes. Using the Excel, all data will be kept in your worksheet!

Because the transferring of that data from SQL tables to Excel client is completed over the Internet, I choose the ISAPI extension DLL to be the HTTP provider of feed. In fact, the Excel COM add-in will make requests at equal time intervals to the ISAPI extension DLL. The ISAPI will interrogate the SQL database, will format the data and will put that data to the HTTP stream. The Excel COM add-in will receive that formatted data and will fill its cells and graphs with updated values!

Advantages

• Obtain directly the data from the server database in your Excel worksheet. No web pages or download files are needed !!!
• In that way, it is possible to implement in your worksheet more calculations, computed fields, graphs based on primary data.
• It is possible to easy distribute the ExcelFeed client program to your clients.
• Using the ISAPI the web server is freed by the calls from clients.
• ISAPI extension DLLs are the best choice if you want to obtain the maximum number of simultaneous connections to IIS web server or to provide huge quantities of HTML data.

Functionality

In order to start the client ExcelFeed application, launch the installation Setup.exe program and follow the wizard. After a successful installation, open the Excel program. You will find a new "Excel Real Feed" toolbar inside!

Click on the "RealFeed" button of the Excel Real Feed menu. A new book will be opened and the "Login" option will be activated. Click on the "Login" button of the Excel Real Feed menu.

The "Main switchboard" dialog box will appear. Choose a country provider from the "Country" combo box, put user "andi" and password "andi" and click on the "Login" button. The "GetIndices" and "GetTickers" buttons will be activated.

After a click to "GetIndices" button, the "IndicesHistory" dialog box will appear. The Tickers module and functionality are the same like that with indices. On the left side of the dialog box is the History module. After a click on "GetIndicesHistory", in the worksheet will appear historical data for IdIndex. Also a history graph is provided.

On the right side of dialog box is the Portfolio module. After a click on "GetIndicesPortfolio", in the worksheet will appear online data for all indices. Here it is possible to obtain 3 types of graphs: the pie graph and intraday graph are in the same worksheet. An image sample is available on the top of this article.

The historical intraday graphs are provided for all indices in a separate worksheet. If we click on the "Lauch Real Feed" checkbox on the IndicesHistory or TickersHistory forms, the program will look 30 min at each 60s at the GetOnlineIndices method of the ISAPI server.

http://localhost/ArticleExcelFeed/IsapiExcelFeed.dll?GetOnlineIndices?

Developing

A COM add-in is an in-process COM server (an ActiveX DLL) that must implement the IDTExensibility2 interface. All COM add-ins must implement each of the five methods of this interface: OnConnection, OnStartupComplete, OnAddinsUpdate, OnBeginShutDown, and OnDisconnection.

When a COM add-in is installed on a user's system, registry entries are created for the add-in. In addition to normal COM registration, a COM add-in is registered for each Office application in which it runs. COM add-ins used by Excel are registered in the following registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\

This key contains a sub-key for each installed COM add-in. The name of the sub-key is the ProgID for the COM add-in. The sub-key for a COM add-in also contains values that describe the COM add-in's friendly name, description, and load behavior. The load behavior describes how the add-in is loaded in Excel: loaded at startup, loaded at next startup only, loaded on demand, or not loaded.

COM add-ins can also be loaded and unloaded through the Excel user interface. To do this, follow these steps:

1. On the View menu, point to Toolbars and then click Customize.
2. In the Toolbars dialog box, click the Advanced tab. In the list of categories, select Tools. Locate COM Add-ins in the list of commands and drag the command to a menu or CommandBar of your choice. Close the Toolbars dialog box.
3. Click the COM Add-ins command that you added to display the COM Add-ins dialog box. The dialog box lists all COM add-ins that are installed on your system, and the COM add-ins that are currently loaded are selected.

Steps to cCreate the COM add-in

1. Start a new AddIn project in Visual Basic 6.0.
2. Add a reference to Microsoft Office 9.0 and Microsoft Excel 9.0 object libraries.
3. On the Project Explorer, open the Forms folder, and then remove frmAddin from the project.
4. On the Project Explorer, open the Designers folder, and then double-click the Connect Addin Designer.
5. On the General tab of the designer, change the Application to Microsoft Excel, and then change the Initial Load Behavior to Startup.
6. On the View menu, click Code.
7. Replace all of the code in the Connect code module with your code (see the sample VB project).
8. On the File menu, click Make MyAddIn.dll to build the COM add-in.

The COM add-in is registered for use with Microsoft Excel when you build it. Since you specified Startup for the Initial Load Behavior, the COM add-in automatically loads when you start Microsoft Excel.

To make HTTP calls, I use a HTTP retrieve component, which have the possibilities to make asynchronous calls. This are needed to not block the user when the program wait for HTTP response. The full code of that component is included. A description of how to work with that component is also provided in the following article: Discover WIN32. How to use a sync/async retrieve HTTP content in your ASP & VB projects.

Set myORetrieve = CreateObject("RetrievePage.RetrievePage.1")
Dim str As String
str = myORetrieve.RetrieveAsync(urlIndicesHistory)

' launch a thread who look every 1
'seconds at retrieveasync function
If str = "ok" Then
    'until the HTTP request are completed
    TimerIndexHistory.Interval = 1000
End If

'This are the timer function ("thread who are looking every
'second at HTTP component completion"):
Private Sub TimerIndex_Timer()
    Dim str As String
    Dim lRetrieveAsyncCompleted As Long

    'until lRetrieveAsyncCompleted = 0 i will dont goway
    Message.Caption = "Loading Data..."
    lRetrieveAsyncCompleted = _
      myORetrieve.RetrieveAsyncCompleted(urlIndicesPortfolio)

    If lRetrieveAsyncCompleted = 1 Then
        str = myORetrieve.RetrieveAsyncPage(urlIndicesPortfolio)
        TimerIndex.Interval = 0
        Message.Caption = ""
        ' do something now with HTTP data
        ParsePortfolio (str)
    End If

    If lRetrieveAsyncCompleted = -1 Then
        TimerIndex.Interval = 0
        Message.Caption = "Wrong URL"
    End If
End Sub

The ISAPI extension DLL is the best choice for HTTP feed, regarding at the speed , number of requests per second and number of simultaneous connections.

At each HTTP hit, the ISAPI module makes request again to the SQL database, using the CAdoDatabase and CAdoRecordset classes from CodeProject site :). The minor modification was to keep only database connection as public variable.

On each method the record sets needed are opened and closed. An optimization is the using of GetString method of AdoRecordset class, which returns directly a formatted data text. The ISAPI connection string is located in IsapiDemoFeed.cpp file, on the constructor:

m_bstrConnectionString="Provider=SQLOLEDB;Data Source=andi;Initial 
Catalog=ArticlesExcelFeed;
User ID=sa;Password=;";

More about how to use the ISAPI extension DLL like a HTTP feed provider can be found at Discover ISAPI. Feed provider for graphical applets.

The SQL database (ArticlesExcelFeed) is simple and contain only few tables and views. It is possible to use an Access MDB file with the same structure.

If we click on the Lauch Real Feed checkbox on the IndicesHistory or TickersHistory forms, the program will look 30 min at each 60s at the GetOnlineIndices method of the ISAPI server.

http://localhost/ArticleExcelFeed/IsapiExcelFeed.dll?GetOnlineIndices?

If the data in the tblOnlineIndices or tblOnlineTickers are changed in that interval of time, we will obtain online data that's changed in our IndicesPortfolio (TickersPortfolio) worksheet cells and IndicesCharts (TickersCharts) charts.

Installation

• The ISAPI connection string is located in IsapiExcelFeed.cpp file, on the constructor:
  VB

  m_bstrConnectionString=_
      "Provider=SQLOLEDB;Data Source=andi;" + _
      "Initial Catalog=ArticlesExcelFeed;" + _
      " User ID=sa;Password=;"; 

• Create a folder ArticleExcelFeed under your web site, and copy inside the IsapiExcelFeed.dll.
• Give Scripts and Executables execute permission to IsapiExcelFeed directory web application. In that way the IIS web server will be able to execute the IsapiExcelFeed.dll.
• To install the database on your SQL server select restore database option on SQL Server Enterprise Manager and choose the DB/ArticlesExcelFeed.bak file backup. The name of database is ArticlesExcelFeed.
• In order to use the Excel COM add-in on a client computer, we need the RetrievePage component. Use on the client computer regsvr32 RetrievePage.dll command from DOS prompt.
• To install the Excel COM add-in on a client computer, compile the VB project with File->Make ComExcelFeed.dll... option and launch the Excel program. It is possible to just use the regsvr32 ComExcelFeed.dll command from DOS prompt. A new toolbar named "" will be created in your Excel program.
• It is possible to implement an install program like that which is provided to automatically install the Excel client on the needed computers.

The demo application

To install the demo application just launch the ExcelFeedDemo setup.exe program and launch Excel. (use andi/andi as user/password).

Links

• Discover ISAPI. Feed provider for graphical applets.
• Discover WIN32. How to use a sync/async retrieve HTTP content in your ASP & VB projects.