Suppose you had a group of devices connected via a multi-drop RS422 or RS485 line such that each device responded to a prompt by sending back a record of data. Suppose also that you wanted to set up the Wedge and Excel to poll each device in sequence and retrieve the data stacking up the readings from each device in separate columns in your spreadsheet. In a multi-drop situation you typically have to wait for each device to respond to its prompt before you can send the next prompt to the next device. The following instructions and macro examples show how to handle a situation like this.
Note: The following example can also be used to send multiple prompts to a single device over an RS232 connection and thus retrieve multiple data elements from the device.
WinWedge Setup
- Select “DDE Server” from WinWedge “Mode” menu. When the dialog box appears asking for a “DDE Command Destination Application”, clear out both text boxes prompting for an Application Name and a DDE topic.
Note: in this example we will not be configuring the Wedge to send a DDE Command to Excel therefore these two items are not necessary and can be left blank. - Select “Input Data Record Structure” in the “Define” menu and define the structure of the input data record(s) to WinWedge. When you get to the final Window with the caption “Input Record Definition Editor”, make sure that you do not have any Field Postamble DDE Commands defined.
- Set up the Wedge to transmit the first prompt for the first device that you want to poll using either a timer controlled output string or a button controlled output string – Select “Output Strings” from the DEFINE menu. If you use a timer controlled output string, set the timer interval to a value that is high enough to guarantee that all devices will be polled before the first prompt gets sent again. Also, do not check the option “Enable Timer on Activation” – we will activate the timer later, after we are finished setting up Excel.
- Set up the rest of WinWedge parameters and then activate it.
Excel Instructions
Create or edit a macro module in Excel and enter the following code in the module:
(To create a new module click on Insert > Module from the Menus in the Visual Basic Editor Window.)
Global Const MyPort="COM1" ' Change port to match config. of WinWedge
Global Const MaxPrompts% = 3 ' number of prompts or devices
Dim RowPointer(MaxPrompts%) As Long ' create an array of RowPointers
Dim Prompt$(MaxPrompts%) ' create an array of prompts
Sub GetSWData()
' the variable PNum keeps track of which prompt was sent last
' start with device 1
Static PNum As Long ' preserve the value of PNum between calls
If PNum = 0 Then PNum = 1 ' Set the initial value of PNum to 1
' define the specific prompts for each device - add a line for
' each prompt
' see the Wedge manual for details about the SENDOUT command
' WinWedge will send the first prompt so we do not need to
' define it here
Prompt$(2) = "[SENDOUT('PString2',13,10)]" ' prompt for device 2
Prompt$(3) = "[SENDOUT('PString3',13,10)]" ' prompt for device 3
For x%= 1 to MaxPrompts% ' initialize RowPointer array
' start saving data in row 2
If RowPointer(x%) = 0 Then RowPointer(x%) = 2
Next
On Error Resume Next ' ignore errors
' open a link to WinWedge
Chan = Application.DDEInitiate("WinWedge", MyPort)
' get data from field(1)
MyData = Application.DDERequest(Chan, "FIELD(1)")
' convert variant array type to a string variable
MyDataString$ = MyData(1)
' The data from Field(1) of WinWedge is now in "MyDataString$"
Sheets("Sheet1").Cells(RowPointer(PNum), PNum).Value = MyDataString$
' the above line writes the data to:
' column "PNum" in row "RowPointer(PNum)"
RowPointer(PNum) = RowPointer(PNum) + 1
' increment RowPointer(PNum)
' PNum indicates which prompt requested the data that we just got
' add code here to do further processing of the data if required.
Select Case Pnum
Case 1 ' do something with data from device #1 here if desired
Case 2 ' do something with data from device #2 here
Case 3 ' do something with data from device #3 here
End Select
' Increment Prompt Number - count from 1 to MaxPrompts%
PNum = PNum + 1
' If PNum%>MaxPrompts% then loop back to 1
If PNum > MaxPrompts% Then
PNum = 1 ' and quit - Wedge will send prompt for first device
Else ' otherwise send the prompt for the next device
Application.DDEExecute Chan, Prompt$(PNum) ' send the next prompt
End If
DDETerminate Chan ' terminate the link
End Sub
Sub SetUpDDE()
' activate sheet 1 and set up a DDE link to WinWedge
Sheets("Sheet1").Activate
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
' each data record is received.
End Sub
After entering the two subroutines above, run the subroutine “SetUpDDE” to establish a DDE link between Excel and the RecordNumber DDE item in the Wedge.
After Excel is set up and you are ready to start prompting for data you can enable the timer in the Wedge. What is going on here is that the Wedge will send the first prompt to the first device causing it to send back a response. When each response comes back, the Wedge updates the RecordNumber DDE item in Excel forcing it to run the macro above (because we used the SetLinkOnData method in the SetUpDDE subroutine to cause this to happen). The first thing the macro does is pull in the data from the Wedge and place it in a column using the “PNum” variable as the column selector. For example, if PNum is equal to one, the data is written to column A. If PNum is equal to two then the data is written to column B, etc. Next, the macro increments a “PNum” variable and checks its value. If PNum is greater than the maximum number of prompts (i.e. devices) then it simply resets to 1 and quits; otherwise it transmits the next prompt in the sequence. When the response to this prompt comes in, the process repeats itself until we receive the data from the last device and PNum is incremented past the number of devices. The timer controlled output string in the Wedge starts the whole process over again by prompting for data from the first device.
Note: You do not have to use a timer controlled output string. To kick off the sequence, simply transmit the prompt for the first device. You can do this from Excel by writing a macro that sends a DDE command to the Wedge telling it to send the first prompt or you can define a hot key or a button controlled output string in the Wedge that would transmit the first prompt.