Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hold Macro running?
Refer to the post in Worksheet
Does anyone have any suggestions on how to hold Macro running until specific file being updated? When I run a Macro, in the middle of the process, I would like to hold Macro running until the periods between the last updated time for specific file and the current time is less than a hour. For example, a Macro is running under the Eric.xls Sub temp() Application.DisplayAlerts = False Workbooks.Open Filename:="C:\A.xls", UpdateLinks:=3 Workbooks("A.xls").Close savechanges:=True Before processing the next step, I would like to check the last updated time for specific files - Mary.xls with the current time. If the difference between the last updated time for Mary.xls and the current time is less than 1 hour, then process the next coding, else wait until the difference periods is less than 1 hour. Workbooks.Open Filename:="C:\B.xls", UpdateLinks:=3 Workbooks("B.xls").Close savechanges:=True End Sub Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hold Macro running?
Eric wrote:
<<When I run a Macro, in the middle of the process, I would like to hold Macro running until the periods between the last updated time for specific file and the current time is less than a hour. This will result in an endless loop that runs constantly, since the current time will always be less than one hour since the last update, if you just ran your code!!! Are you sure you meant instead to wait until 1 hour has passed, then update the file? -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hold Macro running?
I think he said what he meant.
he said: if now() - timestamp(mary.xls) < 1 hour then run my code else wait until mary.xls is less than an hour old run my code end if either way, he wants to run the code, so it can be changed to: Do While now() - timestamp(mary.xls) 1 hour Loop Run my code You might want to put something into your loop to give an option to get out, so that it isn't an endless loop... or you might not, depending upon what YOU want. You can get the timestamp of mary.xls using the FileSystemObject Here's a sample: Sub sbTest() Const cnFile = "c:\test.xls" Dim myFileTime As Date Dim i As Long myFileTime = fnFileDate(cnFile) MsgBox Now() - myFileTime Do While Now() - myFileTime (1 / 24) i = i + 1 If i 10000 Then Exit Do End If Loop MsgBox "I can go!" End Sub Function fnFileDate(filename) As Date 'dimension variables Dim fs As Object Dim f As Object 'assign variables Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.getfile(filename) fnFileDate = f.DateLastModified End Function You'll want to take that Exit Do out and work it up a little... I just put that in three to get myself out of the loop on the test, because my test.xls file is 65 days old and I got into the loop! "Bill Renaud" wrote: Eric wrote: <<When I run a Macro, in the middle of the process, I would like to hold Macro running until the periods between the last updated time for specific file and the current time is less than a hour. This will result in an endless loop that runs constantly, since the current time will always be less than one hour since the last update, if you just ran your code!!! Are you sure you meant instead to wait until 1 hour has passed, then update the file? -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hold Macro running?
OK, so Eric's program is waiting for another process to update the file,
before it updates everything. I guess I missed that concept! You might want to put some sort of 5 minute wait inside the loop, so that your routine is not checking the file constantly! This might tie up the file server so much that the other process would have a difficult time making its update, which your routine is waiting on. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to hold running Macro? | Excel Worksheet Functions | |||
Report with macro losing links to a particular worksheet after running macro | Excel Programming | |||
disable user running macro from Tools Macro | Excel Discussion (Misc queries) | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
Launch Macro in Access via Macro running in Excel??? | Excel Programming |