EXCEL Time Graph

Hi there.
I am requesting information on how do I plot a graph of time vs temp in real time using data from a PLC via DDE.
The graph should display data in 30min intervals over a period of 24hurs.

One way is to have a graph sitting on a page. Already built with the X and Y field defined.

So, for example, the DATA field could be From A1 to A48 and the TIME field could be B1 to B48.

Now there is 2 way to do the transfer.

Primo you could index the values in 48 registers coresponding to what is read at the wright TIME and MOVED into them. This way you have all the values in 96 registers using the RTC of the PLC. You would upload them into there corresponding registers.

Secundo you could only have the value in one register and "decide" when to read it. Then move the read value in the corresponding (time) cell.

I always prefer to have the Data indexed in the PLC AND have "Today's Data in almost realtime AND yesterdays Data. At a certain time I move all the registers of today's Data into another suite so Today is emptyed again to start the new loggin.

This way I can shutdown the logginf PC without losing any Data. And BSOD won't kill your graph.

more information

I have the data in one cell and I want Excel to sample the data and store it in different cells so that I can plot it. It is also required that the date as well as the time be updated by excel auto...

Re-read my post.

What is it that you don't hunderstand?

Pierre-
I'm going to be doing something similar to Kabir, but more for the educational excersise than anything.
I think Kabir understands conceptually what he wants to do. He wants your second option. I think he is asking how to do this in Excel.
Kabir, I haven't done it yet but as far as I have been able to learn you will need to do this in a VBA script in Excel. There are a few VBA time functions and at least one Windows time function you can use to trigger your reads. You will need to perform the index and copy of the data in the VBA script.
I'm sure this has been done quite a bit before. I'm not into this real deep yet. But my next step will be a quick Google search to see what I can find. You may want to do the same.

Keith

I have done something like this before. One way to do it is to put a DDE link to one cell on an excel sheet that references a number in the PLC. Put an equation in the worksheet that adds the number or something. When it is time to get the data the PLC will change the number. This will trigger the Worksheet_Calculate event. Then you can build a VBA macro to read the data and put it into cells to graph.

Like I mentionned before there is many ways to do this.

I will details one way.

IF you want to have LESS DDE transactions you can do it like this.

You make a DDE link to ONE rgeister. This register contains the value you want to make a graph with.

In Excell you have this value in a cell. Lets say its cell A1.

Now to move this value into the range of cells that plot your graph you must use some parameters. By parameter I mean conditions like IF and THEN.

You left Right-click on the bottom of the sheet where its speeled Sheet1 and you chose to Visuallize the code (Gosh my Excell version is french so I don't have the good name for this menu but you should find it)

This code is a Visual Basic code which is part of Excell to help you make conditions and functions and macros.

Thats is where all actions will take place.

The conditions you have to program is something that says "IF the A1 cell has a value greater than 0 AND IF the actual time matches another cell where there is no value greater than 0 then MOVE this value into it"

For this to work you will have to buid a grapgh with real cells and see if it plots. Then you just empty the cells with the values, not the one with the time values. Those values (Time) correspond in hours and minutes to your 48 points i.e. 00:00, 00:03, 01:30, etc.

So you have to compare the data of the ACTUAL TIME, the Data of the cell corresponding to this Actual Time and compare the value of Cell A1.

Just try it with an empty Excell file and manually put some value in Cell A1. When this works link the cell to your PLC.

PS: I have used DDE a lot to send some Data to a server to be included in an htm page for an Intranet. Its a real cheap and robust way to have some browser enable Data viewing scheme with a very small footprint.

Hello,

There's an excellent forum on the net for this kind of questions, they even got a something especialy for Excel.
Follow this Link

Rudi

Guys, "chakorules" has done a lot of the work for you allready.
He has made an Excel spreadsheet that logs data off an SLC.

Find it HERE.

I dont think he has made it into graphs, but has taken care of a lot of the other basic things. You could take his work an add to it.

Macro Problem

Hey guys thanks for all your input so far.
I got the data into excel and created a few windows such as logger view where the data and time recorded can be viewed .
Setup as well a config windows and a graphs view

This is the problem that I have now
While the macro is running if you click on any of the pull down menu such as file, tools etc the macro goes into a pause and restarts when you come out of that pulldown. now what I wanna know is what can I do to avoid this problem.

The restart of my program uses a statement like this.

RunWhen = Now + Timeserial(0,1,0)
Application.OnTime RunWhen, "Control", , True

Kabir:

You can disable all of the menus in Excel. Make sure you leave a way to re-enable them though.

Marc

Yeah Well How

Is there a way that I can test for which of the toolbars that were enabled before I enter excel then when I exit restore all of them. The application will have to be working on the plant supervisors office andd hence I cant mess with there excel settings

Try this code. I used it on an excel spreadsheet a couple of years ago to turn off all the menus and toolbars at when excel opened the workbook and turned them back on when I exited the workbook. I also defined some keyboard macros "Cntrl - O" for turn off the menus and "Cntrl - N" for turn on the menus.
***************8
Turn off Menues
Windows("dataacq.xls").Activate

Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
Application.DisplayScrollBars = False
Application.DisplayExcel4Menus = False
ActiveWindow.DisplayHeadings = False

'Turn Off CommandBars
For Each cb In CommandBars
cb.Enabled = False
Next cb
********************
Turn on Menues
Dim cb As Variant

Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
Application.DisplayScrollBars = True
Application.DisplayExcel4Menus = True
ActiveWindow.DisplayHeadings = True

For Each cb In CommandBars
' cb.Protection = msoBarNoChangeDock
cb.Enabled = True
Next cb

Kabir,

bwheat has posted the code that you need. Make sure you put the re-enable into a workbook event (like Workbook Close) so that you don't accidentally lock yourself out of Excel. There are ways around these restricted menus, but you have to be pretty knowledgeable about how Excel manages its objects.


Good luck,

Marc

Yeah thanks fellas right now the loging program looging good with 24 different arears recorded I plan to take it to like 48 by next week
maybe more.

I sample every 5mins for 24 hurs

Now this is a next problem that I wanna do that i am going into some problems.
At the end of the 24hurs I want to save the readings in a text file (csv). Could anyone give me an example on how this is done
Or do you think that it is better to use a database instead