RSLinx to Excel triggering read with hotlink

[BillC]

I am completing a project that ties an excel spreadsheet to a micrologix through RSLinx. I want to limit the traffic and take advantage of block reads so I want to have a single 'hotlinked' bit trigger a marco that does all the 'coldlinked' reads. So far I cannot find a way to do this. Any suggestions?

 

[Vetteboy]

Macro1
Sub Logical_If()
If Sheet1.Range("A1").Value = 1 Then Application.Run "Macro2"
End Sub

Something like that......

 

[chakorules]

I do it like this:

I put my hotlink on a "sheet" all by itself.

Then in my macros I put:

Sub Auto()



    ' A bit change from the PLC Hot Linked on DDE worksheet

    ' will automaticly Run Macro (start).

    Worksheets("DDE").OnData = "Start"

    

End Sub

Then below I have the sub calles "start"

Sub Start()



Dim lngRow As Long

Dim varCycle As Variant

Dim varLogging As Variant

Dim varResults As Variant

On Error GoTo Error

    

 

    'opens a COLD DDE link

    RSIchan = DDEInitiate("RSLinx", "M1138")

    

    'assign PLC bit values to VB variant varibles

    varLogging = DDERequest(RSIchan, "B3/163")

    varCycle = DDERequest(RSIchan, "B3/161")

    

    'close COLD DDE link

    DDETerminate (RSIchan)

    

    'check to see if the Cycle bit went to "1" if it did, excute read data

    If varCycle(1) = "1" And varLogging(1) = "1" Then



        'starts at row 3 of sheet

        lngRow = 3

Hope that helps. If you want the whole excel dde sheet you can download it here: Click Here:

Good Luck

 

[BillC]

Thanks to Vetteboy and Chakorules....I guess I have the same question for both the suggestions. What I have been struggling with is how to run either of these suggestions on an event basis instead of having them run in some kind of infinite loop.

For example could I/should I put the Sub Logical_If() suggestion under the Workbook_SheetChange event. That way it would only run if something on the sheet changes. If not maybe you could suggest a good place to put it.

Also the Worksheets("DDE").OnData suggestion... I looked at the code-thanks. It looks like a sub that runs on opening the file has this code:
Done:
Application.Run Macro:="Auto"
NoMacros:
Could you talk a little about the mechanicals of this even though auto is called once it continues "run"?? Also maybe you could enlighten me on the OnData part of this. I saw that you moved the DDE hotlinks to another workbook??

Hope my questions make sense and thanks.....

 

[chakorules]

I too struggled with an EVENT triggered update. So the best way I was able to figure this out, was I put my hotlink on a workbook all by itself. If you downloaded that example, unhide the workbook called "DDE".

As far as my knowledge takes me, using Sub Auto () only excutes when there is a change on the sheet. So it's not infinitely looping like you think.

Knowing that you can see that putting it all by itself is important, and not on the same wroksheet you are trying to store data too. If any other data, or even a human invoked change to the workbook would also fire the Sub Auto () code to excute. Hope I explained that ok.

The other question you asked here:

Done:
Application.Run Macro:="Auto"
NoMacros:

All that part of the code is this...I wrote this code to extract the date from the computer time and made the file name the date of the log sheet. So at Midnight, the file name of the log sheet would save and open a new workbook with the next days date. So anyhow, if you wanted to open a workbook from a previous date, you don't want the code to append or log to an old sheet. Since I did a SAVEAS, all the macros get saved with each sheet. So I checked what file was opened and compared it to today's day. If the file name was older than today's date, I did not want any macros to run. So I made the code jump to the NoMacros: label. If you opened an exsiting log sheet, and it was still today's day, then it would start appending to the end of the sheet. Or if there was no file name that matched today's date, it would create a new file and start logging today's date.

So basically it was what I call the macro police. If I wanted it to log, then I told the code to run the macro "auto", which monitors a hotsheet update of the DDE workbook.

Hope that makes since.

 

[BillC]

Thanks alot ..rules. I am digesting this all now and starting to implement. It helps to know you went through some of the same struggles. It seems like something so obvious to do yet isn't necessarily a walk in the park. I am going to be gathering up to one hundred data points at a time so I definitely want this be on block reads and not hotlinks.....

 

[Vetteboy]

If Sheet1.Range("A1").Value = 1

This part of the statement runs macro2 only when the value in cell A! is true. That way you could have a bit in your PLC run your macro only when a change occurs

 

[BillC]

Thanks vette...don't mean to be ah slow or stubborn here but I understand that part. The issue I was stuggling with is that the IF statement needs to be constantly running to look for the change. I understand the second Macro will only run on a change to A1, but the first macro has to be running all the time to look for this change - in a way defeating the purpose.....Hope that makes sense- thanks for the help...

 

[soop7667]

I HAVE YOUR SOLUTION!

I just figured this out last night - in looking for a solution to my own application of DDE/OPC. Do this - Use a text box from the controls toolbox - set the linked cell property to that of your "Triggering Bit" cell address. Then when your "Trigger Bit" changes you can have it execute your macro procedure. - I also have incorporated check boxes to enable or disable the logging of information.

Example -

textbox_change ()
call YourMacro
end sub

or with a checkbox to enable or disable

textbox_change ()
if checkbox1.value = true then call YourMacro
end sub

I initially used this method of monitoring specific bits to trigger an embedded Windows Media Player. It plays .wav files which "say" if a machine has faulted out and what the fault is - for example...

(In this example I used a combo box - Works the same way as a text box does)

Private Sub cbxV3Sound_Change()

If Workbooks("DH+Plantlink").Sheets("plantlink").ChkSounder.Value = True Then
Workbooks("DH+PLANTLINK").Sheets("plantlink").V3Sounder.URL _
= ("C:\Documents and Settings\johng\My Documents\DH+ PLANT Data\Sound\") & (Range("i20").Value)
Else
Workbooks("DH+PLANTLINK").Sheets("plantlink").V3Sounder.URL = ("")
End If
End Sub

chksounder is my check box which enabled or disables Sounds.
V3Sounder is my embedded Windows Media Player.
Range("I20") contains the name of the wav sound which will be played.
It is also the cell linked to my triggering combo box.
If/then statements within the worksheet cells change the value of range("I20") depending on which fault bit is triggered.

I thought I was really on to something good when I figured out how to utilize combo and text boxes in this manner and by the looks of this post I might of been right. Hope this does something for you.