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:
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:
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.
The ‘Robust Data Logging for Free’ eBook is also available as a free download. The link is included when you subscribe to ACC Automation.
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
Hi Jeff,
AdvancedHMI can be found on their website. Here are a couple of links for the documentation:
https://www.advancedhmi.com/index.php?main_page=page&id=14&chapter=0
https://advancedhmi.com/documentation/index.php?title=Main_Page
In order to download AdvancedHMI, you will have to register. This is still a free programming package.
I hope this helps you out.
Regards,
Garry
Thank you, Garry.
jb
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
Hi Jeff,
Norton will block OstroSoft Winsock. You will have to disable Norton before downloading and installing.
If you are using AdvancedHMI then I would use VB.Net to communicate to an excel file.
https://docs.microsoft.com/en-us/previous-versions/office/troubleshoot/office-developer/automate-excel-from-visual-basic-net
Regards,
Garry
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
Hi Jeff,
Thanks for the update.
Garry