Page History
...
Now select cells A1 through D5 and copy them to the clipboard:
When you open up a Word document you will see two options for bringing in this clipboard data: “Paste” and “Paste Special” (see above picture for an example.) Paste does its normal job and simply creates a static table and text area. This information no longer has any connection to Excel. Even the formula we created in Excel has been converted to simple text.
Paste Special brings up a dialog box that gives us two options: “Paste” and “Paste link”. Note the descriptions provided in the Result group box based on which one is selected:
To summarize, Paste Special -> Paste brings a copy of our Excel table (including formulas) into Word while maintaining its property as an Excel table. Therefore, changes to the table data can be made directly in Word and any formulas will re-calculate as needed. This is an example Object Embedding.
Paste Special -> Paste link simply brings a visual display of our Excel table into Word but the source of this data is still within our original Excel document. So when changes are made to the Excel document we can see the results displayed in Word. This is an example of Object Linking and in many ways is similar to the concept of symbolic fields in OpenInsight.
Conceptually, OLE represents the evolution of object-oriented programming that was introduced through the use of DLLs and, in some ways, DDE (Dynamic Data Exchange). Both of these older technologies have been supported by OpenInsight from its inception. However, OLE is easier to reuse and share because it is based on design specifications that require standard interface support and backwards compatibility. Whereas changes to a DLL might break an application, changes to an OLE object should not affect any systems built around an older version. Anyone who has installed a commercial application using a newer version of datatbl.dll has experienced how the edittable control can start behaving differently than expected.
Perhaps the most common use of OLE is through ActiveX controls; also known has OCX because of the .OCX extension of their system files. ActiveX provides very similar functionality as DLLs in that they contain callable stored procedures (known as “methods”) and they provide a user interface. Hence, when an ActiveX control is placed within a container that can support this type of OLE, e.g. an OpenInsight form, the end user is presented with its pre-designed visual interface. While ActiveX controls can be designed as very complicated self-contained applications, they are normally designed to fulfill relatively simple user interface needs. Common examples include: command buttons, picture viewers, tab bars, and spin controls.
OLE in OpenInsight
...
Revelation has metaphorically put their “foot in the door” with their current support for OLE. While there are wonderful advantages and features that can be utilized right now, OLE implementation is somewhat rough and limited. Revelation has expressed their intention to improve OLE support but we probably won’t see anything significant until version 7.1 or later. Therefore, it is important to know what can and cannot be done now with OLE in OpenInsight.
At present OpenInsight only supports ActiveX/OCX. Consequently, for the remainder of this document, unless otherwise noted, the terms ActiveX, OCX, and OLE will have synonymous meanings. Even though ActiveX is not as sophisticated as linking/embedding productivity applications, it does provide developers an opportunity to enhance their OpenInsight applications (visually and functionally) in a virtually unlimited number of ways.
Programmers who are familiar with other visual programming tools (like Delphi, Visual Basic, etc.) know that the OpenInsight control palette provides very limited options. For instance, one of the most common user interface objects in any Windows application is the tab control. Since tabs are not integrated into the Form Designer, developers have to write complicated code and/or spend a lot of time designing bitmaps to simulate a working tab control.
OLE via ActiveX is a great way to overcome this kind of limitation without having to upgrade the Form Designer whenever a new control is desired. Adding a new control to OpenInsight now only requires the installation of an ActiveX control of one’s choice into Windows, placing an OLE object on a form, and then providing the necessarily information to link the OLE object to the desired ActiveX control.
Getting Started
There are a few items that are needed to successfully use OLE controls in OpenInsight. Each of these will be discussed in greater detail:
- One or more controls with the .OCX extension (e.g. MSCAL.OCX)
- Current and complete documentation on each control’s properties, events, and methods
- Each OCX control’s ProgID or CLSID
There are three basic ways to acquire OCX controls: 1.) Use ones that are already supplied with the Windows operating system, 2.) acquire commercial, shareware, or freeware controls from a third-party (usually through the internet), or 3.) write your own. Since most of us will be unable to write our own we need to know what to look for with third-party or Windows supplied controls.
If you ever do an online search for OLE controls (e.g. “free activex”, “free ocx”, “ole controls”, etc.) you will find more links than you could possibly exhaust. What will become obvious rather quickly is that the majority of available controls are designed primarily for Visual Basic. While this doesn’t preclude other development environments from using these OLE controls, it is likely that some of the functionality will be limited. This is because people who design controls from within Visual Basic for Visual Basic depend upon commands that are unique to Visual Basic. That is to say, they were not interested in having their controls utilized in other environments (like Delphi, OpenInsight, etc.) and therefore they took advantage of pre-built Visual Basic features. This makes their work easier but unfortunately it makes their controls somewhat limited. Therefore we suggest that you look for controls that are designed to be friendly to all development platforms. Usually anything designed with Visual C++ or Delphi will work fine.
Something that we have seen lacking in share/freeware downloads is sufficient documentation. Typically, controls are bundled with a sample Visual Basic application instead. So unless you have access and understanding of Visual Basic this will be useless (note: Visual Basic applications usually come with .FRM files. If you edit this with a simple text editor you will see how the form stores the initial property settings for its ActiveX controls. This information is pretty easy to interpret and can be a way of identifying control properties and supported values.) Do everything possible to acquire complete documentation; your life will be so much easier.
Finally, in order for OpenInsight to incorporate an OCX control into the form, we must provide it the control’s ProgID or CSID. Hopefully this information will be provided in the documentation. If not, then we need to search for these ourselves.
One helpful tool is Microsoft’s OLE/COM Object Viewer (see the References section below). However, we have not found any easy way to copy and paste the information it provides. Here is an example of what this tool will show you when examining the Microsoft Calendar Control (MSCAL.OCX):
Note that the ProgID line has been highlighted. This tells us that MSCAL.Calendar.7 is this ActiveX control’s Program Identifier (ProgID). We will need this in order to associate our OLE container object in the Form Designer to this particular ActiveX control. (Note: Five lines below the ProgID is the VersionIndependentProgID line. We can also use this value. Its purpose, as should be evident by the name, is to provide us a Program Identifier that is common to all versions of this ActiveX control.)
Another tool than can be used fairly easily is the Registry Editor. Usually you can do a search for your ActiveX control (e.g. MSCAL.OCX) and within the first few matches it should place you in the vicinity where you can retrieve the ProgID:
After you have located your ActiveX control’s ProgID Key, you can double-click on the value to the right to easily copy and paste to the clipboard.
An alternative to using the ProgID would be the CLSID (Class ID). This value can be retrieved from both tools that have been discussed here. In the above screen shot of the Registry Editor, the CLSID is the upper left string of numbers and letters contained within the “french” braces:
| Code Block |
|---|
{8E27C92B-1264-101C-8A2F-040224009C02} |
...
For obvious reasons we recommend using the ProgID or VersionIndependentProgID whenever possible.
ActiveX controls must be registered to the local Windows operating system in order to work. Controls that are already included with Windows are normally pre-registered. Third-party controls might do this for you during an installation process. Otherwise, you are responsible for registering them yourself as you would for any controls that you develop in-house. However, since registering a control requires that you know the path where the controls resides, a decision must first be made as to where to place any third-party or custom developed controls.
Standard practice dictates that ActiveX controls, like system DLLs, be kept in the Window’s System32 folder. This provides the advantage of maintaining all controls in one place. All applications that use this control will always be using the same (and hopefully newest) version of the ActiveX control. Since the System32 folder never moves, applications can be moved at will and they will always know where to access any needed ActiveX controls.
One caveat, however, is that with large network environments any major changes to an ActiveX control will require an update to every user workstations that accesses this application. Without special tools that administrate client environments automatically, this can be a significant chore. This is particularly the case with custom developed ActiveX controls which tend to go through significant modifications more often. In this scenario, it might be easier to store the ActiveX control in the same folder where the application is. If an application’s path does not change, then this can be a better solution.
Once the path is known, the command to register a control is: RegSvr32 pathname\controlname.ocx. For example:
...
If all went well then you will receive a message similar to this:
If you didn’t get this message then double-check your path, spelling, and whether or not the OCX file actually exists in your system.
Putting It All Together
...
Once the above steps have been completed, displaying an ActiveX control in an OpenInsight form is very easy. We’ll continue to use the Microsoft Calendar as an example. Begin by creating a new form with no table. Select the OLE button from the Control palette and then click on the form to create a new OLE container object:
...
At this point it is important to understand that there are two components that have to be considered when working with OLE: the OpenInsight OLE control (which is what we just placed on our form and will act as a container) and actual ActiveX control that this container will reference. This distinction will be relevant when the subject of Properties and Events are of Properties and Events are discussed below.
Now we need to associate our ActiveX control to the OpenInsight OLE container. In the Text property field enter the ProgID (or CLSID) of the control you want. Since the Microsoft Calendar ActiveX control employs a large interface, stretch out the container to accommodate. Your window should look something like this:
...
Go ahead and test run your form, naming it OLE_TEST. It should look like this:
In one sense, that’s all there is to it! This calendar control can now respond to user interaction. Different dates can be selected and the month or year can be changed at will. No Basic+ programming was required to enable this functionality. Remember, ActiveX controls are essentially self-contained programs that only require an environment to operate within. Therefore, any built-in functionality will immediately be available to the end-user without any intervention by the OpenInsight developer.
While the ActiveX control can certainly operate without additional assistance from OpenInsight, it will need help to communicate and interact directly with the development environment. For instance, even though the end user can easily change the date on the calendar, the ActiveX control hasn’t told OpenInsight what this date is. So even though the end user can see the date on the screen, the application itself needs to know this date if it is going to use it in a program or store it in the database.
Since OLE uses communication concepts that we are already familiar with (i.e. Properties, Events, and Stored Procedures (normally called Methods)) we can leverage this knowledge without too much of a learning curve. Additionally, Revelation Software has very nicely adapted the Set_Property(), Get_Property(), and Send_Message() functions to work with OLE controls. This is good news for those who are concerned about having to learn a whole new tool set in order to use ActiveX in their OpenInsight applications.
Properties
Just like native OpenInsight controls, OLE controls have properties that are set or retrieved. Note, however, that in the case of OLE controls there are the properties of the OpenInsight OLE container (which are regular native OpenInsight properties) and there are properties that are inherent to the OLE (i.e. ActiveX) control itself. As we will shortly see, there needs to be a way to distinguish between them.
If we wanted to change the text that appears in a standard editline we would do something like this:
| Code Block |
|---|
rv = Set_Property(@Window:".EDITLINE_1", "TEXT", "Hello World!") |
...
We can use the exact same logic to modify the property of an OLE control. For instance, the Microsoft Calendar control has a property called FirstDay which tells the control which day of the week should be displayed in the first column (see References section for a complete list of properties, methods, and events for the Microsoft Calendar control.) Let’s say we wanted to create a calendar that is oriented toward a business schedule (similar to how Day Runner calendars are formatted). We would want Monday to appear in the first column. This can be done by placing this logic in the CREATE event then test running the form:
| Code Block |
|---|
rv = Set_Property(@Window:".OLECONTROL_1", "FirstDay", 1) ; * 1 = Mon, 2 = Tues, etc. |
...
We see that the TEXT, VISIBLE, and ENABLED properties (among others) can be easily set without a line of code. OLE container controls, however, have only this dialog:
Although there appears to be a few properties in this dialog box, these only affect the OLE container object that OpenInsight provides and not the ActiveX control that it will eventually contain. Therefore, all property settings, initial or otherwise, must be handled via code or through a tool that does the work for you (SRP has designed their own OLE Wizard to manage most of this type of work.)
2. No Data-binding Support
Certain controls, like tabs and command buttons, would rarely, if ever, need to be tied to the database. Edit controls, however, would greatly benefit if this were possible. Consider the advantages of having a fully featured rich-text control whose contents could be edited like a word processor and could also directly read and write its information, including text formatting, to the database. Without the ability to bind OLE controls to the database, we are only half-way there. Obviously we can simulate the second half, but this requires more code.
3. Implementation Only at Runtime When in the Form Designer we can only specify the name of ActiveX control we want to display in the OLE control area. Unfortunately this gives us a very limited idea of how the control will appear on the form until it is executed:
...
Unless you like the border to display during runtime, make sure you turn off the checkbox when the control is in its final location and size. Unfortunately we still can’t see how the control will appear to the end user until we run the window ourselves:
Our inability to see a visual layout becomes a very noticeable problem when several OLE controls need to be placed next to each other, like a row of buttons. Therefore, we developed within our OLE Wizard the ability to reposition OLE controls on a running window and save any changes back to the source record in SYSREPOSWINS table.
4. Limited Interaction with the OpenInsight Form and Native Controls
There are a few issues that we will identify here. First, if you are using OpenInsight v4.1.x, OLE controls will not be within the tabbing order of native controls unless you use the NEXT and/or PREVIOUS properties to modify the tab flow to include the OLE Container control. OpenInsight 7.0.x, however, allows the developer to include OLE controls in the tab order by using the Tools -> Order Tabs menu item. In either case, when the OLE control needs to lose focus the ActiveX control needs to send a TAB character via the WM_CHAR Windows message to the parent form. Therefore, third-party controls are likely to not work “as is”. Second, OLE controls prevent access to any native OpenInsight controls if they occupy the same space on the form. Normally one wouldn’t do this on purpose, but some ActiveX controls are designed to be a container for other controls (similar to the Group Box). Therefore one might attempt to do this in OpenInsight and wonder why they can’t click on their native controls. Third, OpenInsight forms behave strangely when there are only OLE controls on a form (or page). This can be resolved if the ActiveX control sends a WM_SETFOCUS Windows message to the OLE Container control (not the parent form) whenever they get focus. Alternatively, this can also be resolved by making sure at least one native control, one that can get focus, exists on every form or page that has an OLE control. If this is done, multi-page forms should explicitly set focus to a native control whenever the page is changed. This is because OpenInsight remembers the last native control that had focus. Therefore, if something triggers the form to find a control (like toggling to another window and back) we want it to focus on a control that appears on the same page. Finally, in OpenInsight v4.1.x, keyboard and mouse messages are suppressed and are not received by the parent form when an OLE control has focus. This causes features like menu accelerator keys to fail. There is a workaround, but it has to be designed within the ActiveX control itself. This will unlikely exist in any pre-built control (commercial or share/freeware), but the fix is very simple: Get the handle for the parent window of the OLE control (i.e. the OpenInsight form) and issue a command like PostMessage(hWnd, message, wParam, lParam) for all keyboard and mouse messages. OpenInsight 7.0.x resolves this problem and does not require any additional help from the ActiveX control.
References
...
Further information we think will be helpful are listed here for your convenience:
1. Microsoft’s OLE/COM Object Viewer can be downloaded here:
http://www.microsoft.com/com/resources/oleview.asp?&SD=GN&LN=EN-US&gssnb=1
2. Microsoft’s Calendar Control Documentation:
(NOTE: For properties of type Bool, -1 is TRUE and 0 is FALSE.)
| Property | Type | Description |
|---|---|---|
| BackColor | Color | Control’s background color. |
| Day | Integer | The current day of the month. |
| DayFont | Font | The font used to display the days of the week. |
| DayFontColor | Color | The color used to display the days of the week. |
| DayLength | Integer | The format used to display the days of the week: 0 for Short (“M”), 1 for Medium (“Mon”), 2 for Long (“Monday”). |
| FirstDay | Integer | The day of the week to be displayed in the first column: 0 for Sunday, 1 for Monday, 2 for Tuesday, 3 for Wednesday, 4 for Thursday, 5 for Friday, 6 for Saturday |
| GridCellEffect | Integer | The effect used to display the grid: 0 for Flat, 1 for Raised, 2 for Sunken GridFont Font The font used to display the days of the month. |
| GridFontColor | Color | The color used to display the days of the month. |
| GridLinesColor | Color | The color used to display the lines in the grid. GridCellEffect must be 0 (Flat). |
| Month | Integer | The current month. A value between 1 and 12. |
| MonthLength | Integer | The format used to display the month: 0 for Short (“Jan”), 2 for Long (“January”). |
| ShowDateSelectors | Bool | Specifies whether to display drop-down boxes for the month and year. |
| ShowDays | Bool | Specifies whether to display the days of the week. |
| ShowHorizontalGrid | Bool | Specifies whether to display horizontal gridlines. |
| ShowTitle | Bool | Specifies whether to display the month and year above the calendar grid. |
| ShowVerticalGridlines | Bool | Specifies whether to display vertical gridlines. |
| TitleFont | Font | The font used to display the month and year above the calendar grid. |
| TitleFontColor | Color | The color used to display the month and year above the calendar grid. |
| Value | Variant | The currently selected date. |
| ValueIsNull | Bool | Specifies whether the value is null, i.e., no data is selected. |
| Method | Description |
|---|---|
| AboutBox() | Displays an informational dialog box about the Calendar Control including it’s version and copyright information. |
| NextDay() | Increments the control’s value by one day and refreshes the control. |
| NextWeek() | Increments the control’s value by one week and refreshes the control. |
| NextMonth() | Increments the control’s value by one month and refreshes the control. |
| NextYear() | Increments the control’s value by one year and refreshes the control. |
| PreviousDay() | Decrements the control’s value by one day and refreshes the control. |
| PreviousWeek() | Decrements the control’s value by one week and refreshes the control. |
| PreviousMonth() | Decrements the control’s value by one month and refreshes the control. |
| PreviousYear() | Decrements the control’s value by one year and refreshes the control. |
| Refresh() | Repaints the control. |
| Today() | Sets the control’s value to today’s data and refreshes the control. |
...
| Event | Description |
|---|---|
| AfterUpdate() | Occurs after the user selects a new date in the control and the new date is highlighted. |
| BeforeUpdate(Cancel) | Occurs after the user selects a new date in the control but before the new date is highlighted. If Cancel is set to TRUE (-1) then the event is canceled and the previously selected date is restored. |
| Click() | Occurs when the user clicks on a date in the control. |
| DblClick() | Occurs when the user double-clicks on a date in the control. |
| KeyDown(KeyCode, Shift) KeyUp(KeyCode, Shift) | Occurs when the user presses or releases a key while the control has focus. KeyCode is an integer representing the key. Shift specifies the SHIFT, CTRL, and ALT button states: 0 for None, 1 for SHIFT, 2 for CTRL, 3 for SHIFT+CTRL, 4 for ALT, 5 for SHIFT+ALT, 6 CTRL+ALT, 7 for SHIFT+CTRL+ALT. |
| KeyPress(KeyAscii) | Occurs when the user presses and releases a key while the control has focus. An integer that is the numeric ANSI key code. |
| NewMonth() | Occurs whenever the value of the control changes to display a different month. |
| NewYear() | Occurs whenever the value of the control changes to display a new year. |
3. OpenInsight 4 Programmer’s Guide, Version 4.1.3 (progref.chm), OLE event. This article contains updated information on special eventprocessing parameters that can be issued with the Send_Message() function for OLE event handling.
4. Revelation Software’s 2002 End-of-the-Year Works CD. SRP has included evaluation versions of their own OLE controls with an OLE Demo window in the Third Party section of this CD. This includes a Tab control, Picture control, StatusBar control, Button control, and HyperLink control. Here are some screenshots of what the OLE Demo window looks like:
...
