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.

Finding a specific file from a folder

Some times we need to check whether specific file is contained in a folder. Below example checks whether file "book1.xlsx" is contained in a folder call "Test" which is located in "D" drive. Then open the file if it is exists.


Dim file As Variant
Dim FName, pathAndFileName As String
FName = "book1.xlsx"

file = Dir("D:\Test\")
While (file <> "")
    If StrComp(file, FName) = 0 Then
        pathAndFileName = "D:\Test\" & file
        Application.Workbooks.Open (pathAndFileName)
        MsgBox "found " & file
    End If
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. Variable FName holds the file name we want to check.

Dim file As Variant
Dim FName, pathAndFileName  As String
FName = "book1.xlsx"

Next we assign the first file of the folder 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 compare all the files inside that folder with file name we want. For the comparison we use below line of code

 If StrComp(file, FName) = 0 Then

If the file names matched then the codes inside the if statement will be executed.
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)

Then program will give the message that it has found the file we want.



 MsgBox "found " & file

There is important thing we need to do when loop through the program. It is that we need to clear the attributes of our variable "file"  before going to next cycle. We use following line of code to clear them

file = Dir

Contact Form

Name

Email *

Message *

Popular Posts