How can I open all files in folder with VBA?

---

You can use a Do While loop along with the Workbooks.Open method in VBA to open all files in a particular folder.

Here is one common way to do so in practice:

Sub OpenAllFilesInFolder()

Dim ThisFolder As String
Dim ThisFile As String

'specify folder location and types of files to open in folder
ThisFolder = "C:UsersbobDocumentscurrent_data"
ThisFile = Dir(ThisFolder & "*.xlsx")

'open each xlsx file in folder
Do While ThisFile <> ""
    Workbooks.Open Filename:=ThisFolder & "" & ThisFile
    ThisFile = Dir
Loop

End Sub

This particular macro opens all files with a .xlsx extension in the following folder:

• C:UsersbobDocumentscurrent_data

The following example shows how to use this macro in practice.

Related:

Example: How to Open All Files in Folder Using VBA

Suppose we have the following folder called current_data that contains three .xlsx files:

Suppose we would like to use VBA to open all of the .xlsx files in this folder.

We can create the following macro to do so:

Sub OpenAllFilesInFolder()

Dim ThisFolder As String
Dim ThisFile As String

'specify folder location and types of files to open in folder
ThisFolder = "C:UsersbobDocumentscurrent_data"
ThisFile = Dir(ThisFolder & "*.xlsx")

'open each xlsx file in folder
Do While ThisFile <> ""
    Workbooks.Open Filename:=ThisFolder & "" & ThisFile
    ThisFile = Dir
Loop

End Sub

When we run this macro, each file with a .xlsx extension in the folder will be opened one by one.

Note that if any of the files in the folder are already opened, those files will simply remain opened.

Note: You can find the complete documentation for the Workbooks.Open method in VBA .