Excel VBA: Check If File or Folder Exists (DIR) – Open File or Create Folder




About this tutorial:

Video duration: 9:32
Use the DIR function in Excel VBA to check if a file or a folder exist. You can then decide what to do if they exist. For example you can open the Workbook to copy values from and then close the workbook.

I will also show you how you can handle cases where the file does not exist or the file name is not typed correctly. In that case VBA generates an error but you can easily avoid with the IF statement.

[5:10] I also show you how you can check with VBA if a folder exists. and if it doesn’t exist, how you can create the folder. We use the message box function to ask the user for…

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

38 thoughts on “Excel VBA: Check If File or Folder Exists (DIR) – Open File or Create Folder

    Dhanush bandi

    (October 10, 2019 - 1:34 am)

    Great tutorials thank you,
    Could u do tutorial on transfer specific files from one folder to another folder using vba…

    Vinod S

    (October 10, 2019 - 1:34 am)

    Leila Gharani
    i have become a Great Fan of you,the way you explain the concepts,tips and communication . …Excellent

    DavidFMayerPhD

    (October 10, 2019 - 1:34 am)

    Leila Gharani, I have a question:

    Why is it that you use the A1 style of notation instead of the R1C1 notation? It is far more convenient, especially when using VBA, to refer to NUMERIC row and column. The following example from VBA code shows how much more convenient Row/Column reference is:

    SourceRow = ActiveCell.Row
    Tenure = Cells(SourceRow, 2).Text
    NameLast = Cells(SourceRow, 3).Text
    NameFirst = Cells(SourceRow, 4).Text
    ActiveWindow.ActivatePrevious
    Cells(DestinRow, 1).Value = Tenure
    Cells(DestinRow, 5).Value = NameFirst
    Cells(DestinRow, 7).Value = NameLast

    This is especially convenient when dealing with worksheets with more than 26 columns. Yes, I realize that this can also be done with A1 notation, but not as cleanly.

    Remars Agustin

    (October 10, 2019 - 1:34 am)

    just wondering if possible (in your free time), how can I use a code if the file is inside the network, I'm trying to program an automated billing statement but forgot some PC's are not updated with Excel 2013 and above lol… need to link excel with access to promote database to thee worksheet. Thank you again for your formula's tutorial, you are indeed the excel queen!!!

    maha siva

    (October 10, 2019 - 1:34 am)

    Hi I also need very similar help as the previous one how to display Available & Not in available in the column if file exists in folder it should print in the column available if not it should print Not available in the column

    TutorialesLAB

    (October 10, 2019 - 1:34 am)

    Excel is pretty 🙂

    showtimehereiam

    (October 10, 2019 - 1:34 am)

    Thank you so much Leila.
    Once again you saved my life, it works like charm !
    Keep it up with the great work

    Mukhalad Alshimary

    (October 10, 2019 - 1:34 am)

    great explanation

    Tejas Desai

    (October 10, 2019 - 1:34 am)

    Hello,
    Basis this tutorial, I created the folders. Now i have multiple files and I want to copy these files in different folders basis the excel where I have mentioned the folder name and next to it have mentioned the file names. Need a Macro which can read the folder name mentioned in excel column and copy those respective files in that respective folder.

    Tejas Desai

    (October 10, 2019 - 1:34 am)

    Hello,

    Slaybaugh Jeff

    (October 10, 2019 - 1:34 am)

    i could you some help with a vba code to repeat a macro on all the sheets in my workbook

    pramod kumar yadav

    (October 10, 2019 - 1:34 am)

    Thanks a lot….

    Digil Das

    (October 10, 2019 - 1:34 am)

    Showing path not found

    Marc

    (October 10, 2019 - 1:34 am)

    Can you make a video of VBA Code for Button -> Make a Pic of the sheet -> open Mail -> paste and write the correct mail adress please. I have a KPI Overview Sheet for my Employees and want to send each of them there KPIs. #ideas #curses #recommend #questions #onenote

    Peter Kristof

    (October 10, 2019 - 1:34 am)

    Hello Leila, Is it possible to take file name from cell? Lets say I got folder with 52 excel files named 1-52 each one represent a week in the year and I would like to use macro to always open file for current week.

    Ashis Basak

    (October 10, 2019 - 1:34 am)

    Thanks Laila for this DIR function VBA

    Mohammad Hamoud

    (October 10, 2019 - 1:34 am)

    Ok, here is the situation most of the time I convert my work to pdf using pdf pro.
    And I use multi sheets workbook.
    But what bothering me is that I always have to write the sheet's name manually.
    Is there away to force the excel to print my sheet with its name not the workbook name.

    mohammad asad

    (October 10, 2019 - 1:34 am)

    Hai mam how ru? Mam i want one video how to use excel in logistics plz

    Malina C.

    (October 10, 2019 - 1:34 am)

    Thank you Leila! 🙂

    Peter J Maida

    (October 10, 2019 - 1:34 am)

    Great video but one question. Why did you say that you were not smart in the other video when I told you that you are. Don't you have a Master's degree? I graduated from high school 30 years ago but barely. I struggled in school now I am 48. Are you kidding me? You are brilliant. Having said that about myself, now who isn't smart, you or me. Don't say you are not smart.

    Gaurav Mathur

    (October 10, 2019 - 1:34 am)

    hello, @leila wonderful videos. Always check your videos if i get stuck. Can i ask a question, maybe a topic for a subsequent video. If my data set is from A1-B4 (A1-apple,B1 banana, C1-Oranges, D1 Mangoes) and B1-B4 is quantities. i need to create a visual dynamic basket where if a person enters 4 units of apples – 4 cells show up A, then say 3 Banana subsequent 3 cell are B, and next 0 oranges then nothing in the basket and say 5 mangoes then 5 cell after Banana(B) shows M. is such a thing even possible.

    ckokse

    (October 10, 2019 - 1:34 am)

    Thanks for another VBA video!

    Wayne Edmondson

    (October 10, 2019 - 1:34 am)

    Hi Leila.. thanks for this video on DIR and examples of how to use it.. very helpful. I noticed that your code indents are 2 characters vs. the default of 4. Is that your normal preference or do you do that just for the sake of the video presentation? I have always used the default of 4 spaces, but I see lots of debate on the Internet about 2, 3 or 4 spaces. Any comments on what you recommend for students learning VBA today? Thanks again and Thumbs up!

    Watch Video Game

    (October 10, 2019 - 1:34 am)

    hello Leila Gharani. I want to ask you, can we create bending moment diagram in excel. (For Civil Engineering Major)

    Md.Saiful Islam Tuku

    (October 10, 2019 - 1:34 am)

    Thanks from Bangladesh

    Gopala Krishna

    (October 10, 2019 - 1:34 am)

    Excellent. Taking lot of trouble to make the things very easy for Excel/VBA learners. Thanks a lot to my beloved/beautiful/brainy EXCEL TEACHER.

    Abdelrahman Omer

    (October 10, 2019 - 1:34 am)

    Great video as usual
    I have question not a VBA related!😉

    What function can I use to make lookup for multiple items?
    Such as an example I have many student results I want to view the students who has the same results in one cell separated with “,”
    Is that possible?
    I thought of aggregate function with combination with others functions!!

    Jonas Sami

    (October 10, 2019 - 1:34 am)

    Please, would you consider a lesson on how to interact between Excel, Access and SharePoint.

    Vida

    (October 10, 2019 - 1:34 am)

    As always such a good logical explanation

    ra one

    (October 10, 2019 - 1:34 am)

    What can I do if changes path file name??

    Marios HP

    (October 10, 2019 - 1:34 am)

    Your tutorials are TOP quality!! Thank you! 😊

    Betterifitsfree

    (October 10, 2019 - 1:34 am)

    OUCH! My brain is hurting 🤪

    ExcelIsFun

    (October 10, 2019 - 1:34 am)

    Wow!!! Such a crisp and clear picture for your video – best I have ever seen, Teammate Leila!!! Thanks for the VBA fun : )

    Shashank Gupta

    (October 10, 2019 - 1:34 am)

    Can u please tell me how to add worksheets using vba?

    Md.Saiful Islam Tuku

    (October 10, 2019 - 1:34 am)

    Thanks

    Blr Acc2

    (October 10, 2019 - 1:34 am)

    Hi Madam This is Girish I need one help from you am getting difficulty in removing spaces in excel, i tried Trim,Clean, Substitute and Text to column functions for removing spaces in Excel sheet but not working please help me further i don't have your mail id for sharing the excel file. My mail id girishmadikeri@gmail.com, please share the your mail id i will share excel file.

    Luis Felipe Gola

    (October 10, 2019 - 1:34 am)

    Hello Leila. Very well explained and useful in future projects using VBA. I am a fan of your channel. Thanks for sharing this content.

    nabil sn

    (October 10, 2019 - 1:34 am)

    I love your tutorials. Keep it up.. Thx

Leave a Reply

Your email address will not be published. Required fields are marked *