How to "Stack" PLC data table value in Excel sheet

Hi, every one,

I Used Rslinx DDE/OPC function copied a data table value (N7: XX) and paste it as a link to a cell of Excel sheet. The pasted value is changing every 30 seconds.



Does any one know how to copy this value to other cell in the same sheet based on the changes of the PLC data value that happen in PLC data table?

What I mean is how to "Stack it up in Excel". So I can use it to build a chart.



I done it with a "COP" instruction stack it up in PLC, then hot link 300 words to Excel for display and chart.

Data table

You could copy the N7:xx into a FAL table every minute and then copy the whole table into Excel in one move. On the other hand, you could "shift" the excel data at a predetermined time like every minute. This would be done via a script in Excel. Select the row, copy, select the new row below the old one and paste. Make sure the data is shifted down one row after each paste. Try using the record a maco and Im sure you can get this to work.

You should be able to write some VBA code for excel that can take a snapshot of the hotlink value and paste it into an incrementing row or column so that you don't beat up your network and still get the data you want for your chart.

I am not a VBA expert so I would be hesitant to try to write the code to do that, it would take me several hours of trial and error and poring over help files...

Paul

Data Table

Ok here is an example you can work from.

Step 1 - Create a directory called PLC C:\PLC
Step 2 - Open this file and enable macros and links
Step 3 - save this file as report.htm in PLC directory (very Important) Under Excel save as
a "web page".
I use HTM extension so the file can be opened in Internet Explorer.
Step 4 - Click on tools/ Macro / macros and run the "update" macro.
On the "Links" worksheet you can change the values in Colum "C" and "F".
The Data Table will save the values and shift them down one row on the
Data Table worksheet. The update is every minute.
The "Links" sheet is where you will put your PLC DDE values.
This is a very crude example, you will have to edit a new file to
the appearance you want. You will have to edit the Macro to suite your
page setup.
You just have to create a new worksheet linked to the data table to make
the chart you want.
Hope this gets you started.

Bruce :thank you for the reply.
I will try it tomorrow.
I like your idea that used HTM extension so the file can be opened in Internet Explore”。 this is mean every one in the company can review the excel report with the intranet

Thank you! paul.

Thank you for your reply. I will look into the VBA learn the tricks