Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: program that opens excel files one by one with a 30 minute interval between each one

    I'm not sure this forum is the right one but I thought i'd try. Could somebody please help me with this?

    Im interesting in a program (maybe VB?) that will open several excel file containing macros one by one.
    The files names and the directory in which the files are stored is always the same. The order in which they open doesnt matter.
    However, Id like each excel file to open 30 minutes after the previous one, to allow the macro to run.
    Thanks!

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    This routine will open all xls file in the specified directory for 5 seconds
    and then waites until 1800 seconds have elapsed untill opening the next if any. (this example uses the diectory in wich the file runing the code is located, therefore I have stopped trying to open itself- if a diferent directiory is specified this will not be necessary !).

    Sub OpenFileTest()

    Dim StartTime As Long
    Dim ElapseTime As Long
    Dim FileName As String
    Dim SpecifiedPath As String

    SpecifiedPath = ThisWorkbook.Path

    FileName = Dir(SpecifiedPath & "\*.xls")

    If FileName = "" Then Exit Sub

    StartTime = Timer


    Application.ScreenUpdating = False

    Do Until FileName = ""

    If FileName <> ThisWorkbook.Name Then
    Workbooks.Open SpecifiedPath & "\" & FileName

    Do
    ElapseTime = Timer - StartTime

    Loop Until ElapseTime >= 5


    ActiveWorkbook.Close SaveChanges:=False

    Do
    ElapseTime = Timer - StartTime

    Loop Until ElapseTime >= 1800


    StartTime = Timer

    End If
    FileName = Dir
    Loop
    Application.ScreenUpdating = False

    End Sub


    This will tie up Excel for the duration ! (although it seem another instance can be run at the same time)

    Hope this helps


    MTB

  3. #3
    Join Date
    Apr 2004
    Posts
    4

    Thanks

    I'll try it. Many thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •