Ive been given the job of making a data logging program using DDE comms for a Compactlogix system.
Ive taken a peek at how Chako does this on his program that he uploaded to the site, and I have a few questions.
1. Im slightly confused how the data is getting logged every 3 seconds. It seems to be controlled in the varCycle variable in some way. Is this a value that was set in the PLC? What I want to do is to be able to give the user the option of how often to take readings. Hopefully this is something that can be controlled through Excel.
2. Though I don't save my data to another file, what would happen if the data reached the end of the page? Would the program error out and lose all of the data?
Also, I'm not totally sure how to set up and use buttons on excel. Im hoping to have start and stop logging buttons along with enable and disable buttons for each of the data strings I will be logging. I cant seem to find any help on how to create these.
Thanks in advance
Full Version: ChakoRules' DDE Application question
| QUOTE (pete3589 @ Nov 1 2005, 11:14 AM) |
| Ive taken a peek at how Chako does this on his program that he uploaded to the site, and I have a few questions. |
I think Chako should "chime in" to explain....
1.) I believe the plc sends a "1" to a HotLinked cell on a worksheet in the workbook then you can use the "Public WorkSheet_Change" Event in the Visual Basic editor, or monitor this hotlinked cell for a change in value, then run some code to Log the Data. If I remember correctly this is on a hidden sheet.
There are ways to have Excel control the updates using the "TIME" Method. For example this will run the SubRoutine LogData every 5 sec.
NextTime = Now + TimeValue("00:00:05")
Application.OnTime NextTime, "LogData"
2:)In Excel 2000 you can log to cell 65536 then it will not log past I believe it just will not lwet you log any further although I do not believe it would close the WB nor would you loose any data logged before cell 65536
3:) In Excel go to View -> Toolbars -> Control ToolBox on this toolbar you will see and item Called a Command Button this can be used to start and stop your logger.
You may also want to look at this download:
http://forums.mrplc.com/index.php?act=Downl...&CODE=02&id=432
There are ways to have Excel control the updates using the "TIME" Method. For example this will run the SubRoutine LogData every 5 sec.
NextTime = Now + TimeValue("00:00:05")
Application.OnTime NextTime, "LogData"
2:)In Excel 2000 you can log to cell 65536 then it will not log past I believe it just will not lwet you log any further although I do not believe it would close the WB nor would you loose any data logged before cell 65536
3:) In Excel go to View -> Toolbars -> Control ToolBox on this toolbar you will see and item Called a Command Button this can be used to start and stop your logger.
You may also want to look at this download:
http://forums.mrplc.com/index.php?act=Downl...&CODE=02&id=432
Basically the problem with DDE and EXCEL is that some how you have to create an event to drive your macros within EXCEL.
In other words, VB code is dumb, it needs an event of some sort to trigger the code so it will execute. The only way I could come up with a way to trigger the VBA script using a toggle from the PLC was with a hotlink DDE topic.
So what I did was put a hotlink on a hidden workbook, then there is an event you can use within VBA called "on data". Either a 0 or 1 change of state on that hotlinked workbook would fire a macro called "Start"
Unhide the workbook to see it within EXCEL.
In other words, VB code is dumb, it needs an event of some sort to trigger the code so it will execute. The only way I could come up with a way to trigger the VBA script using a toggle from the PLC was with a hotlink DDE topic.
So what I did was put a hotlink on a hidden workbook, then there is an event you can use within VBA called "on data". Either a 0 or 1 change of state on that hotlinked workbook would fire a macro called "Start"
Unhide the workbook to see it within EXCEL.
| CODE |
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 in the "start" sub routine, once the VBA code got to excute, I double checked two things. First if the varcycle bit was set and if logging was enabled. So really all those two bits are for conditionals. Even if the data changed on the hotlink DDE workbook, those two bits had to be true before any data was logged in the spreadsheet. Mainly because, I had to set the hotlink back to ZERO, when the hotlinked changed from 1 to 0, this caused the "start" macro to excute again, when it did excute, varcycle was false, and logging was true, so then no logging would take place. Once DDE workbook was 0 again, then I would toggle 0 to 1 and then varcycle would be true and logging would be true, then it would capture a record to the spreadsheet. So you see why I used varcycle now? I wanted to log the number when the change of state happened from 0 state to 1 state. But eventully I had to turn it back off from 1 state back to 0 state. When I did this, excel saw this as an "on data" event and excuted my macro again...well I don't want to log any numbers when this change of state happened, only the change of state from 0 to 1.
Hope that makes sense.....
As for question #2, I think TechJunki covered the basics on what you need to know there as well as came up with a good solution and link for you to check out if you do not need to save a new file everyday like I did.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.