The native format for Excel data is tab delimited text with a carriage return at the end of each row of data. When you perform a DDERequest function in Excel, the function returns a variable with the data type "Variant Array". If the data that is requested with the DDERequest function is tab delimited with carriage returns at the end of each row of data and we assign this type of data to variant array, Excel will automatically parse the data and fill up the array with the data. If you use the FormulaArray function to assign a variant array variable to a range of cells, Excel will write the data to the specified range.

For example, if you have a device that generates data in the following format:

123,456,789,123<cr>

456,789,123,456<cr>

etc...

you can use the "pre-transfer translation table" in WinWedge to convert all commas to tabs so that the data would appear in the Wedge as follows:

123<tab>456<tab>789<tab>123<cr>

456<tab>789<tab>123<tab>456<cr>

etc...

Note: When the Wedge is in DDE Server mode, its default behavior is to strip out carriage returns by translating them to "Nulls" therefore we will also need to translate carriage returns back to carriage returns and not to nulls. Select "Pre Transfer Translation Table" from the DEFINE menu and select the comma character in the table and click the button marked "Translate". When the ASCII chart appears, scroll the chart down to the TAB character (ASCII 9) and click the OK button to perform the translation.

Next select the carriage return character (ASCII 13) in the translation table and click the "Translate" button again. When the ASCII chart appears this time, select ASCII 13 and click the OK button. Click the OK button in the translation table to return to the main menu.

If you define the record structure in WinWedge as "Single Field Data Records" and thus pull the entire array into a single field in the Wedge and then pull the array into a variant array variable using Excel's DDERequest function and finally use the FormulaArray function to assign the variant array to a range of cells, Excel will automatically parse the data and write it to a range of cells such that each cell will contain a single value from the array. Data elements separated by tabs will be written to cells across a row and each carriage return in the data causes Excel to place data following each carriage return in the next row down.

The following example demonstrates how to set up the Wedge and Excel to pull in an entire comma delimited array (where each line of data in the array is carriage return terminated). By pulling in the entire array with a single DDERequest and letting Excel parse the data for us, you can pass huge amounts of data extremely quickly into a large range of cells.

Suppose you have a device that transmits a comma delimited array of data similar to the following where the entire array is transmitted once every ten seconds or in response to a prompt and you would like to input the data into a range of cells in Excel extremely quickly: (<cr> represents carriage returns and <lf> represents linefeed characters)

123, 456, 789, 123<cr><lf>

456, 789, 123, 456<cr><lf>

789, 123, 456, 789<cr><lf>

123, 456, 789, 123<cr><lf>

456, 789, 123, 456<cr><lf>

789, 123, 456, 789<cr><lf>

1. Select "DDE Server" from WinWedge "Mode" menu. When the dialog box appears asking for a DDE Command Destination Application, enter: "EXCEL" as the Application Name and then enter: "SYSTEM" as the DDE topic.

2. Select "Input Record Structure" in the "Define" menu and define the structure of the input record(s) to WinWedge. Select the "Start of Record Event" as "Any Character Received" and the "End of Record Event" as "Time Delay Between Records" and then click the "Continue" button. In the next dialog box to appear prompting for the time between records, leave the default value of 3 clock ticks and click the "Continue" button again. Next, select "Single Field Data Records" from the "Record Structure" dialog box and click the "Continue" button again. When you get to the final Window with the caption "Input Record Definition Editor", enter the string: [RUN("GetDataArray")] as the Field Postamble DDE Command for Field(1). This is a DDE command that will be sent to EXCEL after each data array is received by the Wedge.

3. Because Excel is expecting tab delimited data with carriage returns at the end of each line, we will need to use the "Pre-Transfer Translation Table" to translate the commas to tabs. Also, when the Wedge is in DDE Server mode, its default behavior is to strip out carriage returns by translating them to "Nulls" therefore we will also need to translate carriage returns back to carriage returns and not to nulls. Select "Pre Transfer Translation Table" from the DEFINE menu and select the comma character in the table and click the button marked "Translate". When the ASCII chart appears, scroll the chart down to the TAB character (ASCII 9) and click the OK button to perform the translation.

Next select the carriage return character (ASCII 13) in the translation table and click the "Translate" button again. When the ASCII chart appears this time, select ASCII 13 and click the OK button. Finally, click the OK button in the translation table to return to the main menu.

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.)

Sub GetDataArray() Static StartCol as Integer, StartRow as Integer ' retain values between calls ' This sub performs a DDERequest for Field(1) in the Wedge and reads in a tab ' delimited array with carriage returns at the end of each line. It then fills a range of ' cells with the data. The native format for Excel data is tab delimited text with a ' carriage return at the end of each row of data. If we assign this type of data to a ' range of cells using the FormulaArray function, Excel automatically parses the data ' and fills it into the specified range. Chan = DDEInitiate("WinWedge", "COM2")   ' initiate DDE channel DataArray = DDERequest(Chan, "Field(1)")   ' request field(1) from wedge DDETerminate chan                ' terminate the DDE channel ' ************************************************************ ' the following code finds the upper x and y bounds for the variant array If StartCol = 0 Then StartCol = 1    ' Starting column where data will go in our sheet If StartRow = 0 Then StartRow = 1 ' set the starting row x = 1                           ' set default x dimension to 1 On Error Resume Next ' ignore errors (error occurs if array has one dimension) ' get upper bound of the array x dimension ' the following line will generate an error if the array is only a one dimensional array x = UBound(DataArray, 2) ' by presetting the value of x to 1 and then ignoring errors, we end up with the correct ' value for x if we are only being passed a one dimensional array On Error GoTo 0 ' allow errors y = UBound(DataArray, 1) ' get upper bound of array y dimension If x = 1 And y > 1 Then x = y: y = 1 x = x + StartCol - 1 ' add offset from StartCol - this is the starting row y = y + StartRow- 1 ' add offset from StartRow - this is the starting col ' the following line fills up the cells in the range starting in "StartCol:StartRow" ' with the data from the variant array Sheets("Sheet1").Range(Cells(StartRow, StartCol), Cells(y, x)).FormulaArray = DataArray ' uncomment the following line to store each successive array to the right of the previous input ' StartCol = x + 1    ' update the starting column ' uncomment the following line to store each successive array below the previous input StartRow = y + 1     ' or update the starting row End Sub

The example above sets up the Wedge to issue a DDE command to Excel forcing it to run the subroutine "GetDataArray ()" after each complete array is received from the serial device. The "GetDataArray" subroutine performs a DDERequest to the Wedge that returns the contents of FIELD(1) to a variant array variable named "DataArray". The entire array is then assigned to a range of cells in "Sheet1" using the FormulaArray function. The example above assumes that the open workbook contains a worksheet named Sheet1.
Note: This subroutine will also work with non-array type data (i.e. single data values). Single data values are actually equivalent to a single dimension array containing a single data element.

Related Links

Getting past the 40 field limit in WinWedge
Excel DDERequest Function Issues