Monitor for external file update (2003 / SP2)

Topic

New Reply

Hello once again everyone..

I’m wondering if I can use Excel to monitor for an external file update (timestamp change) and when the monitored file (.CSV) changes, extract various bits of data from it (and update the current .XLS file) and then go back to waiting again..

I would like to use some sort of “non-blocking” wait function so that I check for file updates only so often (so my CPU load doesn’t go to 100%) and can click a button on the form to disable the loop (though a global “LoopEnabled” boolean variable, for instance), and of course to give Excel time to perform it’s screen updates to see the new data.. I see the “Application.Wait()” function is available, but it blocks everything, including my ability to click a “stop” button on the worksheet..

I know this isn’t exactly what Excel is tailored for, but the data I wish to monitor for is Excel data and this sure would be a simple approach..

thanks in advance,
..dane

Reply | Quote

Replies

You can use Application.OnTime to make a macro call another macro or itself to be executed at a specified time. Excel will continue working normally in the meantime. For example:

Sub ProcessFile()
‘ Static variable to be able to compare timestamp across runs
Static datOldTimeStamp As Date
Dim datNewTimeStamp As Date
‘ Code to get new time stamp goes here
…
datNewTimeStamp = …
‘ Compare new time stamp to old one
If datNewTimeStamp > datOldTimeStamp Then
‘ Code to update things goes here
…
‘ Set old time stamp to new one
datOldTimeStamp = datNewTimeStamp
End If
‘ Set macro to be run again in 1 hour
Application.OnTime Now + TimeSerial(1, 0, 0), “ProcessFile”
End Sub

You can set the time to wait to less than one hour, for example to 5 minutes, but don’t set it too short.

Reply | Quote

Awesome, Hans. Thanks again.

I’ve now got a strange range comparison error..

I have two named ranges both of the same size (128 cells), but when I try to do a comparison like the below, it fails..

with thisworkbook.worksheets(1)

if (.Range(“Test_Row_Data”) .Range(“Golden_Row_Data”)) then

msg “error!”

end if

end with

and it errors with Error(13): Type Mismatch…

I have verified both ranges are valid and of size 128 cells…

I thought such range comparisons were valid, but I suppose not?

..dane

Reply | Quote

You cannot compare one multi-cell range to another multi-cell range. You should loop through the cells of one range and compare each cell to the corresponding cell in the other range.

Reply | Quote

Cool.. That’s what I needed to know.

thanks a million, Hans!

..dane

Reply | Quote