Using Excel to Populate CLX Tags

[Chuck Woodbury]

I have a UDT with 166 Members,part of which has the 40P module definition. As part of the upgrade from a PowerFlex 40P to a PowerFlex 525, this tag gets deleted and recreated with the new datatype with the 525 module definition embedded. Uploading the data to excel is simple using the DDE Link available on the RSLinx OEM or Gateway. What is shown below is the vba code to use a command button to POKE that data back to the new tag.
I have set up my spreadsheet with all of the data for every VFD in the plant. All we have to do is enter the motor number in the cell and all of the Tags on Sheet 2 are changed and the data is populated using the VLookup from Sheet 1.
Push the CommandButton21 labeled "POKE" and it runs and populates the tag with that data. With over a thousand VFD's to upgrade, this cuts out any chance for fat fingering the data and reduces the amount of time it takes to upgrade a VFD.
Sorry about the way it copied to here but all of the REM statements begin '

Private Sub CommandButton21_Click()

'Set up Counter for the For/Next Loop
Dim LCounter As Integer

'Open the DDE Channel to the ControlLogix Processor via RSLinx
DDEChannel = Application.DDEInitiate(app:="RSLinx", topic:="Trainer_Room")

'Begin For Next where the Tags begin at row 3 and end at row 168
For LCounter = 3 To 168

'Define the Tag as a DDEItem and where it is located so the first tag is at Cells(Row3(variable LCounter), ColumnC(3))
DDEItem = Cells(LCounter, 3).Value

'Set the RangeToPoke where the data has to be converted to a range and the cell the data is in is the variable row LCounter column H (8)
Set RangeToPoke = Worksheets("Sheet2").Range(Cells(LCounter, 8), Cells(LCounter, 8))

'Poke the Data from Range to Poke
Application.DDEPoke DDEChannel, DDEItem, RangeToPoke

'Step the counter and do it again
Next LCounter

'When the Counter finishes (168) terminate the DDE Connection
Application.DDETerminate DDEChannel

End Sub

 

[Archie]

Out of curiosity, how many seconds does it take to write 166 values to the PLC going through RSLinx?

 

[ASF]

Earlier this year I used this approach to populate a 250-element array of UDT's, with 6 floating point values and 2 string values each. It took about 3-4 seconds.

 

[AshleyParr]

We have recipes controlled by the brewhouse on an excel sheet, when they make changes they can download one or all of them by brand.

So far all of them by brand (can be up to 300) takes between 13-20 seconds, yup i put a timer in as i was curious

Ash

 

[arlenjacobs]

Good idea Chuck. Thanks for sharing.


Side topic: if you want to speed up your VBA code, try the ideas in the post below. Those make big improvements in the code when using many cells, or loops.
But the RSLinx DDE will likely be the slowest part of your loop anyway.

Read/Write large blocks of cells in one operation
https://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/#Qyav4LBKu6iihiPC.97