CLX to Excel Multi Array

I have created a 2 dimensional array in one of my processors.
the array is called PPM_LoadingArray, this array has 10 columns each with 1440 rows.
Which is equivalent to 24 hours into minute blocks, it is used to track three product counts, also product code plant speed and date information
I have been pulling this data through RS linx OEM active topic to excel.
My problem is if I try to run the excel spreadsheet for more than 4 columns excel will crash. Probably down to the sheer volume of data.
I was wondering if there's a clever way to get excel to do a staged update of the 10 column fields.
My excel knowledge can be optimistically described as basic.
Does anyone now of such a method

Leigh

lsheridan said:

I have created a 2 dimensional array in one of my processors.
the array is called PPM_LoadingArray, this array has 10 columns each with 1440 rows.
Which is equivalent to 24 hours into minute blocks, it is used to track three product counts, also product code plant speed and date information
I have been pulling this data through RS linx OEM active topic to excel.
My problem is if I try to run the excel spreadsheet for more than 4 columns excel will crash. Probably down to the sheer volume of data.
I was wondering if there's a clever way to get excel to do a staged update of the 10 column fields.
My excel knowledge can be optimistically described as basic.
Does anyone now of such a method

Leigh

Click to expand...

A simple, but not all that reliable, way is to have a single column transfer the information.

1 - Add a column 11 to your array.
2 - Have your excel macro send a number from 1 - 10 to the PLC.
3 - The PLC detects the index number change and copies the correct column data to column 11.
4 - Excel delays for a few seconds to make sure that the Excel links have all updated
5 - Excel then copies the column 11 to whatever column it should be stored in
6 - Excel increments the index and it is written to the PLC

Repeat from step 3 until the index reaches 10.

Welcome to the Forum!

It's probably not the volume of data that is causing Excel to crash. I've got spreadsheets that have 10's of thousands of data points that run just fine.

You don't say exactly how you are trying to pull data from the PLC to Excel. As you must be aware, the equation in Excel is of the form:

=RSLINX|Topic!'ArrayTag[a,b],Lx,Cy'

This can be created in one of two ways. It is possible (I've done it) to browse in RSLinx under the Edit >> Copy OPC/DDE Link option, find the tag, and then select an array of cells in Excel that is 1440 rows by 10 columns wide, paste special (as link) the link in, and edit the default L1,C1 to be L1440, C10, and actually get the whole shebang in one link.

I don't recommend this, however. It's difficult to edit the sheet, and (as I recall, it's been a while), Excel tends to get a bit flaky. Perhaps this is what you are doing, and getting the crashes you report.

A better approach is to merely select the [0,0] tag, and paste it in with the default L1,C1 values. Then copy that cell 10 columns across, and edit the [0,0] by hand to [0,1], [0,2], and so forth. Then copy those 10 cells to make 10 rows. Then select the second row, and do a search/replace of [0 to [1, the second row to [2, and so on. Then copy those 10 rows 10 times, and then select the second set of 10, and search/replace [ to [1, making [0,y] to [10,y], [1,y] to [11,y] and so forth.

Lather, rinse, repeat.

Should take you about 20 minutes to have the entire 1440 x 10 array all linked.

Good luck.

See attached file

Isheridan:
My topic is called Mesa
Please see my macros
Macro "Ingresar" is to write from excel to plc
Macro Revisar is to write from plc to excel
This array has 35 elements, but I have another excel files with up to 400 elements.