The examples we looked so far used cold links. That means, the client obtains a new set of data only when it asks for it. On the other hand, in case of a hot link, whenever data changes in the server, the client application is automatically informed. To organize this type of connection, a client must establish link to the cells it want to track, but you don't have to worry about opening or using a channel.

The command to start a hot link is StartHotLink(). This function accepts three parameters: 'location of data,' 'Application' and 'Topic'. As an example of how to use this type of data transfer, we'll use the spreadsheet we used in the previous examples.

First, we have to set up the necessary hot links. This is the code for the "Start Hot Link with Excel" CommandButton:

// Object: cb_3 (Start Hotlink with Excel)
// Event: Clicked

string lRetValFromExcel

StartHotLink( "R2C2","Excel","ddetst1.xls" )
StartHotLink( "R3C2","Excel","ddetst1.xls" )
StartHotLink( "R4C2","Excel","ddetst1.xls" )
StartHotLink( "R5C2","Excel","ddetst1.xls" )

StartHotLink( "R2C3","Excel","ddetst1.xls" )
StartHotLink( "R3C3","Excel","ddetst1.xls" )
StartHotLink( "R4C3","Excel","ddetst1.xls" )
StartHotLink( "R5C3","Excel","ddetst1.xls" )

StartHotLink( "R2C4","Excel","ddetst1.xls" )
StartHotLink( "R3C4","Excel","ddetst1.xls" )
StartHotLink( "R4C4","Excel","ddetst1.xls" )
StartHotLink( "R5C4","Excel","ddetst1.xls" )

The function to stop a hot link is StopHotLink(); its arguments are same as StartHotLink(). You need to call StopHotLink() for the cells which have hot links established. To optimize an application, start the hot link when ever required, and stopping it as soon as possible.

// Object: cb_4 (Stop Hotlink with Excel)
// Event: Clicked

StopHotLink( "R2C2","Excel","ddetst1.xls" )
StopHotLink( "R3C2","Excel","ddetst1.xls" )
StopHotLink( "R4C2","Excel","ddetst1.xls" )
StopHotLink( "R5C2","Excel","ddetst1.xls" )

StopHotLink( "R2C3","Excel","ddetst1.xls" )
StopHotLink( "R3C3","Excel","ddetst1.xls" )
StopHotLink( "R4C3","Excel","ddetst1.xls" )
StopHotLink( "R5C3","Excel","ddetst1.xls" )

StopHotLink( "R2C4","Excel","ddetst1.xls" )
StopHotLink( "R3C4","Excel","ddetst1.xls" )
StopHotLink( "R4C4","Excel","ddetst1.xls" )
StopHotLink( "R5C4","Excel","ddetst1.xls" )

When a DDE conversation is running as a hot link, whenever data changes, the server informs the client. It is the client's responsibility to accept the data or not, and if accepted, how to process it.

Whenever the server talks to the client during a hot link, a HotLinkAlarm event occurs at the window level. Thus, any code for the client's reaction to a hot link broadcast should be assigned to this event. One important function that can be very useful for this event is GetDataDDEOrigin(). This function informs the client of the 'Application', 'Topic' and location of data from the server:

// Object: w_dde
// Event: HotLinkAlarm

int lRowNo, lColNo, lTotRows, i, lRowPos, lColPos, lLen
string lApplication, lTopic, lLocation, lDDEDataValue
long lInsertedRow

If dw_1.DataObject <> "d_dde_demo1" Then
   dw_1.DataObject = "d_dde_demo1"
   dw_1.SetTransObject( SQLCA )
End If

GetDataDDEOrigin( lApplication, lTopic, lLocation )

lRowPos = Pos( lLocation, "R")
lColPos = Pos( lLocation, "C")
lLen = Len(lLocation )

lRowNo = integer( mid( lLocation, (lRowPos + 1), &
         (lColPos - (lRowPos +1))))
lColNo = integer( mid( lLocation, (lColPos + 1), &
         (lLen - lColPos)))

lRowNo = lRowNo - 1
lColNo = lColNo - 1

lTotRows = dw_1.RowCount()
If lTotRows < lRowNo Then
   For i = 1 to ( lRowNo - lTotRows ) STEP 1
     dw_1.InsertRow(0)
  Next
End If

GetDataDDE( lDDEDataValue )
RespondRemote( TRUE )
dw_1.SetItem( lRowNo, lColNo, integer( lDDEDataValue ))

As we described in the DDE Concepts section, GetDataDDEOrigin() function returns three values. First is the application name ( Excel ), topic name ( ddetst1.xls ) and the item location ( Row no and Col No for example, R2C4 ). From the item ( location ) we are calculating the row no and the col no. If there aren't enough rows in the DataWindow, we are inserting rows in the DataWindow. Since the spreadsheet used in this example has row 1 and column 1 as blank, so, subtract 1 from the row and column number we received.

GetDataDDEOrigin() doesn't return the actual value received from the client, GetDataDDE() does, and it is used here to pass the value to a local variable, defined in the PowerBuilder script.

Run the application again and open DDETST1.XLS spreadsheet in Excel. Size the windows so that you can see what's happening in both Excel and PowerBuilder application. Get the data from Excel by clicking on the first or second CommandButton.

Data is retrieved from Excel as before and the spreadsheet is closed down. To see hot link in action, we'll have to open the spreadsheet again. Do this and then click on the "Start Hotlink with Excel" CommandButton. You won't find anything different, but, in the background hot link has been set up.

To see it work, go to the spreadsheet and change one of the values. When you hit the Enter/tab/arrow key, the change is reflected back in the DataWindow control.

We set up hot links to watch the first and second columns, so that you can change the values in columns B, C and D of the spreadsheet, and those changes will be seen in the DataWindow control. To close the hot links, click on the "Stop Hotlink with Excel" button.

Home	Previous Lesson: Using Excel Names Next Lesson: Mail Merge

Advanced PowerBuilder

Hot Link with Excel