Excel Conditional Movement of Data

Excel Conditional Movement of Data

by

I have been recently asked how you can move data conditionally on an excel spreadsheet once the information has been obtained from the PLC? Monthly data was being collected from the PLC and put on an excel spreadsheet using Omron CX-Server DDE. They wanted at the beginning of each month to move the data to the appropriate month so they can track the changes.


Previous Posts – Excel Conditional Movement of Data

The following three blogs were posted on how to get data into the Microsoft Excel Spreadsheet from the programmable logic controller. (PLC)

HOW TO IMPLEMENT MODBUS TCP PROTOCOL USING VBA WITH EXCEL

IMPLEMENTING THE OMRON CX SERVER DDE AND EXCEL

HOW TO IMPLEMENT THE OMRON HOST LINK PROTOCOL PART 2 – VBA

Excel Conditional Movement of Data

The method to move the data around the spreadsheet once the data has been obtained will be done by Visual Basic for Applications. (VBA)

Make a spreadsheet in excel as follows:
Excel Conditional Movement of Data

Operation:
When the current data in A2 or B2 changes, it will trigger a macro to execute. This macro will use the Worksheet_Change to trigger the code. The date in A2 is then compared to all of the dates located from A5 to A30. If a match is found then the value in B2 is placed beside the matching date found in the column. Example: A2 = A7 then B7 = B2
Here is a look at the VBA editor and code:
Excel Conditional Movement of Data

Here is the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$A$2” Or Target.Address = “$B$2” Then
For x = 5 To 30
If Sheets(“Sheet1”).Range(“A2”) = Sheets(“Sheet1”).Range(“A” & x) Then
Sheets(“Sheet1”).Range(“B” & x) = Sheets(“Sheet1”).Range(“B2”)
End If
Next ‘x
End If
End Sub

Download the example excel file.

Watch on YouTube: Excel Conditional Movement of Data


If you have any questions or need further information please contact me.
Thank you,
Garry



If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLCs are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII, and Floating Point.

To get this free article, subscribe to my free email newsletter.


Use the information to inform other people how numbering systems work. Sign up now.

Excel Conditional Movement of Data

The ‘Robust Data Logging for Free’ eBook is also available as a free download. The link is included when you subscribe to ACC Automation.


7 thoughts on “Excel Conditional Movement of Data”

  1. Hi Garry,
    As you recommended (on ADC’s forum), I am exploring AdvancedHMI, for my customer’s application. I installed the Virtual Studio and AdvancedHMI. I have implemented your ModbusTCP example (working!)

    When I went to download the manual for AdvancedHMI, from the SourceForge website, they indicate that AdvanceHMI is no longer hosted on their site (although AdvancedHMI is still on SourceForge…)

    My application needs access to Excell spreadsheet(s), for which you recommended Visual Basic and OstroSoft Winsock.

    Is the AdvancedHMI manual available elsewhere?

    Thank you!

    Jeff Brodhead
    Brodhead Digital

    Reply

  2. As follow-up to my previous comment, the following is from a file called “AdvancedHMI.txt” which was downloaded during my attempt to download the AdvancedHMI manual:
    “The AdvancedHMI software will no longer be hosted on SourceForge.

    Please visit then following for the latest information and downloads: https://www.advancedhmi.com ”
    ~~~
    Oh, one other thing, the link (above) to download OstroSoft Winsock is immediately blocked by Norton 360, as a malicious website, when I attempt to open it.

    Regards,
    Jeff Brodhead
    Brodhead Digital

    Reply

  3. I’m sorry, Garry. After Norton 360 blocked the oswinsck.exe link (in “How to Implement Modbus TCP Protocol using VBA with Excel”), apparently Norton 360 decided to say that OstroSock.com is okay. It ran the installation.

    The report created by Norton 360, indicated that the program has been used by many in the Norton community, for many years, but that origins are not known… I have never seen such confusion on Norton’s part. (malicious, so block it, but it is okay. [paraphrased])

    So, never mind. Feel free to delete this and my previous comment, or not.

    The manual is still desirable.
    jb

    Reply

Leave a Comment