Excel vba to control logix data dump

[ldrewes]

Good evening,
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.

 

[JaxGTO]

I use the selection object. Highlight the addresses I want and assume the value is in the next column.



for each a in Selection
ddevaluetosend = a.Offset(0, 1).Value
{write ddevaluetosend using whatever DDE you normally use}

next a

 

[ldrewes]

I use the selection object. Highlight the addresses I want and assume the value is in the next column.



for each a in Selection
ddevaluetosend = a.Offset(0, 1).Value
{write ddevaluetosend using whatever DDE you normally use}

next a

I appreciate your response but unfortunately as I mentioned I don't really understand VBA too well. I kind of understand what you said to do but i don't know where i would put that in the code. And the part in brackets at the end is throwing me off as well. Again i apologize for my ignorance in this, I'd love to learn VBA at some point. Seems like a pretty powerful language to learn when it comes to these things.

 

[glenncovington]

Is the variable in the CLX created with array = or greater than the number of values you are pushing to it? Is the type of variable a match?

 

[ldrewes]

Is the variable in the CLX created with array = or greater than the number of values you are pushing to it? Is the type of variable a match?

The CLX array i made is a string array with 200 elements and right now i'm trying to push 50 variables/elements to it. I guess i think its a match. The variables in excel are letters and numbers so i would think that would need to go into a string datatype in the CLX.