Discover a COM Realtime Feed for Excel



Click here for a larger image.

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 supports 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, it will be possible to compute a lot of columns online, to add calculations, to add graphs and computing fields, and to look online at graphics changes. Using 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 the 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, format the data, and put that data to the HTTP stream. The Excel COM Add-in will receive that formatted data and fill its cells and graphs with updated values!

Advantages

  • Directly obtain 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 more calculations, computed fields, and graphs based on primary data in your worksheet.
  • It is possible to easily distribute the ExcelFeed client program to your clients.
  • By using the ISAPI, the Web server is freed from clients’ calls.
  • The ISAPI extension DLLs are the best choice if you want to obtain the maximum number of simultaneous connections to the IIS Web server or to provide huge quantities of HTML data.

Functionality

To start the client Excel Feed application, launch the installing Setup.exe program and fallow the wizard. After a successful installation, open the Excel program. You will find a new “Excel Real Feed” toolbar inside!

Click the “Real Feed” button of the Excel Real Feed menu. A new Book will be opened and the “Login” option will be activated. Click 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 in user “andi” and password “andi” and click the “Login” button. The “GetIndices” and “GetTickers” buttons will be activated.

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

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

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

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 subkey for each installed COM Add-in. The name of the subkey is the ProgID for the COM Add-in. The subkey 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 Create the COM Add-In

  1. Start a new AddIn project in Visual Basic 6.0.
  2. Add a reference to the 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. Because 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 an HTTP retrieve component that has the capability to made asynchronous calls. This is needed to not block the user when the program waits for a HTTP response. The full code of that component is included. A description of that component works is also provided in the following article: http://www.codeproject.com/useritems/retrievehttp.asp.

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

If str = "ok" Then   ' launch a thread who look every 1 seconds
                     ' at retrieveasync function
  TimerIndexHistory.Interval = 1000    ' until the HTTP requests
                                       ' are completed
End If
' This is the timer function ("thread that is looking every second
' at HTTP component completion"):
Private Sub TimerIndex_Timer()
  Dim str As String
  Dim lRetrieveAsyncCompleted As Long

  Message.Caption = "Loading Data..."
    ' until lRetrieveAsyncCompleted = 0 I won't go away
  lRetrieveAsyncCompleted =
    myORetrieve.RetrieveAsyncCompleted(urlIndicesPortfolio)

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

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

The ISAPI extension DLLs are the best choice for a HTTP feed, regardless of the speed, number of requests per second, and number of simultaneous connections. At each HTTP hit, the ISAPI module again requests the SQL database, using the CAdoDatabase and CAdoRecordset classes. The minor modification was to keep only the database connection as a public variable.

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

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

More about how to use the ISAPI extension dll like a HTTP feed provider can be found at http://www.codeproject.com/useritems/IsapiAppletsFeed.asp.

The SQL database (ArticlesExcelFeed) is simple and contains only a few tables and views. It is possible to use an Access MDB file with the same structure. If you click the “Launch Real Feed” checkbox on the IndicesHistory or TickersHistory forms, the program will look 30 minutes at each 60s at the “GetOnlineIndices” method of the ISAPI server.

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

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

Installation

  • The ISAPI connection string is located in the IsapiExcelFeed.cpp file, on the constructor:
    m_bstrConnectionString="Provider=SQLOLEDB;Data Source=andi;
                            Initial Catalog=ArticlesExcelFeed;
                            UserID=sa;Password=;";
    
  • Create a folder, ArticleExcelFeed, under your Web site, and copy IsapiExcelFeed.dll to it.
  • Give “Scripts and Executables” Execute Permission to the “IsapiExcelFeed” directory’s 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 the restore database option on SQL Server Enterprise Manager and choose the “DB/ArticlesExcelFeed.bak” file backup. The name of database is ArticlesExcelFeed.
  • To use the Excel COM add-in on a client computer, the “RetrievePage” component is needed. Use it on the client computer “regsvr32 RetrievePage.dll” command from a DOS prompt.
  • To install the Excel COM add-in on a client computer, compile the VB project with the “File, Make ComExcelFeed.dll…” option and launch Excel. It is possible to just use the “regsvr32 ComExcelFeed.dll” command from the 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.

Links

http://www.codeproject.com/useritems/IsapiAppletsFeed.asp

http://www.codeproject.com/useritems/retrievehttp.asp
.

Downloads

Download source files – 357 Kb
Download demo project – 630 Kb

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read