Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Open files in a specific directory (folder)

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.

Contact Form

Name

Email *

Message *

Popular Posts