|
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.
1. 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.
2. 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.
3. 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.
4. Set up the rest of WinWedge parameters and then
activate it.
1. Create or edit a macro
module and enter the following code in the module:
(To create a new module select "Macro" and "Module" from
Excel’s "Insert" menu.)
| |
Global Const MyPort="COM1"
' Change port to match configuration 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
Chan = Application.DDEInitiate("WinWedge",
MyPort)
' open a link to WinWedge
MyData =
Application.DDERequest(Chan, "FIELD(1)")
' get data from field(1)
' the above line gets the response from the last prompt that was
sent
MyDataString$
= MyData(1)
' convert variant array type to a string variable
' The data from Field(1) of WinWedge is now in the variable "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
PNum
= PNum + 1 '
Increment Prompt Number - count from 1 to MaxPrompts%
If PNum > MaxPrompts%
Then '
If PNum%>MaxPrompts% then loop back to 1
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. |
