Excel VBA to CLX data dump

[ldrewes 0]

Good afternoon,
I'll start by explaining what i want to do and then what I've tried. We have an excel document at work that contains our material batch information and there is a column of data i would like to be able to dump into our control logix processor. This data could potentially need updated on a weekly/monthly basis so I am trying to find an easy way to do the transfer. I know how to do the DDE connections and can get individual cells to show or write individual tag information. But what would be the best way to take a whole column of data and dump it into a sequential array in logix? I did find a DDE VBA example that did this however I have not been successful. I got the example to work in its original setup using my DDE connection information but when I tried to change it to do what I needed, RSLinx crashed and it just keeps not working or doing anything. Is there a numerical limit on how many i can write in this one operation and also the data i am trying to write is going into a string datatype. Does that matter?
I will admit I have a VB code reading and understanding skill of about a first grader.

Here is the VBA code
Private Function OpenRSLinx()
On Error Resume Next

'Open the connection to RSLinx
OpenRSLinx = DDEInitiate("RSLINX", "CND_EXCEL")

'Check if the connection was made
If Err.Number <> 0 Then
MsgBox "Error Connecting to topic", vbExclamation, "Error"
OpenRSLinx = 0 'Return false if there was an error
End If

End Function

Private Sub CommandButton2_Click()

rslinx = OpenRSLinx() 'Open connection to RSlinx

'Loop through the cells and write values to the CLX array tags
For i = 0 To 50

'Get the value from the DDE link
realdata = DDERequest(rslinx, "Batch_DB_Test[0][" & i & "],L1,C1")
'If there is an error, display a message box
If TypeName(data) = "Error" Then
If MsgBox("Error reading tag Batch_DB_Test[0][" & i & "]. " & _
"Continue with write?", vbYesNo + vbExclamation, _
"Error") = vbNo Then Exit For
Else
'No error, place data in CLX
DDEPoke rslinx, "Batch_DB_Test[0][" & i & "]", Worksheets("Sheet2").Cells(2 + i, 7)
End If

Next i

'Terminate the DDE connection
DDETerminate rslinx
End Sub

Is there anything obvious I am doing wrong? Is this the best/easiest way to do this? I don't have any experience with RSQL or anything like that which I think might be the "Right" way to access databases or large amounts of information but again i have zero experience in that. Sorry for the really long post but I am pretty stuck and I'm almost sure this is possible...but maybe not. I always appreciate all your help! Thank you!!
Let me know if there is any needed information I left out.

[BobLfoot 301]

It has been quite a while since I played with DDE thru RSLinx to a Rockwell PLC.  Years Ago (10 plus) I was quite the guru doing it to SLC500, PLC5 and ControLogix all with DDE.  Then MSoft depracated DDE and pushed OPC.  I changed jobs where I no longer needed to push DDE data.  And I got quite rusty.

You can definitely do what you describe.  IF we assume for a minute you have an array of strings named "RECIPE_VALUE".  You will have to write to the actual string data to RECIPE_VALUE[1].Data and the length of the string to RECIPE_VALUE[1].LEN for the PLC to consider the data valid and usable.

I'd also suggest looking at some OPC VBA examples that are out there.  OPC is the future.  

 

 

[ldrewes 0]

17 hours ago, BobLfoot said:

It has been quite a while since I played with DDE thru RSLinx to a Rockwell PLC.  Years Ago (10 plus) I was quite the guru doing it to SLC500, PLC5 and ControLogix all with DDE.  Then MSoft depracated DDE and pushed OPC.  I changed jobs where I no longer needed to push DDE data.  And I got quite rusty.

You can definitely do what you describe.  IF we assume for a minute you have an array of strings named "RECIPE_VALUE".  You will have to write to the actual string data to RECIPE_VALUE[1].Data and the length of the string to RECIPE_VALUE[1].LEN for the PLC to consider the data valid and usable.

I'd also suggest looking at some OPC VBA examples that are out there.  OPC is the future.  

 

 

BobLfoot,

thanks for the reply. So I would have to convert the string or text information in excel to SINT format before i tried to send it for the PLC to accept it? Is this the same for OPC also?

I agree with you that OPC would probably work better. I have looked at some OPC examples and as i mentioned not being very good with understanding VBA when i compared the DDE example to the OPC example, the DDE look like way less of a nightmare to figure out. But it looks like I will start looking down that route. 

Edited 22 Sep 2020 by ldrewes

[pcmccartney1 141]

I believe a string has two parts, .Data an array of SINT of the .LEN as a DINT. 

By default, a STRING data type is defined as .Data being SINT[82] with .LEN as DINT.  That does not mean that the PLC programmer could have defined a different data type with a shorter array.  You'll need to know for each tag, the exact length.

Edited 22 Sep 2020 by pcmccartney1

[BobLfoot 301]

@ldrewes Like I said it was 12 years ago, but this sample I provided worked with Logix 5000 Version 15 and Excel 2007.

 

[pcmccartney1 141]

Strongly urge you to look at Bob's example.  There is a reason why he is the Wizard!

[ldrewes 0]

Holy Crap, That is exactly what I needed! You are indeed a wizard good sir. I can't thank you enough! 

Now i'd be interested in looking through the VBA to figure out how it all works.

Thanks again!

(BobLFoot)It wouldn't let me post the "quote" but I'm refering to your Excel File you attached)

 

Edited 22 Sep 2020 by ldrewes

[BobLfoot 301]

@pcmccartney1Spend 35 years fighting Balrogs and other Industrial Automation Gremlins and you'll be able to claim the title Wizard as well.  Not that you're any slouch of a Guru yourself! LOL!

[pcmccartney1 141]

Getting there, only 31 years.

I have to support an excel application for the last 15 years.  Unfortunately, there is no string data involved, only numerical data.  So I'll always be referring to your example.

Edited 23 Sep 2020 by pcmccartney1

[BobLfoot 301]

That example is taken from a live system where I was up and downloading production recipes, but then stripped and made generic so I didn't share any proprietary/confidential stuff.  Wish I could have kept a copy of the sheet I wrote for my employer at the time, but per agreements they owned the work product.