Advanced Excel tricks: reading large arrays of data into Excel

Understanding Excel data format

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

456,789,123,456

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   456   789   123

456   789   123   456

etc...

Note: When WinWedge 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.

Complete setup example

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:

123, 456, 789, 123

456, 789, 123, 456

789, 123, 456, 789

123, 456, 789, 123

456, 789, 123, 456

789, 123, 456, 789

WinWedge DDE server settings

Remember to set WinWedge up in DDE Server Mode! See the DDE Server Mode Setup Guide. DDE Application Name: Excel DDE Topic: System DDE Command: [RUN(“GetDataArray”)]

Excel instructions

Create or edit a general VBA code module and enter the following code in the module:

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.

Support articles

Advanced articles

Legacy articles

Resources

Advanced Excel tricks: reading large arrays of data into Excel

Understanding Excel data format

Complete setup example

WinWedge DDE server settings

Excel instructions

​Understanding Excel data format

​Complete setup example

​WinWedge DDE server settings

​Excel instructions

Understanding Excel data format

Complete setup example

WinWedge DDE server settings

Excel instructions