| Excel 5 has two "idiosyncrasies"
with its DDERequest() function. The first is that the DDERequest() function always returns
a variable with the data type "Variant Array".For some unknown reason the DDERequest function in Excel expects to receive a Tab
delimited array of data. In fact, if the data from the Wedge were tab delimited, Excel
would automatically parse it and fill up a Variant Array such that each tab delimited data
item would be stored in successive array elements. Typically, data from most instruments
is not tab delimited therefore when you perform a DDERequest to the Wedge from Excel you
end up with a Variant Array variable with only a single element (element 1) containing all
the data from the field in the Wedge that you are requesting the data from.
Excel will let you assign a Variant Array to a cell in a
spreadsheet however if you try to perform string functions on a Variant Array, Excel
generates an "Invalid Data Type" error. The Variant Array must be converted to a
string data type before it can be used in a string function. This problem is easily
overcome by simply assigning the Variant Array element 1 to a string. The following code
fragment uses the DDERequest function to return a Variant Array and then uses an
assignment statement to convert element 1 of the Array to a string.
DDEChannel = DDEInitiate("WinWedge",
"Com2")
DDEVariantArray = Application.DDERequest(DDEChannel,
"Field(1)")
DDETerminate DDEChannel
StringVariable$ = DDEVariantArray(1)
' assign Array element 1 to a String variable
The second problem is that the DDERequest()
function can only return string data that is less than 255 bytes
unless the string is delimited with tabs (see the following section
"Advanced Excel Tricks" pg. 93). If you try to DDERequest()
a data string that is longer than 255 bytes, Excel truncates the
data and you lose anything after the 255th byte.
The following subroutine shows how to get around both of
the problems described above as well as how to get around the 40 field limit in the
Software Wedge. To get past the 40 field limit in the Wedge, instead of using the parsing
capabilities of the Wedge, we will use our own parsing routine written in Excel VBA. To do
this we first have to get all the data from the device into a single string variable in
Excel. This is where the problems in Excel catch up to us, especially when the total
length of the data stream could be more than 255 bytes.
The following example shows how to configure the Wedge and
Excel to deal with a situation where a device transmits a data record containing over 40
data fields that are delimited with commas and terminated with a carriage return. The
total length of the data could also be over 255 bytes. If Excel were capable of pulling in
over 255 bytes with the DDERequest function, then all we would have to do is define the
"End of Record Event" in the Wedge as "Carriage Return or CrLf
Received" and then define the structure of the data records as "Single
Field". Finally, we would use a VBA macro to parse the string after we pass it to
Excel. Because the data string could be over 255 bytes long, we have to break it into
pieces before passing it to Excel and then put it back together again in our VBA macro.
The way to do this is to again select "Carriage Return
or CrLf Received" as the "End of Record Event" in the Wedge and then select
"Multiple Fixed Length Data Fields" as the "Record Structure".
Finally, define several data fields with all their field lengths set to 250 bytes. You
need to define enough 250 byte data fields so that the largest data record from the device
will fit in all the fields defined in the Wedge. For example if the largest record
transmitted by the device is 1125 bytes, then you would need to define five 250 byte data
fields. Make sure that the Wedge is in DDE Server mode and after the very last data field
that you define, enter: [RUN("NewGetSWData")] as the "Field
Postamble DDE Command".
The above command will cause Excel to run a subroutine
named "NewGetSWData" after each complete data record is received by the Wedge.
Note: Be sure not to apply any Filters to any of the data
fields that you define in the Wedge.
The following subroutine performs the job of pulling the
data from the Wedge and then putting it back together into one long string. Finally the
subroutine parses the data into individual data fields and plugs each field into separate
cells in a row of the spreadsheet. |
