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...
LinkBack URL
About LinkBacks
Register To Reply
Thanks Tom
Bookmarks