Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Monitor for external file update (2003 / SP2)

    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.. [img]/forums/images/smilies/sad.gif[/img]

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Monitor for external file update (2003 / SP2)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monitor for external file update (2003 / SP2)

    Awesome, Hans. Thanks again. [img]/forums/images/smilies/smile.gif[/img]

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Monitor for external file update (2003 / SP2)

    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.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monitor for external file update (2003 / SP2)

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

    thanks a million, Hans!

    ..dane

Posting Permissions

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