p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

vba_excel thread: RE: vba_excel digest: September 20, 2002


Message #1 by "Bernie O'Brien" <obrienb@c...> on Sat, 21 Sep 2002 07:32:40 -0700
Rather than putting in the waitTime code, use DoEvents to ensure that
the last workbook has closed. Also put a Resume Next in the error trap.

It seemed to work ok for me but then again, I do not have as many
workbooks as you have.

Try this and let me know if it helps...


Private CurrentFile As String

Sub Tester()
    Sheets("Sheet1").Range("A1").Select
    Cells.ClearContents
    SearchDir = InputBox("Input name of directory to start in")
    FileExt = ".xls"
    SearchSubs = MsgBox(prompt:="Do you want to include files in
subdirectories?", Buttons:=vbYesNo)
   
    With Application.FileSearch
        .NewSearch
        .LookIn = SearchDir
        If SearchSubs = vbYes Then
            .SearchSubFolders = True
        Else
            .SearchSubFolders = False
        End If
        .Filename = FileExt
        .MatchTextExactly = True
        If .Execute > 0 Then
            counter = 0
            For i = 1 To .FoundFiles.Count
                counter = counter + 1
                CurrentFile = .FoundFiles(i)
                Application.Caption = CurrentFile
                ProcessOpenFile
                DoEvents
           Next i
           
           MsgBox prompt:=counter & " files were tested"
           
        ElseIf .Execute = 0 Then
           MsgBox "No files were found, no files were converted"
        End If
    End With
End Sub

Private Sub ProcessOpenFile()
    On Error GoTo Errorhandler

    Workbooks.Open Filename:=CurrentFile
    ActiveWorkbook.Close savechanges:=False
    ActiveCell.Value = CurrentFile
    ActiveCell.Offset(1, 0).Select
    Exit Sub
Errorhandler:
    ActiveCell.Value = "ERROR OPENING " & CurrentFile
    ActiveCell.Offset(1, 0).Select
    Resume Next
End Sub


Bernie O'Brien
CIM Instructor
Saddleback College, California



  Return to Index