How do you open files in a folder in Excel VBA?

Some times VBA programs needs to access files from a specific folder. Assume you have a folder call "Test" within your drive "D".

Following code will open all the files inside that folder. You can easily modify this code for your requirement.

Dim file As Variant
Dim pathAndFileName  as String
file = Dir("D:\Test\")
While (file <> "")
    pathAndFileName = "D:\Test\" & file
    Application.Workbooks.Open (pathAndFileName)
    file = Dir
Wend

Here is the explanation of how this code works.

First we need to define our variables. Variable "file" is used to get individual file names from the folder. Variable "pathAndFileName" is used to hold the file path and file name.

Dim file As Variant
Dim pathAndFileName  as String

Next we assign a our first value to the variable we have defined.

file = Dir("D:\Test\")

So now the name of firsts file inside the folder "Test" is assigned to the variable "file". In this example we use while wend loop to open all the files inside that folder. We use variable "pathAndFileName" to hold the file path and file name. 

 pathAndFileName = "D:\Test\" & file

Next we use  Application.Workbooks.Open method to open the file.

Application.Workbooks.Open (pathAndFileName)

So now we have opened the first file in that directory. We need to repeat the process for all the other files. But before going to next cycle we need to clear the attributes of our variable "file". We use following line of code to clear them

file = Dir

That is the full explanation of the code.

How do you open all the files in a folder in Excel VBA?

How do I open an Excel file in a folder?

How do you get all file names in a folder in Excel VBA?

How do I open a file in VBA?