+ Reply to Thread
Results 1 to 5 of 5

machine monitoring

Hybrid View

  1. #1
    Registered User
    Join Date
    01-22-2007
    Posts
    6

    machine monitoring

    I've got a question here that may sound crazy but here goes.....

    This is what I'd like to be able to do:

    I have a CNC milling machine hooked up to my PC. I have the CNC program sending out info to the PC at the end of every part cycle. I use the machine's internal part counter and timer and it outputs the cycle time of each part.

    "PART 20 of 5000 CYCLE TIME- 430 SEC."

    At the end of every cycle the software on the PC receives this info and creates a new txt file, naming it NEW1, NEW2,......

    NEW "PART 20 of 5000 CYCLE TIME- 430 SEC."
    NEW1 "PART 21 of 5000 CYCLE TIME- 435 SEC."
    NEW2 "PART 22 of 5000 CYCLE TIME- 440 SEC."
    NEW3 "PART 23 of 5000 CYCLE TIME- 428 SEC."
    ETC.


    What I'm looking to do is have the most recent part number/cycle time
    be dropped into a specified cell in excel and overwrite the previous info.
    I only want excel to deal with one cell and the most recent info.

    The 2 things I don't know how to do are:

    1.set-up the cell to only "hold" the most recent input,and overwrite the last.

    2.set-up the cell to either "look" to the next text file during refresh(NEW1, NEW2,ETC.)
    or delete the file it just imported info from so that the next file created by the CNC will be the same
    name as the previous.

    If I can somehow get this to work I can send production info to our electronic bulletin board.


    ANY help you could give me would be greatly appreciated


    Thanks,

    Scott

  2. #2
    Registered User
    Join Date
    01-22-2007
    Posts
    6

    machine monitoring

    I'm sorry if anyone also read this thread on the new users forum, but after posting there, I thought this might definitely be a question for advanced programmers


    I've got a question here that may sound crazy but here goes.....

    This is what I'd like to be able to do:

    I have a CNC milling machine hooked up to my PC. I have the CNC program sending out info to the PC at the end of every part cycle. I use the machine's internal part counter and timer and it outputs the cycle time of each part.

    "PART 20 of 5000 CYCLE TIME- 430 SEC."

    At the end of every cycle the software on the PC receives this info and creates a new txt file

    NEW "PART 20 of 5000 CYCLE TIME- 430 SEC."
    NEW1 "PART 21 of 5000 CYCLE TIME- 435 SEC."
    NEW2 "PART 22 of 5000 CYCLE TIME- 440 SEC."
    NEW3 "PART 23 of 5000 CYCLE TIME- 428 SEC."
    ETC.


    What I'm looking to do is have the most recent part number/cycle time
    be dropped into a specified cell and overwrite the previous info.
    I only want excel to deal with one cell and the most recent info.

    The 2 things I don't know how to do are:

    1.set-up the cell to only "hold" the most recent input,and overwrite the last.

    2.set-up the cell to either "look" to the next text file during refresh(NEW1, NEW2,ETC.)
    or delete the file it just imported info from so that the next file created by the CNC will be the same
    name as the previous.

    If I can somehow get this to work I can send production info to our electronic bulletin board.


    ANY help you could give me would be greatly appreciated


    Thanks,

    Scott

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    Hey, ace,

    Please read the Forum Rules, and then choose one forum to post your threads.

  4. #4
    Forum Contributor
    Join Date
    07-01-2008
    Location
    Cincinnati, OH
    Posts
    150
    Hi Scott. There is not a concise way to do this in Excel. The following uses two instances of Excel. One instance runs a workbook that watches your folder. (The folder that will recieve the files from the CNC machine). This workbook sends notification to the visible workbook that displays the updates you are wishing for.

    I am assuming that the only files within a single folder will be those created by the CNC machine. If there are other files, we will need to adjust for that.

    See the attached zip file that contains two workbooks created by following the instructions below.

    Create a workbook named "CNCCycleWatcherHelper.xls", Add a standard module and place this code within:
    Option Explicit
    
    Private Declare Function FindFirstChangeNotification Lib "kernel32" Alias "FindFirstChangeNotificationA" (ByVal lpPathName As String, ByVal bWatchSubtree As Long, ByVal dwNotifyFilter As Long) As Long
    Private Declare Function FindNextChangeNotification Lib "kernel32" (ByVal hChangeHandle As Long) As Long
    Private Declare Function FindCloseChangeNotification Lib "kernel32" (ByVal hChangeHandle As Long) As Long
    Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
    
    Private Const FILE_NOTIFY_CHANGE_LAST_WRITE = &H10
    Private Const WAIT_FAILED As Long = &HFFFFFFFF
    Private Const WAIT_TIMEOUT As Long = 258&
    
    Private pCallBackRef As Object
    Private ChangeNotificationHandle As Long
    Private WatchFolder As String
    Private fso As Object
    Private StopWatching As Boolean
    Private PollingIntervalMilliseconds As Long
    
    Public Sub StartWatch(Path As String, CallBackRef As Object, Optional PollingInterval As Long = 500)
        WatchFolder = Path
        ChangeNotificationHandle = FindFirstChangeNotification(WatchFolder, -1, FILE_NOTIFY_CHANGE_LAST_WRITE)
        Set pCallBackRef = CallBackRef
        PollingIntervalMilliseconds = PollingInterval
        Set fso = CreateObject("Scripting.FileSystemObject")
        StopWatching = False
        Application.OnTime Now + TimeSerial(0, 0, 1), "StartWatch2"
    End Sub
    
    Public Sub StopWatch()
        StopWatching = True
    End Sub
    
    Public Sub StartWatch2()
        Dim MsgText As String, Ret As Long, f As Object
        
        On Error GoTo ErrStartWatch2
        
        WaitForSingleObject ChangeNotificationHandle, 1
        
        Do
            FindNextChangeNotification ChangeNotificationHandle
            Ret = WaitForSingleObject(ChangeNotificationHandle, PollingIntervalMilliseconds)
            Call pCallBackRef.Parent.Run("CycleUpdateInfo", "WatcherHelper.xls is running...")
            DoEvents
            If Ret = WAIT_FAILED Then
                'may wish to respond to this error
                Call pCallBackRef.Parent.Run("CycleUpdateError", "WAIT_FAILED")
            End If
            If Ret <> WAIT_TIMEOUT Then
                For Each f In fso.getfolder(WatchFolder).Files
                    If Not f Is Nothing Then
                        With f.openastextstream
                            MsgText = .readline
                            .Close
                        End With
                        f.Delete
                        Call pCallBackRef.Parent.Run("CycleUpdate", MsgText)
                    End If
                    Exit For
                Next
            End If
        Loop Until StopWatching
    
    ErrStartWatch2:
        FindCloseChangeNotification ChangeNotificationHandle
        If Err.Number <> 0 Then
            Call pCallBackRef.Parent.Run("CycleUpdateInfo", "ERROR:  " & Err.Description)
        Else
            Call pCallBackRef.Parent.Run("CycleUpdateInfo", "Controlled Shutdown")
        End If
        ThisWorkbook.Saved = True
        Set pCallBackRef = Nothing
        Application.Quit
    End Sub
    Save and close this file.

    Create another workbook named "CNCCycleWatcher.xls". Add this code into the workbook class:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        If WatchIsRunning Then LocalStopWatch
    End Sub
    Add a standard module and place this code within:
    Option Explicit
    
    Private NewInstance As Application
    Private WatcherHelper As Workbook
    Public WatchIsRunning As Boolean
    
    Sub LocalStartWatch()
        Dim WatcherHelperFullName As String
        Dim WatchThisFolder As String
        Dim PollingInterval As Long
        
        'in the example, these three arguments are typed into ranges
        'you may wish to use contants if others may edit the ranges or
        'you can lock and protect the ranges
        WatcherHelperFullName = ActiveSheet.Range("A14").Text
        WatchThisFolder = ActiveSheet.Range("A12").Text
        PollingInterval = ActiveSheet.Range("A16").Value
        
        'delete all files in the folder that will recieve data from your CNC machine
        On Error Resume Next
        Kill WatchThisFolder & "\*.*"
        On Error GoTo 0
        
        'create a separate, hidden instance of Excel
        'the code in that process will tie up the instance
        'but leave this one free.  Notification is sent by way of
        'CycleUpdate and CycleUpdateInfo
        Set NewInstance = New Application
        NewInstance.IgnoreRemoteRequests = True
        NewInstance.UserControl = False
        
        Set WatcherHelper = NewInstance.Workbooks.Open(WatcherHelperFullName)
        NewInstance.Run "StartWatch", WatchThisFolder, ThisWorkbook, PollingInterval
        WatchIsRunning = True
    End Sub
    
    Sub LocalStopWatch()
        'WatcherHelper takes care of its own cleanup
        NewInstance.Run "StopWatch"
        Set NewInstance = Nothing
        WatchIsRunning = False
    End Sub
    
    Public Sub CycleUpdate(txt As String)
        Range("A1") = txt
    End Sub
    
    Public Sub CycleUpdateInfo(msg As String)
        Range("A2") = msg
    End Sub
    There are three mandatory and one optional arguments passed to a procedure named "StartWatch" located in "CNCCycleWatcherHelper.xls". How you assign value to these args is up to you. In this example and the attached zip file, I created some variables to make it plain and stored the values in cells to make it easy.

    The full path to "CNCCycleWatcherHelper.xls"
    WatcherHelperFullName = ActiveSheet.Range("A14").Text
    The full path of the folder we are watching.
    WatchThisFolder = ActiveSheet.Range("A12").Text
    The interval to check for updates. This is an optional argument with a default of 500 milliseconds.
    PollingInterval = ActiveSheet.Range("A16").Value

    The other mandatory object is a reference to the workbook itself. This reference will not need editing.

    The names of the workbooks are not essential, but the first two arguments above are essential.

    The code in the hidden instance should actually be packed into an activeX.exe. If you have any VB coders on site, they should have no problem placing the code into VB with minimal editing. This would allow you to use events which are more dependable than application.run.

    Download the attached for guidance.

    Let me know if you have any problems...
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-22-2007
    Posts
    6

    Smile Thanks Tom

    Thanks for replying Tom. I'll try it out today and let you know how it turned out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1