Open / Close Workbooks Sequentially

[fred3]

I have a large number of Excel files that I need to open, wait for their opening sub to run, and then close.
Then on to the next one in the list.
etc.

Currently, I'm doing this with a .bat file that simply lists the file names.
It's pretty easy to manage.
But, the disadvantage is that I have to close Excel after each one runs.
(It's not possible to allow all the files to remain open - that will cause the system to hang).
This means that Excel has to open again to run the next.
This is time consuming and the process takes much longer than I'd like.

What I'm thinking is to put the list of filenames into a vba module or into a column of names in a worksheet.
Then sequentially open the file, let the sub run, close the file *while leaving Excel open*.

Also, I'd like the opened files to *not* open a window while running in this mode. I figure this will also save time.

A nudge in the right direction would be very helpful.

Thanks!

 

[shg]

Here's a first cut, completely untested:

Sub Fred()
    Dim cell        As Range
    Dim sFile       As String

    Application.ScreenUpdating = False

    On Error Resume Next
    For Each cell In Range("A1", Cells(Rows.Count, "A").End(xlUp)).Cells
        sFile = cell.Value
        If Len(Trim(sFile)) Then
            If Len(Dir(sFile)) Then
                Workbooks.Open sFile
                If Err.Number Then
                    MsgBox "Can't open " & sFile
                    Err.Clear
                Else
                    ActiveWorkbook.Close SaveChanges:=True
                End If
            Else
                MsgBox "File " & sFile & " does not exist"
            End If
        End If
    Next cell

    Application.ScreenUpdating = True
End Sub

 

[fred3]

Thanks!
I believe when it hits

   Workbooks.Open sFile

Then references to sheets and cells in that target file are looking at the initiatingworksheet that has this code in it.
The reference workbook didn't change so it generates an error.
I'm not sure how to fix that....

 

[shg]

Say what?

 

[fred3]

Well, I don't know what I did but it works now.

Thanks for the help!

 

[shg]

You're welcome.