Problem with loop and/or conflicting macros

Technical support and scripting issues

Moderators: JRL, Dorian (MJT support)

Post Reply
kevin
Newbie
Posts: 8
Joined: Thu Dec 10, 2009 2:18 am

Problem with loop and/or conflicting macros

Post by kevin » Thu Dec 10, 2009 2:50 am

I have a script that loops for a certain amount of time and looks to see if a file has been updated. Once updated, it should open a self-executing excel file. If it times out, then it should open a self-executing excel error file.

What's been happening is that usually the checked file is updated before the script runs, so when the script runs everything is fine. My problem is when it's not generated before the script runs...I've run into a couple problems.

1) My script ended before it was supposed to
2) When it timed out, the error file was never opened
3) *possible error* ...the file doesn't run after the data file is generated during the looping process

Code: Select all

VBSTART
  Function GetStartTime()
    GetStartTime = Now - (1/24)
  End Function

  Function IsFileUpdated(t)

    On Error Resume Next

    IsFileUpdated=False
    t = FormatDateTime(t,vbGeneralDate)

    Dim fso, f

   Set fso = CreateObject("Scripting.FileSystemObject")
   Set f = fso.GetFile("\\somedir\datafile.csv")

   If (DateDiff("s",FormatDateTime(f.DateLastModified,vbGeneralDate),t ) <0> 2048) Then
      IsFileUpdated=True
   Else
      IsFileUpdated=False
   End If

   Set fso = Nothing
  End Function
VBEND

// Disable screensaver?
MouseMove>300,350
MouseMove>350,300
Wait>1.50

// Get start time
VBEval>GetStartTime,timeStarted

// Check for updated file
Let>i=0
Repeat>ans
  Let>i=i+1

  If>i>20
    Run Program>C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE "p:\kevin\error.xls"
    Goto>SkipToEnd
  EndIf

  If>i>1
    Wait>60
  EndIf

  VBEval>IsFileUpdated("%timeStarted%"),ans
Until>ans=True

Let>RP_WAIT=0
Run Program>C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE "p:\kevin\filetorun.xls"

Label>SkipToEnd
edit: the datediff line should read as follows:
"If (DateDiff("s",FormatDateTime(f.DateLastModified,vbGeneralDate),t) islessthan 0) and (f.Size > 2048)"

The weird thing is that when testing this macro, using the debugger...everything runs as it should! Late-generating file is detected, and also the error file runs when it isn't detected. Makes no sense.

