Requesting Data from WinWedge Using the VBA SetOnLinkData Method

TALtech Home: Support: WinWedge Support: DDE and the WinWedge: WinWedge DDE Examples:

The Excel VBA macro language has a function called "SetLinkOnData" that can be used to configure Excel to automatically run a subroutine whenever data from a DDE Server is updated. In the previous example we showed how to configure the Wedge to send a command to Excel to force it to run a macro after each data record is received from a device on a serial port. This example shows how to use the Excel SetLinkOnData method instead of having the Wedge send a command to Excel to trigger a macro to run.

The two techniques are similar in that the end result is that after each data record is received by the Wedge, Excel automatically runs a macro that grabs the data from the Wedge and does something with it. The difference between them is simply that in the previous example the Wedge triggers the macro to run whereas in this example Excel automatically runs the macro with no coaxing from the Wedge.

One of the benefits of the following method is that the Wedge does not have to be in "DDE Server Mode" in order to have Excel run the macro after each data record from the Wedge. Although the Wedge has an explicit "DDE Server Mode", it is actually a DDE Server no matter what mode it is in ("Send Keystrokes", "Log To Disk" or "DDE Server" modes). The reason that the Wedge has a DDE Server Mode at all is so that you can configure it to send a DDE command to another application after each input. Suppose that you wanted to log all data from the Wedge to a disk file and pass the data to Excel via DDE in real time. You could set up the Wedge in Log To Disk mode and use the SetLinkOnData method in Excel to trigger a macro that would pull the data from the Wedge into a spreadsheet.

Another benefit of this method is that the data from the Wedge will be transferred to Excel slightly faster than it would if the Wedge were sending a command to Excel after each input.

In addition to demonstrating the SetLinkOnData method in Excel, the following macros also show how to launch and activate the Wedge automatically when you open up your spreadsheet as well as how to quit the Wedge automatically when you close your spreadsheet.

To use the following macros, set up WinWedge to work with your instrument by choosing the correct serial communications parameters and defining the input data record structure to fit the data that is returned from your serial device. If you set up the Wedge in DDE Server mode, make sure that you do not have any "Field Postamble DDE Commands" defined. Next, activate the Wedge in Test Mode, switch to Excel and enter the following macros into a module in your Excel spreadsheet. (You may have to modify the values for some of the Global constants as described in the comments in the subroutines.) After you have entered the following subroutines, save your spreadsheet and close it. Finally, re-open your spreadsheet and start collecting data. The Wedge should automatically load and activate itself when you open the sheet.

Global RowPtr As Long, ColPtr As Long ' define global variables
Global Const MyPort = "COM1" ' change comport if necessary for your configuration
' If Wedge is not set up for COM1, then change the line above to the right port
Global Const CmdLine = "C:\WinWedge\WinWedge.Exe C:\WinWedge\Config.SW3"
' change the above command line to point to your copy of wedge and your config file

Sub Auto_Open() ' this sub runs automatically when you open the spreadsheet

On Error Resume Next ' ignore errors and try to launch WinWedge
RetVal = Shell(CmdLine) ' launch wedge using command line defined above
' the above line launches and activates the wedge with a config file: Config.SW3

Application.Wait Now + 0.00002 ' give wedge time to load and activate itself
AppActivate Application.Caption ' set the focus back to excel
StartCollecting ' set up excel to collect data from wedge

End Sub

Sub StartCollecting()

RowPtr = 1: ColPtr = 1 ' initialize global variables
Sheets("Sheet1").Activate ' activate sheet 1 and set up a DDE link to WinWedge
Sheets("Sheet1").Cells(1, 50).Formula = "=WinWedge|" & MyPort & "!RecordNumber"
ActiveWorkbook.SetLinkOnData "WinWedge|" & MyPort & "!RecordNumber", "GetSWData "
' the SetLinkOnData method causes excel to run the GetSWData macro
' automatically when new data is available in the Wedge (when the RecordNumber is
' updated). Using the SetLinkOnData method in excel eliminates the need to have
' the Wedge send a Field Postamble DDE Command to excel to cause it to run the
' GetSWData sub.Excel will automatically run the GetSWData sub when the Wedge
' updates the RecordNumber DDE item. The RecordNumber DDE item is updated
' after all other DDE items (input data fields) have been updated.

End Sub

Sub Auto_Close() ' this macro runs automatically when you close the spreadsheet

StopCollecting ' set up excel to stop collecting data from wedge
On Error Resume Next ' ignore errors
chan = DDEInitiate("WinWedge", MyPort) ' open a dde link with the wedge
DDEExecute chan, "[Appexit]" ' tell wedge to quit
DDETerminate Chan

End Sub

Sub StopCollecting()

Sheets("Sheet1").Activate ' activate sheet1
Sheets("Sheet1").Cells(1, 50).Formula = "" ' remove the dde link from R1C50
ActiveWorkbook.SetLinkOnData "WinWedge|" & MyPort & "!RecordNumber", ""
' shut down the SetLinkOnData function by assigning it an empty string to the macro name
ActiveWorkbook.Save

End Sub

Sub GetSWData()

If RowPtr = 0 Then RowPtr = 1: ColPtr = 1
' If this is the first time through then initialize RowPointer to point to Row 1 and
' ColPointer to point to Column 1 - i.e. collect data into column 1 starting in Row 1
chan = DDEInitiate("WinWedge", MyPort) ' initiate DDE link with wedge
MyVariantArray = DDERequest(chan, "Field(1)") ' get Field(1) from Wedge
MyString$ = MyVariantArray(1) ' convert to a string
' the DDERequest function in Excel returns a variant array data type
' this is a peculiarity of Excel - the return type should be a string data type
' the above line fixes the Excel "inconsistency" and converts the variant array
' to a string by assigning element 1 of the variant array to a string variable
' Add your code here to do something with the data maybe?
' or possibly send a command back to the Wedge as in the following line:
' DDEExecute chan, "[Beep]" ' send a beep command to the Wedge

Sheets("Sheet1").Cells(RowPtr, ColPtr + 1).Value = MyString$
' the above line writes the data to cell address: (RowPtr,ColPtr) in Sheet1

DDETerminate chan ' terminate the dde link
RowPtr = RowPtr + 1 ' point to the next row down

End Sub