Check if file Exists in location using Excel VBA

Home/Excel VBA/Check if file Exists in location using Excel VBA

VBA check if file exists example Excel Macro code helps to Check if file Exists in location using Excel VBA. You can use FileSystemObject or Dir function to check if file Exists in location using Excel VBA.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


For example, When we are automating any task we generally save the output file in a location. Using this program we can check if there is any file exists already in the same location. So that we can delete or rename the old file.

Solution(s):

You can use FileSystemObject or Dir function to check if file Exists in location using Excel VBA.Follwoing are the examples to show you how to check If a file is already exists in a folder or not. Follwoing are the two different methods.

Check if file Exists in location using Excel VBA – Example Cases:

Check if file Exists in location using Excel VBA – Using FileSystemObjects

Following is the example to check if a file exists in a folder with using FileExists function of FileSystemObject.

Code:
'In this Example I am checking if Sample.xls file which exists in the same location of the macro file
Sub sbCheckingIfFileExists()
Dim FSO
Dim sFile As String
sFile = ThisWorkbook.Path & "\Sample.xls"
'OR
'"C:\Sample.xls" 'You can change this Loaction
Set FSO = CreateObject("Scripting.FileSystemObject")
If Not FSO.FileExists(sFile) Then
MsgBox "Specified File Not Found", vbInformation, "Not Found"
Else
MsgBox "Specified File Exists", vbInformation, "Exists"
End If
End Sub
Check if file Exists in location using Excel VBA – Using Dir Function

Following is the example to check if a file exists in a folder with using Dir function.

Code:
'In this Example I am checking if Sample.xls file which exists in the same location of the macro file
Sub Check_If_File_Exists()
Dim stFileName As String
stFileName = ThisWorkbook.Path & "\Sample.xls" 'You can change this location
If Dir(stFileName) <> "" Then
MsgBox "Specified File Exists", vbInformation, "Exists"
Else
MsgBox "Specified File Not Found", vbInformation, "Not Found"
End If
End Sub
Output:

If file exists in a location, it will show following message. Otherwise it will show “Specified File Not Found” message.

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Press F5 to check the output
  6. You should see a message box as shown above
  7. Save the file as macro enabled workbook
Download Example File:

Please Download the following example file.
mongopono.ru – Check if File Exists

LIMITED TIME OFFER
By |April 27th, 2013|Excel VBA|5 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

5 Comments

  1. Raman Goel February 5, 2015 at 1:54 AM - Reply

    Hello,

    I like the above VBA coding and want to know if an macro can be created to see the list of files exist in some particular folder or not?
    and instead of msg box pop-up, it will just show it in one cell adjacent to the file name if it exist or not? If it can be done then it will resolve all my purpose. Please e-mail me at —- if you already have any sample macro for it.

    Thank you

    • PNRao February 5, 2015 at 8:32 PM - Reply

      Hi Raman,

      You can use the tool available in our Add-in:

      mongopono.ru Excel Add-in

      Thanks-PNRao!

      • Raman Goel February 11, 2015 at 11:40 PM - Reply

        Thanks Buddy,
        Analysis Tab is awesome, however it is not resolving my purpose completely. The tab is pulling the file names from the folder but I still need to check the names manually to find if anything missing.
        We save some same set of files each day in particular folder, so what I am asking is if through formula it is possible to check if file exist or not and will revert with “Yes” or “No” then it will be great.
        i am not very good at excel so it is difficult for me to frame the formula. Can you please assist me if possible?
        Raman Goel

  2. Raman Goel February 5, 2015 at 10:14 PM - Reply

    Thank you for the quick response!
    However, the link showed a Analysis of data and I need an macro to check for various files exist in particular folder or not. I am not sure if the same link can be modify for my need or not?

    Could you please help me and assist me if an macro can be created to check multiple files exist in the folder or not and instead of pop-up message box, it will show in excel columns only?

    Raman Goel

  3. Raman Goel February 7, 2015 at 12:50 AM - Reply

    Thanks Buddy,

    Analysis Tab is awesome, however it is not resolving my purpose completely. The tab is pulling the file names from the folder but I still need to check the names manually to find if anything missing.
    We save some same set of files each day in particular folder, so what I am asking is if through formula it is possible to check if file exist or not and will revert with “Yes” or “No” then it will be great.

    i am not very good at excel so it is difficult for me to frame the formula. Can you please assist me if possible?

    Raman Goel

Leave A Comment


Related pages


auto fit row heightopen and activate workbook vbaascending order in excel 2007isblank formulahow to create a combobox in excel 2010how to remove duplicate data in excel 2010sql interview questions with answers pdfvba save worksheethyperlink in excel 2007excel select empty rowsexcel vba protect workbookhow to insert hyperlinks in excelobjfso.opentextfilehow to build a dashboard in excelexcel add chart titledefinition of vlookup functionactive cell in excelpivot table in vbatesting fresher interview questionsworkbooks.activateinterview questions in testing for freshersproject plan template pptadd chart title in excel 2010sample vb codehyperlink function in excelexcel 2007 countifsexcel sort alphabeticallysaveas vba excelhow to check duplicates in excel 2007multiple if then statements in excelscripting.filesystemobject vbaexcel vba matrixproject milestone template pptvlookup in excel definitionsumif exampleshow to unprotect excel sheet 2010 without passwordvisual basic command buttonabout excel macrosoperator operandexcel workbook tabsexcel draw chartexcel userform databaseexcel merge functionclear formatting excelformula for finding average in excelexcel macro codinghyperlinking in excelexcel vba copy data to another workbookexcel function in vbavba syntaxhow to find duplicate rows in excel 2010how to unprotect excel sheetopen file dialog vblocking a spreadsheetmultiple ifs in excelvba class methodsconsolidate data from multiple worksheets in a single worksheetadd the developer tab to the ribbon on wordexcel if statement with two conditionshow to protect a worksheet in excelcell interior color vbaexcel macro formexcel cell vbaremove empty cells in excelunhide all cells in excelvba excel sample projectshow to merge columns in excel 2007protecting excel filesvba print userformvba lookup functionms excel basics tutorial