Open / Close Workbooks Sequentially

fred3

New Member
Joined
May 1, 2011
Messages
39
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!
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Here's a first cut, completely untested:

Code:
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
 
Last edited:
Upvote 0
Thanks!
I believe when it hits
Code:
   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....
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top