CODEWITHSUNDEEP
excelexcel-2010vba
Ross McLaughlin
Ross McLaughlin

Reputation: 191

Close all files in a folder

I have the following code that opens all files in a specified folder

Sub OpenFiles()
    Dim MyFolder As String
    Dim MyFile As String
    MyFolder = "\\ILAFILESERVER\Public\Documents\Renewable Energy\FiTs\1 Planning Department\Marks Tracker\Quality Control Reports"
    MyFile = Dir(MyFolder & "\*.xlsx")

    Do While MyFile <> ""
        Workbooks.Open Filename:=MyFolder & "\" & MyFile
        MyFile = Dir
    Loop
End Sub

Is it possible to have a similar code that closes all files in the folder. Many thanks in advance for any assistance provided on this matter.

Upvotes: 0

Views: 4168

Answers (3)

JimmyPena
JimmyPena

Reputation: 8764

Try

Workbooks.Close

From the Excel Visual Basic Help documentation:

This example closes all open workbooks. If there are changes in any open workbook, Microsoft Excel displays the appropriate prompts and dialog boxes for saving changes.

Upvotes: 1

Dan
Dan

Reputation: 45752

Do you need to have them all open at the same time? Because otherwise:

Sub OpenFiles()
    Dim MyFolder As String
    Dim MyFile As String
    MyFolder = "\\ILAFILESERVER\Public\Documents\Renewable Energy\FiTs\1 Planning Department\Marks Tracker\Quality Control Reports"
    MyFile = Dir(MyFolder & "\*.xlsx")
    Dim wb As Workbook

    Do While MyFile <> ""
        Set wb = Workbooks.Open Filename:=MyFolder & "\" & MyFile
        'Do stuff
        wb.Close False 'The false will close without saving
        MyFile = Dir
    Loop
End Sub

Upvotes: 0

Joel Spolsky
Joel Spolsky

Reputation: 33667

Workbooks.Open will return a reference to the pointer of the workbook. Save this in a Collection (using Collection.Add) as soon as you open the workbook. When you want to close all the workbooks, iterate through the collection (using For Each) and close each element. Then remove all elements from the collection.

Upvotes: 3

Related Questions