Excel 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 WinWedge 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 WinWedge 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 WinWedge 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 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 = DDERequest(DDEChannel, "Field(1)")
DDETerminate DDEChannel
' assign Array element 1 to a String variable
StringVariable$ = DDEVariantArray(1)
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. 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.
Sub NewGetSWData() ' this sub is run by the Wedge after each data record is received
Static R As Long, C As Long
' R & C point to the Row and Column where the data will go
If R = 0 Then R = 1: C = 1 ' make sure neither R or C is zero
Chan = DDEInitiate("WinWedge", "Com2")
' assume that there are 5 data fields defined - change to more or less if necessary
Field1 = Application.DDERequest(Chan, "Field(1)") ' get field 1
Field2 = Application.DDERequest(Chan, "Field(2)") ' get field 2
Field3 = Application.DDERequest(Chan, "Field(3)") ' get field 3
Field4 = Application.DDERequest(Chan, "Field(4)") ' get field 4
Field5 = Application.DDERequest(Chan, "Field(5)") ' get field 5
DDETerminate Chan ' kill the link
MyVar$ = Field1(1) & Field2(1) & Field3(1) & Field4(1) & Field5(1) & ","
' Convert each variant array to a string, concatenate them all together and
' add a final comma delimiter for the following parsing routine. The following
' code parses the string MyVar$ by searching for commas and placing each
' delimited field in a separate row
StartPos = 1 ' starting position in the string - start at 1st byte
While StartPos < Len(MyVar$) ' scan until we reach the end of the string
DelimPos = InStr(StartPos, MyVar$, ",") ' find the next comma delimiter
DataPoint$ = Mid$(MyVar$, StartPos, DelimPos - StartPos)
' pull out a data point between the starting position and the position of the delimiter
StartPos = DelimPos + 1 ' update the starting position (skip over the delimiter)
Sheets("Sheet1").Cells(R, C).Value = DataPoint$ ' save the current data point
R = R + 1 ' point R to the next row down for the next data point
Wend ' go get the next point
R = 1 ' point R back to row 1
C = C + 1 ' increment C - move to the right one column for the next set of data
End Sub