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>
Steps for setting up WinWedge:
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.
Steps for setting up EXCEL:
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.) |
