Constantly Running Macro

  • Hi all


    I am using

    to highlight cells if a file is found in the system.


    These files are saved by other users and I would like this macro to be "running in the background" while I have the file open so if someone saves a file it will automatically update. I don't want to start the macro every time I want to know if a file is saved. Can this be done?


    Thanks

  • Re: Constantly Running Macro


    I dont think its possible to have a macro running while your working in excel unless you try something like using a userform. But for normal spreadsheet work I dont think itll happen.


    An alternative would be to use the ontime event to schedule a check every 30 seconds, minutes, 5 mins, or w/e. Your code looks very short and as long as it doesnt take a long time (if K: is a network drive and you have a good connection to it) to execute you could even do 10 secs and hardly notice it.

  • Re: Constantly Running Macro


    Thanks for the reply.
    The code I gave is for only one file. There are 123 files I am checking for so I was planning on copying and pasting that 122 times and just changing the file name. So the code will actually be much larger. Regardless, I would like to try the ontime suggestion you had. Would you mind showing me how it would fit in my code to check every 5 minutes. I may adjust the time depending on how quick (or slow) it runs. Yes, K is a network drive.

  • Re: Constantly Running Macro


    Code
    Private Sub Workbook_Open()
         Application.OnTime TimeValue("15:00:00"), "MyMacro"
    End Sub


    Where MyMacro is the name of the macro you wish to run and resides in a Standard Module and has the OnTime Method again like below


    Code
    Sub MyMacro()
        Application.OnTime 
      TimeValue("15:00:00"), "MyMacro"
    'YOUR CODE
    End Sub


    http://www.ozgrid.com/Excel/run-macro-on-time.htm


    What you could do is test the folder for changes (count total files, look for total folder size changing) every few minutes, and then if it finds a change you could have it scan for all the files.

  • Re: Constantly Running Macro


    You may also be able to test a folder for its last modified date property.... infact I'm almost sure you can. That would probably be best unless you want to put code in all 123 worksheets to trigger the beforesave event.

  • Re: Constantly Running Macro


    Quote from AndrewJ


    I was looking there as well between posts and have an ontime function working now.


    I won't have code in all 123 worksheets. Just code to look for 123 different file names in the given directory. The code is only in my summary worksheet which has a list of all 123 files. I want to denote I have received them.


    I do like the idea about checking for the folder changing first then trigger the rest of the code. I will work on making that happen.


    I don't really understand what Mikerickson typed, but I'm very new to this.


    Thanks all.


    Code with ontime looks like this (for future searches)

  • Re: Constantly Running Macro


    Found this on the web, hope it works for ya. Itll count total items in a folder. I looked for folder modified property for a few mins but didnt find a way. I still think its possible tho.


  • Re: Constantly Running Macro


    Btw, what Mike was talking about was putting the same code in every workbook (on the user end) to send a signal to you when the specific book gets saved.

  • Re: Constantly Running Macro


    Quote from AndrewJ

    Found this on the web, hope it works for ya. Itll count total items in a folder. I looked for folder modified property for a few mins but didnt find a way. I still think its possible tho.


    Excellent. I'll test it Monday. It's quitting time ::D

  • Re: Constantly Running Macro


    I am a total new-be to all this, but I have always been intrigued by such background events as you discribe here. Perhaps there is some potential in the Sync event in the Workbook module?



    Years ago, I was a programmer on a Novell network of 50 DOS computers, using Revelation (PICK Basic for DOS). I made one workstation into a constantly running program which would search a certain file for new records, and whenever it found one, the record would be parameters for it to process some data submitted by one of the other workstations. So it was like a primitive job queue.


    I wrote up my idea for a similar application, at utteraccess.com under the Karbac screen name, entitled "The Hamburger Marquee". You know how in those fast food joints, when you place an order at the drive-in, someone hits a button, and a marquee in the back tells the cook to flip another burger. Well, in a network environment, you could have a marquee on the boss's desktop, polling a file for a new record, such as "VIP CALL ON LINE 3", and when it gets a hit, the screen would flash, plus, all the calls would be logged and datestamped some place.


    So perhaps, if you are on a network, you could have a dedicated task on one workstation, which continuously opens all those spreadsheets, and updates them with the desired codes.


    Or better yet, when a user updates a spreadsheet, have the Excel VBA cut a record to some file, which is constantly polled by another workstation, and when that jobqueue application sees such a record, it will modify the spreadsheet accordingly.

  • Re: Constantly Running Macro


    By the way (I KNOW this is a dumb question), but do you LITERALLY have 123 files, or is this some pun on LOTUS 123? By the way QUATTRO was chosen by Borland as the name for its spreadsheet because it means FOUR, and would naturally be thought of as a successor to 1,2,3. Now I am remembering the ads I used to read for BOING Calc, and that inexpensive clone by Mosaic Softward, that I used for a spreadsheet, when I was too poor to purchase Lotus.

  • Re: Constantly Running Macro


    Quote from AndrewJ

    Btw, what Mike was talking about was putting the same code in every workbook (on the user end) to send a signal to you when the specific book gets saved.

    No, what I was talking about was a routine in the Master book that would run any time any workbook gets saved. Now that I think about it, that routine runs BEFORE the file was saved and so would either have to poll the workbook about to be saved or set an on_time routine to run in a minute or so to read from the (by then) saved file.

  • Re: Constantly Running Macro


    Well, if the code is in the master file, theres no way to trigger it without some event from the user workbooks, or by periodically testing the folder the user books are saved in.



    Sry reafidy, I see you must have corrected it but I dont remember forgetting tags anywhere!

  • Re: Constantly Running Macro


    Quote from AndrewJ

    Found this on the web, hope it works for ya. Itll count total items in a folder.


    I can get the UDF to count the files, but I am more interested in being able to know if the count has changed. I would think the code would read "If file count changes in given directory, then perform sub fillcolor" Any thoughts on how I can make that happen?


    And I noticed it will only count files in the directory, any chance it could be changed to count subfolder's files within the directory.

  • Re: Constantly Running Macro



    Just make sure you compare countit to a new count everytime. If the two counts dont equal each other, the file numbers changed. To do sub-directories - just change the path ;)


    Btw - testing the above as it is will probably result in 0 unless you have files in the base program files dir.

  • Re: Constantly Running Macro


    I found a way to count the files without using a UDF.


    With .SearchSubFolders I can get a count for the subfolders without changing the directory each time. I think the DateLastModified property and TimeSerial function will help with knowing if a file was saved there or not by triggering the then statement if a file is found with a date modified within the past 5 minutes. I then figured I'd attach the ontime function to this sub to check every 5 minutes. My syntax is wrong in a few places though, but I don't know where.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!