ML 1100 to Excel

Martin T. · Feb 28, 2007

ML 1100
This is my first attempt to setup a datalogging program. I was going to use the DataLogReader from AB site but when it is setup for a schedule event it saves the data into a seperate excel file everytime. This was not exceptable for my boss.

So I found a Simple Excel PLC Data Logger that I downloaded from MRPLC.com. Change it a little, I've been able to set it up and retreive the data needed. And everything seemed to be working fine.

This needs to collect the data 24/7, its just a logger to let the upper management know when it is cutting and when it's not.

The Problem I am having is the last 2 days I have had it running when I left work, come in the next day and the excel file has closed itself (of course it stops collecting the data). I not familiar with it enough to understand why.

As anyone hade this hasppen to them? Can someone look at how I have the Excel file setup and let me where I may have messed up? The 2 minute time is just for bench testing, it will be reset to 10 when in operation.

Thanks

SNK · Feb 28, 2007

Can you guarantee that the application has not been closed by a human?
Is the PC locked? Maybe you can synch the times, and pinpoint the actual time that it stopped logging. (roughly)
Excel doesn't just close on its own............

Martin T. · Mar 1, 2007

SNK said:
Can you guarantee that the application has not been closed by a human?

I am for sure it was not closed by anyone, my bench is in a room(can't call it an office, more like a closet) only I have access to.

Excel doesn't just close on its own............

Never would have believed it but I started it yesterday and a few hours later when I was watching it, it closed all by itself and I wasn't even doing anything on the computer at the time.

I was using DF1 comms while waiting for IP from IT, went ahead and set it up for ethernet started it again and it ran all night. I guess I was having a conflict using DF1.

TimWilborne · Mar 1, 2007

You know the Micrologix 1100 has its own data logging built in
http://www.plctalk.net/qanda/showthread.php?t=28776

Martin T. · Mar 1, 2007

TWControls said:
You know the Micrologix 1100 has its own data logging built in
http://www.plctalk.net/qanda/showthread.php?t=28776

Yes, I tried to use it but when use do a scheduled event it saves the data when polled to a seperate excel file. Ran it like that for a while and had way too many excel workbooks with 1 line of data.

SNK · Mar 1, 2007

I guess it's fixed then?
If all else failed, I would try another computer.......

Martin T. · Mar 2, 2007

SNK said:
I guess it's fixed then?
If all else failed, I would try another computer.......

Well I thought it was fixed, came in this morning to find it shutdown again. Gonna hafta try another computer.

Martin T. · Mar 16, 2007

Update:

Tried another computer, same thing is happening.

Anyone have any idea? This is driving me nuts..

JesperMP · Mar 16, 2007

Maybe the Windows system event viewer can reveal any problems. You can try and look for the time of the last log value.

edit: This Excel file, does it just log to itself or does it write to a disk file ?
If it is the first then it is very fault-intolerant solution. Any problem with the PC or Excel itself will mean that you loose data.

Martin T. · Mar 16, 2007

JesperMP said:
edit: This Excel file, does it just log to itself or does it write to a disk file ?
If it is the first then it is very fault-intolerant solution. Any problem with the PC or Excel itself will mean that you loose data.

It's not saving it to a disk, when I re-open excel it gives me a recovery file from the last auto-save. Tried using the event viewer but didn't show anything abnormal.

Martin T. · Apr 18, 2007

Here's an update if anyone is interested:

Finally found out that the code for the timer (API) to pop and grab the data, it has been known to cause excel to completely close by itself. Changed the way excel calls to log the data and it been running fine for weeks.

SNK · Apr 18, 2007

Would you mind posting your changes?
If this was grabbed from MRPLC, is the software application buggy?
If so, maybe we can have the application fixed, so that others can benefit from your "unfortunate" dilema with the software.
Thanks for your update.

Martin T. · Apr 18, 2007

SNK said:
Would you mind posting your changes?
If this was grabbed from MRPLC, is the software application buggy?
If so, maybe we can have the application fixed, so that others can benefit from your "unfortunate" dilema with the software.
Thanks for your update.

The original one I grabbed was from MRPLC which is the file in my first post. I'll post the updated file and try to make some notes on the changes.

Martin T. · Apr 18, 2007

This is how the original started the logging.

Start Logging Button:

Private Sub CommandButton1_Click()

TimerSeconds = 120 ' Enter a value in here to set the rate on

' how often to "pop" the timer.

' Default value 1 second.

' Value can be less than one second (eg 0.5).

 

TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)

End Sub

