How to Make Excel Macro Run Continuously on a Schedule
Constantly Running Macro
- joseph_r03
-
- #1
Hi all
I am using
Sub FillColor() With Application.FileSearch .LookIn = "K:\Departmental Folders\Accounting\Monthly Accounting Package\2007\04_2007\Green Book\Highlights" .Filename = "110 Summary Income Statement-Month, YTD" If .Execute > 0 Then Workbooks("Greenbook_Schedule_Preparers").Activate Dim rFoundCell As Range Set rFoundCell = Columns(1). Find(What:="110", After:=Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) rFoundCell.Interior.ColorIndex = 35 End If End With End SubDisplay More
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
-
- #2
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.
-
- #3
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. -
- #4
Re: Constantly Running Macro
There is an application level event beforeSave. Your routine might hook into that.
-
- #5
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 belowSub MyMacro() Application.OnTime TimeValue("15:00:00"), "MyMacro" 'YOUR CODE End Subhttp://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.
-
- #6
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.
-
- #7
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)
Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnTime dTime, "FillColor", , False End Sub Sub Workbook_Open() Application.OnTime Now + TimeValue("00:01:00"), "FillColor" End Sub Sub FillColor() dTime = Now + TimeValue("00:01:00") Application.OnTime dTime, "FillColor" With Application.FileSearch .LookIn = "K:\Departmental Folders\Accounting\Monthly Accounting Package\2007\04_2007\Green Book\Highlights" .Filename = "110 Summary Income Statement-Month, YTD" If .Execute > 0 Then Workbooks("Greenbook_Schedule_Preparers").Activate Dim rFoundCell As Range Set rFoundCell = Columns(1).Find(What:="110", After:=Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) rFoundCell.Interior.ColorIndex = 35 End If End With End SubDisplay More
-
- #8
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 SubDisplay More
-
- #9
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.
-
- #10
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 -
- #11
Re: Constantly Running Macro
AndrewJ,
Appreciate you helping out, please also remember code tags.
Thanks.
-
- #12
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 SubDisplay More
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.
-
- #13
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.
-
- #14
Re: Constantly Running Macro
Quote from Karebac
By the way (I KNOW this is a dumb question), but do you LITERALLY have 123 files
Yes. -
- #15
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.
-
- #16
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!
-
- #17
Re: Constantly Running Macro
edit: ignore
-
- #18
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.
-
- #19
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 FunctionDisplay More
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.
-
- #20
Re: Constantly Running Macro
I found a way to count the files without using a UDF.
With Application.FileSearch .LookIn = "C:\My Documents" .SearchSubFolders = True .FileName = "cmd*" If .Execute() > 0 Then MsgBox "There were " & .FoundFiles.Count & _ " file(s) found." Else MsgBox "There were no files found." End If End WithDisplay More
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.
Sub TrackFileCountChange() With Application.FileSearch .LookIn = "K:\Departmental Folders\Accounting\Monthly Accounting Package\2007\05_2007\Green Book" .SearchSubFolders = True If .FoundFiles.Application.DateLastModified > TimeSerial(Now, -5, 0) Then Call FillColor End If End With End SubDisplay More
Source: https://forum.ozgrid.com/forum/index.php?thread/60531-constantly-running-macro/
0 Response to "How to Make Excel Macro Run Continuously on a Schedule"
Post a Comment