Here is my log file:
2009/12/10 07:20:00:359 - Started Macro : P&L UpdateSend (AM)
2009/12/10 07:20:00:359 - // Macro: P&LUpdateSend (7:00AM GMT+8:00)
2009/12/10 07:20:00:359 -
2009/12/10 07:20:00:375 - // Macro Code
2009/12/10 07:20:00:375 - VBSTART
2009/12/10 07:20:00:375 -
2009/12/10 07:20:00:390 - // Disable screensaver?
2009/12/10 07:20:00:390 - MouseMove>300,350
2009/12/10 07:20:00:390 - MouseMove>350,300
2009/12/10 07:20:00:390 - Wait>1.50
2009/12/10 07:20:01:921 -
2009/12/10 07:20:01:921 - // Get start time
2009/12/10 07:20:01:921 - VBEval>GetStartTime,timeStarted
2009/12/10 07:20:01:937 -
2009/12/10 07:20:01:937 - // Check for updated file
2009/12/10 07:20:01:937 - Let>i=0
2009/12/10 07:20:01:937 - Repeat>ans
2009/12/10 07:20:01:953 - Let>i=i+1
2009/12/10 07:20:01:953 -
2009/12/10 07:20:01:953 - If>i>20
2009/12/10 07:20:01:968 -
2009/12/10 07:20:01:968 - If>i>1
2009/12/10 07:20:01:968 -
2009/12/10 07:20:01:968 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:20:01:984 - Until>ans=True
2009/12/10 07:20:02:000 - Repeat>ans
2009/12/10 07:20:02:000 - Let>i=i+1
2009/12/10 07:20:02:000 -
2009/12/10 07:20:02:015 - If>i>20
2009/12/10 07:20:02:015 -
2009/12/10 07:20:02:015 - If>i>1
2009/12/10 07:20:02:015 - Wait>60
2009/12/10 07:21:02:031 - EndIf
2009/12/10 07:21:02:031 -
2009/12/10 07:21:02:046 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:21:02:046 - Until>ans=True
2009/12/10 07:21:02:046 - Repeat>ans
2009/12/10 07:21:02:062 - Let>i=i+1
2009/12/10 07:21:02:062 -
2009/12/10 07:21:02:062 - If>i>20
2009/12/10 07:21:02:062 -
2009/12/10 07:21:02:078 - If>i>1
2009/12/10 07:21:02:078 - Wait>60
2009/12/10 07:22:02:093 - EndIf
2009/12/10 07:22:02:093 -
2009/12/10 07:22:02:093 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:22:02:109 - Until>ans=True
2009/12/10 07:22:02:109 - Repeat>ans
2009/12/10 07:22:02:109 - Let>i=i+1
2009/12/10 07:22:02:125 -
2009/12/10 07:22:02:125 - If>i>20
2009/12/10 07:22:02:125 -
2009/12/10 07:22:02:140 - If>i>1
2009/12/10 07:22:02:140 - Wait>60
2009/12/10 07:23:02:156 - EndIf
2009/12/10 07:23:02:156 -
2009/12/10 07:23:02:156 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:23:02:171 - Until>ans=True
2009/12/10 07:23:02:171 - Repeat>ans
2009/12/10 07:23:02:187 - Let>i=i+1
2009/12/10 07:23:02:187 -
2009/12/10 07:23:02:187 - If>i>20
2009/12/10 07:23:02:187 -
2009/12/10 07:23:02:203 - If>i>1
2009/12/10 07:23:02:203 - Wait>60
2009/12/10 07:24:02:218 - EndIf
2009/12/10 07:24:02:218 -
2009/12/10 07:24:02:218 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:24:02:234 - Until>ans=True
2009/12/10 07:24:02:234 - Repeat>ans
2009/12/10 07:24:02:250 - Let>i=i+1
2009/12/10 07:24:02:250 -
2009/12/10 07:24:02:250 - If>i>20
2009/12/10 07:24:02:250 -
2009/12/10 07:24:02:265 - If>i>1
2009/12/10 07:24:02:265 - Wait>60
2009/12/10 07:25:02:281 - EndIf
2009/12/10 07:25:02:281 -
2009/12/10 07:25:02:281 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:25:02:296 - Until>ans=True
2009/12/10 07:25:02:296 - Repeat>ans
2009/12/10 07:25:02:296 - Let>i=i+1
2009/12/10 07:25:02:312 -
2009/12/10 07:25:02:312 - If>i>20
2009/12/10 07:25:02:312 -
2009/12/10 07:25:02:328 - If>i>1
2009/12/10 07:25:02:328 - Wait>60
2009/12/10 07:26:02:343 - EndIf
2009/12/10 07:26:02:343 -
2009/12/10 07:26:02:343 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:26:02:359 - Until>ans=True
2009/12/10 07:26:02:359 - Repeat>ans
2009/12/10 07:26:02:375 - Let>i=i+1
2009/12/10 07:26:02:375 -
2009/12/10 07:26:02:375 - If>i>20
2009/12/10 07:26:02:375 -
2009/12/10 07:26:02:390 - If>i>1
2009/12/10 07:26:02:390 - Wait>60
2009/12/10 07:27:02:406 - EndIf
2009/12/10 07:27:02:406 -
2009/12/10 07:27:02:406 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:27:02:421 - Until>ans=True
2009/12/10 07:27:02:421 - Repeat>ans
2009/12/10 07:27:02:437 - Let>i=i+1
2009/12/10 07:27:02:437 -
2009/12/10 07:27:02:437 - If>i>20
2009/12/10 07:27:02:437 -
2009/12/10 07:27:02:453 - If>i>1
2009/12/10 07:27:02:453 - Wait>60
2009/12/10 07:28:02:468 - EndIf
2009/12/10 07:28:02:468 -
2009/12/10 07:28:02:468 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:28:02:484 - Until>ans=True
2009/12/10 07:28:02:484 - Repeat>ans
2009/12/10 07:28:02:500 - Let>i=i+1
2009/12/10 07:28:02:500 -
2009/12/10 07:28:02:500 - If>i>20
2009/12/10 07:28:02:500 -
2009/12/10 07:28:02:515 - If>i>1
2009/12/10 07:28:02:515 - Wait>60
2009/12/10 07:29:02:531 - EndIf
2009/12/10 07:29:02:531 -
2009/12/10 07:29:02:531 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:29:02:546 - Until>ans=True
2009/12/10 07:29:02:546 - Repeat>ans
2009/12/10 07:29:02:546 - Let>i=i+1
2009/12/10 07:29:02:562 -
2009/12/10 07:29:02:562 - If>i>20
2009/12/10 07:29:02:562 -
2009/12/10 07:29:02:578 - If>i>1
2009/12/10 07:29:02:578 - Wait>60
2009/12/10 07:30:02:593 - EndIf
2009/12/10 07:30:02:593 -
2009/12/10 07:30:02:593 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:30:02:609 - Until>ans=True
2009/12/10 07:30:02:609 - Repeat>ans
2009/12/10 07:30:02:625 - Let>i=i+1
2009/12/10 07:30:02:625 -
2009/12/10 07:30:02:625 - If>i>20
2009/12/10 07:30:02:625 -
2009/12/10 07:30:02:640 - If>i>1
2009/12/10 07:30:02:640 - Wait>60
2009/12/10 07:31:02:656 - EndIf
2009/12/10 07:31:02:656 -
2009/12/10 07:31:02:656 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:31:02:671 - Until>ans=True
2009/12/10 07:31:02:671 - Repeat>ans
2009/12/10 07:31:02:687 - Let>i=i+1
2009/12/10 07:31:02:687 -
2009/12/10 07:31:02:687 - If>i>20
2009/12/10 07:31:02:687 -
2009/12/10 07:31:02:703 - If>i>1
2009/12/10 07:31:02:703 - Wait>60
2009/12/10 07:32:02:718 - EndIf
2009/12/10 07:32:02:718 -
2009/12/10 07:32:02:718 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:32:02:734 - Until>ans=True
2009/12/10 07:32:02:734 - Repeat>ans
2009/12/10 07:32:02:750 - Let>i=i+1
2009/12/10 07:32:02:750 -
2009/12/10 07:32:02:750 - If>i>20
2009/12/10 07:32:02:750 -
2009/12/10 07:32:02:765 - If>i>1
2009/12/10 07:32:02:765 - Wait>60
2009/12/10 07:33:02:781 - EndIf
2009/12/10 07:33:02:781 -
2009/12/10 07:33:02:781 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:33:02:796 - Until>ans=True
2009/12/10 07:33:02:796 - Repeat>ans
2009/12/10 07:33:02:796 - Let>i=i+1
2009/12/10 07:33:02:812 -
2009/12/10 07:33:02:812 - If>i>20
2009/12/10 07:33:02:812 -
2009/12/10 07:33:02:828 - If>i>1
2009/12/10 07:33:02:828 - Wait>60
2009/12/10 07:34:02:843 - EndIf
2009/12/10 07:34:02:843 -
2009/12/10 07:34:02:843 - VBEval>IsFileUpdated("2009/12/10 ¤W¤È 06:20:01"),ans
2009/12/10 07:34:02:859 - Until>ans=True
2009/12/10 07:34:02:859 - Repeat>ans
2009/12/10 07:34:02:859 - Let>i=i+1
2009/12/10 07:34:02:875 -
2009/12/10 07:34:02:875 - If>i>20
2009/12/10 07:34:02:875 -
2009/12/10 07:34:02:890 - If>i>1
2009/12/10 07:34:02:890 - Wait>60
This script starts at 7:20a. If you notice, the script ends before 7:35a. I have another script that runs at 7:35a...is this a possible problem? But this is inconsistent w/past usage as I've had this script loop for about an hour before, then just timing out w/o running the error file. The data file ("datafile.csv") was generated at 7:38a so I'm not sure if that would've been found or not.