This was the timer function to grab the data and send it to the Readings Sheet:

 Public Declare Function SetTimer Lib "user32" ( _

ByVal HWnd As Long, ByVal nIDEvent As Long, _

ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _

ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long

Public TimerSeconds As Single

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _

ByVal nIDEvent As Long, ByVal dwTimer As Long)

'

' The procedure is called by Windows. Put your

' timer-related code here.

'

Sheets(2).Range("A65536").End(xlUp)(2, 1) = Sheets(1).Range("H17")

Sheets(2).Range("B65536").End(xlUp)(2, 1) = Sheets(1).Range("H24")

Sheets(2).Range("C65536").End(xlUp)(2, 1) = Sheets(1).Range("B3")

Sheets(2).Range("D65536").End(xlUp)(2, 1) = Sheets(1).Range("B18")

Sheets(3).Range("A65536").End(xlUp)(2, 1) = Sheets(1).Range("H17")

Sheets(3).Range("B65536").End(xlUp)(2, 1) = Sheets(1).Range("H24")

Sheets(3).Range("C65536").End(xlUp)(2, 1) = Sheets(1).Range("B6")

Sheets(3).Range("D65536").End(xlUp)(2, 1) = Sheets(1).Range("B21")

Sheets(4).Range("A65536").End(xlUp)(2, 1) = Sheets(1).Range("H17")

Sheets(4).Range("B65536").End(xlUp)(2, 1) = Sheets(1).Range("H24")

Sheets(4).Range("C65536").End(xlUp)(2, 1) = Sheets(1).Range("B9")

Sheets(4).Range("D65536").End(xlUp)(2, 1) = Sheets(1).Range("B24")

Sheets(5).Range("A65536").End(xlUp)(2, 1) = Sheets(1).Range("H17")

Sheets(5).Range("B65536").End(xlUp)(2, 1) = Sheets(1).Range("H24")

Sheets(5).Range("C65536").End(xlUp)(2, 1) = Sheets(1).Range("B12")

Sheets(5).Range("D65536").End(xlUp)(2, 1) = Sheets(1).Range("B27")

Sheets(6).Range("A65536").End(xlUp)(2, 1) = Sheets(1).Range("H17")

Sheets(6).Range("B65536").End(xlUp)(2, 1) = Sheets(1).Range("H24")

Sheets(6).Range("C65536").End(xlUp)(2, 1) = Sheets(1).Range("B15")

Sheets(6).Range("D65536").End(xlUp)(2, 1) = Sheets(1).Range("B30")

End Sub

Here is what I changed,

Start Logging Button:

Private Sub CommandButton1_Click()

StartDoingIt

End Sub

This is the how the data is grabbed now:

Dim dNext As Date

Sub StartDoingIt()

Call DoItAgain

End Sub

Sub DoItAgain()

dNext = Now + TimeValue("00:08:00") ' every 480 seconds

Application.OnTime dNext, "DoItAgain"

Call DoIt

End Sub

Sub DoIt()

Sheets(2).Range("B65536").End(xlUp)(2, 1) = Sheets(1).Range("B3")

Sheets(2).Range("C65536").End(xlUp)(2, 1) = Sheets(1).Range("B6")

Sheets(2).Range("D65536").End(xlUp)(2, 1) = Sheets(1).Range("B9")

Sheets(2).Range("E65536").End(xlUp)(2, 1) = Sheets(1).Range("B12")

Sheets(2).Range("F65536").End(xlUp)(2, 1) = Sheets(1).Range("B15")

End Sub

Sub StopDoingIt()

On Error Resume Next

Application.OnTime dNext, "DoItAgain", schedule:=False

End Sub

Here is the full Excel file that I'm using now, it has a couple of different features to it. It has automatic date and time stamp when the date is polled, the Save Data button will save the Readings Sheet to My Documents, the Clear Readings button is kinda odd but it's the only way I figure out how to do it and it works for what I need. The timer is easily changed.

If anyone has a better way of clearing the readings sheet, let me know. I can't take credit for code it's bits and pieces of stuff found on the net, except for the Clear Readings which isn't that great anyway.

Hope this helps.

ML 1100 to Excel

Martin T.

Member

SNK

Member

Martin T.

Member

TimWilborne

Lifetime Supporting Member

Martin T.

Member

SNK

Member

Martin T.

Member

Martin T.

Member

JesperMP

Lifetime Supporting Member + Moderator

Martin T.

Member

Martin T.

Member

SNK

Member

Martin T.

Member

Martin T.

Member

Similar Topics