I am using rslinx to populate row 2 of a Excel sheet. I have a macro that will copy the real time data from row 2 and insert a new row below and paste the data in the new row. The data is in cells A2-N2. In cell O2 I have a bit value from my PLC (1 or 0). When O2 goes from 0 to 1 the data gets copied and pasted as it should. The problem is when O2 goes from 1 to 0 the data gets copied and posted also. I am not good at VB code at all. If anyone could look at the attached file and make some suggestions or write a proper code it would be a great help.
Excel/vba code help
- Thread starter bwiring
- Start date
Here is the current macro:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("o2")
If Not Intersect(Target, VRange) Is Nothing Then
SnapShot
End If
End Sub
Sub SnapShot()
Range("A2:N2").Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3:N3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("o2")
If Not Intersect(Target, VRange) Is Nothing Then
SnapShot
End If
End Sub
Sub SnapShot()
Range("A2:N2").Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3:N3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
This cures it....
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Trigger As Range
Set Trigger = Range("O2")
If Trigger = 1 Then
Trigger = 0
SnapShot
End If
End Sub
Sub SnapShot()
Range("A2:N2").Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3:N3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
You must reset the macro trigger before calling Snapshot, and I'm sure there's better ways to do this, i'm no vba expert
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Trigger As Range
Set Trigger = Range("O2")
If Trigger = 1 Then
Trigger = 0
SnapShot
End If
End Sub
Sub SnapShot()
Range("A2:N2").Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3:N3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
You must reset the macro trigger before calling Snapshot, and I'm sure there's better ways to do this, i'm no vba expert
This is a check that the O2 cell has changed.
after this IF statement snapshot should be called
OK, so i ran my previous vba code, and realised that the next time RSLinx re-reads a "1" into O2, the macro would be called again.
So here's a better version, and more understandable than using Intersect, which I believe is just telling you that cell O2 is on the worksheet! (I stand to be corrected on this).
Initially I had problems with the WorkSheet.Change event being re-triggered by the Snapshot code working, so suspending event handling temporarily cures this.
Here is a working version of the Worksheet_Change routine....
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Trigger As Range
Dim Trigger_Memory As Integer
Set Trigger = Range("O2")
If (Trigger = 1) And (Trigger_Memory = 0) Then
Application.EnableEvents = False
SnapShot
Application.EnableEvents = True
End If
Trigger_Memory = Trigger
End Sub
So here's a better version, and more understandable than using Intersect, which I believe is just telling you that cell O2 is on the worksheet! (I stand to be corrected on this).
Initially I had problems with the WorkSheet.Change event being re-triggered by the Snapshot code working, so suspending event handling temporarily cures this.
Here is a working version of the Worksheet_Change routine....
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Trigger As Range
Dim Trigger_Memory As Integer
Set Trigger = Range("O2")
If (Trigger = 1) And (Trigger_Memory = 0) Then
Application.EnableEvents = False
SnapShot
Application.EnableEvents = True
End If
Trigger_Memory = Trigger
End Sub
Thanks for all the suggestions but it still is nit working. How long does "o2" have to stay at a value of 1? Is there a way that when you test he code you do not change the value of "o2" manually? I'm not sure if this would be two areas that may be causing me troubles. Again thanks for all the help.
I have tested my code manually and it works fine.
Just long enough for the Worksheet_Change event to be recognised. The poll rate of RSLinx would guarantee that it is a "1" long enough. Perhaps the problem is that it isn't a "0" long enough for RSLinx to read into O2 - check your PLC code.
I can hook this up to a PLC later and test from there.....even better if you post the PLC and Excel code so I can test what you have, or p.m. me if you can't post the code.
It would be great if you could actually hook this to a plc. The 0 is there much longer than the 1 is. I will try to post the plc code this evening. Basically B3:0/6 is put into cell o2. this bit is high when I want to run the macro.
Hi I helped you wuth the question you asked
With regard to your problem now, is the idea that you log info as a test... I mean not a permanent logger, or are you looking to log all day.
Cheers
rdrast
Lifetime Supporting Member
Well, no data server is going to catch a transient change consistently. They just aren't fast enough (you can't poll that fast).
If you need to record based on something transient, use it to latch a bit on for a longer duration, say a second, and then reset it. Better yet, have it latch a bit on, then have your client software (the one watching it) set it off again when it is done processing.
Similar Topics
Hello friends;
I found a code for VBA use in excel to read the Tags values from RSlogix 5000 through RSlink. The code is here:
Dim OPCServer1 As...
I need to create and write an Excel Application using VBA code using Factory Talk View 7.0.
Thanks a lot.
Hello to all,
A contractor has written a horsepower calculation using Excel 2000 VBA. My responsibility is to DDE data in and out of the...
Hi.
I have a task of creating checklists for a plant that contain many standard items.
My idea is to do this in Excel, whereby in one sheet I...
Hello, I am looking for help writing to PLC tags from Excel. I am using Factory Talk Gateway as an OPC server and Excel VBA to write out to an...
