Help with loops

[cham.miller]

I am working on writing a VBScript to run an excel macro only if a particular file exists in a specified directory. If the file does not exist, I would like the script to wait 10 minutes and check for the file again. Ideally, this script would continue running until a file is dropped into the directory, at which point the file would be detected and an excel macro would start. At the completion of that macro, the script would resume checking the same directory (assuming the file that triggered the macro was moved out of that directory once the macro had finished).

I am pretty familiar with excel macros, but have never worked with loops. I don't know if a DoWhile or DoUntil loop would be best.

If it helps, I have a large set of macros which take multiple files in specific locations and build a set of PDF reports from those files. I need to be able to drop my original files into specific directories and have the macro automatically build my report and move the files to another directory, then resume checking for new files.

The only VBScript code I have so far (copied and pasted form various sources) is:

Option Explicit


DIM fso

Set fso = CreateObject("Scripting.FileSystemObject")

If (fso.FileExists("C:\Documents and Settings\Cham Miller\Desktop\Test\Test1.txt")) Then
Application.run ("personal.xlsb!MFG37cBladeReport")
Else WScript.Sleep 10000

End If

DoUntil '??

Loop


Thanks in advance,

Charles

 

[cham.miller]

I forgot to add this, I am working on Windows XP and using Excel 2007. Thanks.

 

[shg]

Why VBScript rather than running this in VBA?

 

[cham.miller]

I am trying to use VBScript because I want the directory on a server to be checked continuously for new files. Is there an easier way to do this in VBA?

 

[shg]

I have near-zero experience with VBScript. Maybe like this:

    Dim fso
 
    Set fso = CreateObject("Scripting.FileSystemObject")
    Do Until fso.FileExists("C:\Documents and Settings\Cham Miller\Desktop\Test\Test1.txt")
        WScript.Sleep 10000
    Loop
    Application.Run ("personal.xlsb!MFG37cBladeReport")

 

[cham.miller]

That went a little better. The script tried to run my macro after I dropped the test file into the specified directory, but I received the error:

>
Script: C:\Documents and Settings\Cham Miller\Desktop\Test1.vbs
Line: 7
Char: 5
Error: Object Required 'Application'
Code: 800A01A8
Source: Microsoft VBScript runtime error
>

I think I must need a line to activate the VBA database before running the macro. I'll keep playing with it.

Thanks for your input!

 

[shg]

Well, that doesn't surprise me.

There's got to be something simple you can do in VBS to open a file -- an Excel file with a workbook open event that runs the macro of interest -- but I don't know how, sorry.

 

[shg]

I was optimistic that this would work, but it didn't:

Dim appxl
Set appxl = CreateObject("Excel.Application")
appxl.Open "C:\Documents and Settings\shg\Desktop\Scratch.xls"

 

[cham.miller]

Yeah, that's basically what I was shooting for... No luck here yet. I'm sure I need to create an Excel object in my script before running the macro. I seems like there should be a simple command for that, but not much luck in my searches so far.

 

[shg]

My problem with VBS is that I have no development or debug environment.

Back in VBA land, how about this:

Sub DoThatFile()
    If Len(Dir("C:\Documents and Settings\Cham Miller\Desktop\Test\Test1.txt")) Then
        Application.Run "personal.xlsb!MFG37cBladeReport"
    Else
        Application.OnTime Now() + #12:00:03 AM#, "DoThatFile"
    End If
End Sub