I hope I'm just running into an easily-fixed issue here, and would appreciate it if anyone had any suggestions at all.

Thanks!
Top
User avatar
Marcus Tettmar
Site Admin
Posts: 7395
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:
Contact Marcus Tettmar

Post by Marcus Tettmar » Thu Dec 10, 2009 9:51 am

You say you've run this through the debugger. Did you run the debugger when the file was not there? If you had you should have seen what the problem is: ans is True when the file does not exist.

Your VBScript function will return True if the file does not exist. So if you run this when the file is not there your loop will immediately exit and the error file will never get opened.

The problem is that the file system object will return an error if the file does not exists and since you have On Error Resume Next it will go to the next line and set the result of the function to True. Hence your loop will end.

Change your VBScript function to:

Code: Select all

VBSTART
  Function GetStartTime()
    GetStartTime = Now - (1/24)
  End Function

  Function IsFileUpdated(t)

    'On Error Resume Next

    IsFileUpdated=False
    t = FormatDateTime(t,vbGeneralDate)

    Dim fso, f

   Set fso = CreateObject("Scripting.FileSystemObject")
   
   filename = "\\somedir\datafile.csv"
   if fso.FileExists(filename) then  
     Set f = fso.GetFile("\\somedir\datafile.csv")

     If (DateDiff("s",FormatDateTime(f.DateLastModified,vbGeneralDate),t) < 0) and (f.Size > 2048) Then
        IsFileUpdated=True
     Else
        IsFileUpdated=False
     End If
   Else
    IsFileUpdated=False
   End If

   Set fso = Nothing
  End Function
VBEND
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?
Top
kevin
Newbie
Posts: 8
Joined: Thu Dec 10, 2009 2:18 am

Post by kevin » Thu Dec 10, 2009 11:05 am

Ah, I guess I did not test for that case...only when the file was there and updated, and when the file was there but not updated. Guess it does disappear for a short time while it's being re-generated.

Thanks a lot for the insight...I will make those changes and see if that takes care of the problem!
Top
kevin
Newbie
Posts: 8
Joined: Thu Dec 10, 2009 2:18 am

Post by kevin » Mon Dec 21, 2009 9:24 am

The macro has been running great for the past week, and the error message finally sent out the one time it should have. Thanks again!
Top
Post Reply

Return to “Technical / Scripting”

Sign up to our newsletter for free automation tips, tricks & discounts