Constantly Running Macro

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

There is an application level event beforeSave. Your routine might hook into that.

Re: Constantly Running Macro

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

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

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.

Option Compare Text 
Option Explicit 
 
Function CountFiles(Directory As String) As Double 
     'Function purpose:  To count all files in a directory
    Dim fso As Object, _ 
    objFiles As Object 
     
     'Create objects to get a count of files in the directory
    Set fso = CreateObject("Scripting.FileSystemObject") 
    On Error Resume Next 
    Set objFiles = fso.GetFolder(Directory).Files 
    If Err.Number <> 0 Then 
        CountFiles = 0 
    Else 
        CountFiles = objFiles.Count 
    End If 
    On Error Goto 0 
End Function 
 
Sub TestCount() 
     'To demonstrate the use of the CountFiles function
    Dim fso As Object 
    Set fso = CreateObject("Scripting.FileSystemObject") 
    If fso.folderexists(Selection) Then 
        MsgBox "I found " & CountFiles(Selection) & " files in " & Selection, _ 
        vbOKOnly + vbInformation, CountFiles(Selection) & " files found!" 
    Else 
        MsgBox "Sorry, but I can't find the folder:  " & vbCrLf & Selection _ 
        & vbCrLf & "Please select a cell that has a valid" & vbCrLf & _ 
        "folder name in it!", vbOKOnly + vbCritical, "Error!" 
    End If 
     
End Sub

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

AndrewJ,

Appreciate you helping out, please also remember code tags.

Thanks.

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?

Private Sub Worksheet_Sync(ByVal SyncEventType As Office.MsoSyncEventType)


    If SyncEventType = msoSyncEventDownloadFailed Or _
            SyncEventType = msoSyncEventUploadFailed Then
            
        MsgBox "Document synchronization failed.  " & _
            "Please contact your administrator " & vbCrLf & _
            "or try again later."
    
    End If
    
End Sub

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

Sub TestCount()


    Dim countit As Integer
     'To demonstrate the use of the CountFiles function
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.folderexists("C:\Program Files") Then


        countit = CountFiles("C:\Program Files")


    Else


        countit = 0


    End If
    
    MsgBox countit
     
End Sub




Function CountFiles(Directory As String) As Double
     'Function purpose:  To count all files in a directory
    Dim fso As Object, _
    objFiles As Object
     
     'Create objects to get a count of files in the directory
    Set fso = CreateObject("Scripting.FileSystemObject")
    On Error Resume Next
    Set objFiles = fso.GetFolder(Directory).Files
    If Err.Number <> 0 Then
        CountFiles = 0
    Else
        CountFiles = objFiles.Count
    End If
    On Error GoTo 0
End Function

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.