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

    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.

  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    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

    ElapseTime = Timer - StartTime

    Loop Until ElapseTime >= 5

    ActiveWorkbook.Close SaveChanges:=False

    ElapseTime = Timer - StartTime

    Loop Until ElapseTime >= 1800

    StartTime = Timer

    End If
    FileName = Dir
    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


  3. #3
    Join Date
    Apr 2004


    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