How do I get SLC500 information into an excel sheet? 1

[maxipaz]

Actually I'm trying to use VBA commands available in excel and I've got some results, but my problem is how can I get the macro working every, lets say 60 seconds, by itself and bringing data into the excel worksheet.
Could anybody help me?

 

[xyzz]

With a DDE driver formula in a cell you do not need to reschedule the reading from PLC since it is performed permanently.
For writing to PLC, which is performed by a macro subroutine execution, you can use vba "OnTime" method to schedule the next execution of the macro.

See also thread791-58495

 

[maxipaz]

Thanks for your reply
With this method I'll be able to refresh the data value into a single cell.
What I'm trying to do is perform a historical report of many variables in the excel datasheet.
Does anybody know how to do this?

 

[xyzz]

What exactly do you call a historical report?
Looks like your macro (working every 60 sec) will have nothing with reading from the PLC, but be a kind of data transfer to report area.
And the reading from PLC will still be performed by cells formulas permanently.

 

[maxipaz]

What I trying to mean by historical report is to bring data from the PLC (integers, floating, bits) via DDE into excel datasheet every 60 secs.
Every reading (performed every 60 secs) should be paste in the next row found empty by the macro in the excel datasheet.
I hope, I made my self clear.
Thank in advance

 

[xyzz]

maxipaz,
I suggest you the following.
Create a sheet in your Excel workbook named Report or History (or whatever you prefer).
Use row 1 for headers and fill row 2 with any necessary formulas, including DDE links and real time clock/calendar for report records marking.
Determine the last row for your report sheet, up to maximum 65536.
Create a macro that will:
1. Delete the last row n of the report.
2. Shift down rows 3 to n-1.
3. Copy values from row 2 to row 3.
Execute this macro at any time by any method, OnTime being the most straightforward for scheduling.
As a result, you will have the Report/History of n-2 records in reverse chronological order.

 

[IRstuff]

btw. Have you tried posting this question on the Tek-Tips site?

TTFN

 

[chakorules]

I've done this before. See my download posted here:

http://www.mrplc.com/dl/index.php?action=view&view=entry&entryid=74

Basically I used a worksheet all by itself and used the VBA command "ON CHANGE" to launch the macro. So my PLC wrote data to a sheet all by itself, when the data on the sheet "changed" or "ON CHANGE" event, then I excuted a macro. Check out my download above.

Good Luck

Chris Elston
Automation & Controls Engineer

http://www.mrplc.com

Download Sample PLC Ladder Logic Code
at MrPLC.com