Check if File or Folder Exists with Excel VBA (DIR function)

Check if File or Folder Exists with Excel VBA (DIR function) - Open File or Create Folder

Рет қаралды 90,694

5 жыл бұрын

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
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.
⬇️ Download the workbook here: pages.xelplus.com/vba-dir
Here's what you can expect:
- Understanding the VBA DIR Function: Gain a clear understanding of the DIR function in VBA, which checks for the existence of files or folders.
- Practical Application: See how to apply the DIR function in real-world scenarios, like verifying file existence or folder presence.
- Using Wildcards in Paths: Discover how to use wildcards like asterisks (*) and question marks (?) in file paths for more flexible searches.
- Step-by-Step Examples: Follow along with two practical tasks. First, learn how to check if a specific file exists and open it if it does. Second, see how to verify if a folder exists and create it if it doesn't.
- User Interaction: Understand how to use message boxes to interact with the user, providing options based on the existence of files or folders.
- Creating and Using Variables: Learn how to declare and use variables effectively in VBA for path and folder names.
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.
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 permission if we should create the folder or not.
🌍 My Online Excel Courses ► www.xelplus.com/courses/
★ Links to related videos: ★
Learn about declaring variables in VBA: • How to Declare (Dim) a...
Complete Excel VBA free course (playlist): • Excel VBA & Macros Tut...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#ExcelVBA

Пікірлер: 120

@LeilaGharani 5 ай бұрын

Grab the file I used in the video from here 👉 pages.xelplus.com/vba-dir

@mariostudio7 5 жыл бұрын

Your tutorials are TOP quality!! Thank you! 😊

@vinods1778 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

I'm glad to hear that my tutorials are helpful for you.

@helenasvensson6595 5 ай бұрын

Great tutorial! Thanks!

@bestscenes1469 2 жыл бұрын

Thanks so much. This video was exactly what I need for my project. You are a life saver

@ckokse 5 жыл бұрын

Thanks for another VBA video!

@vida1719 5 жыл бұрын

As always such a good logical explanation

@luisfelipegola5622 5 жыл бұрын

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

@LeilaGharani 5 жыл бұрын

Thank you Luis! I'm glad you find the content useful :)

@ajaythatipalli2197 4 жыл бұрын

Thanks for clear video, here I have a question, i.e., if folder already exists and user is willing to overwrite the current folder. Could u explain how to proceed plz.

@jslaybaugh902 5 жыл бұрын

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

@WebTechies82 5 жыл бұрын

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

@narendrangananathar8107 2 жыл бұрын

Amazing Presentation! It was very helpful to me. Thank you Leila

@LeilaGharani 2 жыл бұрын

You're very welcome!

@showtimehereiam 5 жыл бұрын

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

@LeilaGharani 5 жыл бұрын

I'm glad to hear that!

@msiddiqui4556 3 жыл бұрын

@@LeilaGharani Hi, this video is for one perticular file in folder, how can we check if there are multiple .pdf files in particular folder. If .pdf file exist it should return the value true else false, Need your support leila, thanx

@ankurjain4267 4 жыл бұрын

Thank you so much for your tutorial videos, need your help to get a VBA code to copy and paste a data from specific folder in one Excel sheet and files names are as per worksheet tab name.

@nabilsn411 5 жыл бұрын

I love your tutorials. Keep it up.. Thx

@LeilaGharani 5 жыл бұрын

Thank you! Will keep going :) Appreciate your support.

@2010pavi 4 жыл бұрын

can you tell me specific file open specific folder only please (if that file cope then past desktop or ex...will not open or destroy self)

@peterkristof9291 5 жыл бұрын

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.

@boooooooster 3 ай бұрын

Any solution for URL path-and-filenames on OneDrive?

@huynhngoclinh4167 2 жыл бұрын

Thank you so much Leila. Please help me How to delete the file that i did it in the folder two days ago

@jellyacecream 3 жыл бұрын

For file path, is it possible to search in a sharepoint instead of local drive?

@mjabeer Жыл бұрын

i needed it.. Thank you

@emptymind9107 4 жыл бұрын

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

@falakkhan4886 Жыл бұрын

Hi Leila. I really like your videos and they're super easy to follow but I'm having problem in Vba and macros tutorial. Please elaborate more in your videos.

@graywolfweb1361 2 жыл бұрын

Would anyone know how to use this exact functionality with sharepoint files? If exists exit sub else save file end if, for example.

@davidyenni3455 3 жыл бұрын

Hi Leila, Your videos' are very helpful. Thank you so much! :-) God bless, David

@LeilaGharani 3 жыл бұрын

You are so welcome, David!

@assamiyou 5 жыл бұрын

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

@piyalfernando4914 Жыл бұрын

Hi Leila, can you help me telling if is there any method to take a print out of a PDF file by VBA

@ZAFAR4310 Жыл бұрын

I have created a flow to find onedrive files but what condition should I use if respone to email me that file does not exist.

@bharanidharan285 4 жыл бұрын

Hi,thanks for the good vba explanation, and i want to move on some files one floder to another with of its file name ex- some of the file start with "aaa" it will move to the "A" floder can help me

@amoljadhav7392 4 жыл бұрын

Hi Leila, rather than entering that full file path, can we auto return to the active workbook path? as directory path may vary please resolve. I'm trying to use Workbooks.Open "ActiveWorkbook.Path" & FileName or ThisWorkbook.Path instead of the file path. but doesn't work!

@ashisbasak1175 5 жыл бұрын

Thanks Laila for this DIR function VBA

@LeilaGharani 5 жыл бұрын

You're very welcome Ashis.

@augustodemelo915 3 жыл бұрын

It is just great! You are for sure the best! Your didactic is just perfect!

@LeilaGharani 3 жыл бұрын

Thank you! 😃

@mukhaladalshimary4429 5 жыл бұрын

great explanation

@NP-zg3hq 4 жыл бұрын

I have thousands of pdf files with reference like xxxx-yyyy-zzzz each part denotes some structure. Is there any vba method available to move the files in to folders based on references structure using match function ? A:A - with file names B:B - corresponding folders to move individual files

@desaitejas 5 жыл бұрын

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.

@kcthedog6444 3 жыл бұрын

doesn't work on a Mac. any updates for browsing/selecting folder for modern Macs?

@abhinavvivek2732 4 жыл бұрын

Hi, Thank you for this informative video. Request you to please share information on Microsoft ActiveX library...which version should we use, which version is capatible with different excel versions. Thank you Abhinav

@ErJanardhanJethi 4 жыл бұрын

Hi.. I'm new to VBA, have a doubt and would like to clarify...how to check if a word file opened before opening in vba.

@kuzev 4 жыл бұрын

Does it work with files on a server or OneDrive?

@xguyzerx 4 жыл бұрын

how to you write? \\server\trackers$ - trackers folder? in vba how do you declare it?

@SuperHanme 2 жыл бұрын

Great, Thank You.

@MohAboAbdo 2 жыл бұрын

Thank you so mutch.

@pravinwaghchoure2277 4 жыл бұрын

Could you please upload sql database connection videos.. it will help us to learn more on database connections

@msiddiqui4556 3 жыл бұрын

Hi, this video is for one perticular file in folder, how can we check if there are multiple .pdf files in particular folder. If .pdf file exist it should return the value true or highlight that column where the file path is given. Need your support leila, thanx

@TheLonelymusic 4 жыл бұрын

I want to activate the workbook on error the code is to open the respective workbook from the path. I have tried with the below using on error goto label name. On error goto nxt WORKBOOKS("filename").activate 'Step 1 Nxt: Workbooks.open filename:=.....'step 2 On this code macro completed the step 1 and moving to step 2 please help

@anilulchala5532 4 жыл бұрын

Hello your video on DIR function is very helpful. Can you help me that can we open a folder and search with a word by VBA code

@md.saifulislamtuku9303 5 жыл бұрын

Thanks from Bangladesh

@sumanpaul1948 3 жыл бұрын

Hi, I am facing trouble to check the existing file in a specific folder. Because I am creating the file name automatically with a specific cell reference. Could you please help to get the VBA code to check if the file exists? Here I am showing my file path: [[ActiveSheet.Range("A1:O102").ExportAsFixedFormat xlTypePDF, FileName:="C:\Levi's Recipe\" & ActiveSheet.Range("P1").Value, openafterpublish:=True]] Thanks in advance.

@MalinaC 5 жыл бұрын

Thank you Leila! :)

@LeilaGharani 5 жыл бұрын

You're very welcome Malina :)

@nathaniel8422 Жыл бұрын

I get Exit Sub not allowed in function or property, it breaks on Exit sub line.

@kelvin55141 4 жыл бұрын

Request : Hi Leila, please you create a video where show us how VBA code can convert excel to pdf and email it as attachment. thanks

@as_jugadi272 2 жыл бұрын

Thank you mam...

@wayneedmondson1065 5 жыл бұрын

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!

@LeilaGharani 5 жыл бұрын

Thank you for your comment Wayne. My indent on the Surface (where I film) is 4 characters, but you're right, it actually looks like 2. When I open the same file on my other computer, it looks much more indented. I've always stuck to 4. It's worked well for me :)

@1gopalakrishnarao 5 жыл бұрын

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.

@1gopalakrishnarao 5 жыл бұрын

Thank you Madam. I am learning many things from you. I pray the almighty to bestow good health & Prosperity to you, as you are doing a great & noble job for the benefit of Excel lovers/users.

@valdereiantunes 4 жыл бұрын

Dear Leila I would like to know how to create a new folder, using the same name, but add a number in front of this folder already created, this method is like windows does, New Folder, New Folder (2). Oh and thanks for you explanation about this method, it's help me a lot.

@whoodiniiam 5 жыл бұрын

is there a way i can enter a range of values within one cel at the same time put it to graph say 5-20%?????

@LeilaGharani 5 жыл бұрын

not sure about this.....

@watchvideogame6755 5 жыл бұрын

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

@LeilaGharani 5 жыл бұрын

I have no idea! Is that similar to the step chart here: kzfaq.info/get/bejne/aK6InMqJ17qpaX0.html

@rajumasih234 2 жыл бұрын

Mam really your tutorial is to good Mam now my question is. I am using a network excel macro enable file but when I open it and start work and same time my partner also try to open that file .can teach me how I make a make macro to restrictions it reopen please mam

@shwan_ibrahim 2 жыл бұрын

thank you

@User-xw4dt 5 жыл бұрын

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

@beelogtv9549 2 жыл бұрын

How to search multiple path?

@aryanpramod 5 жыл бұрын

Thanks a lot....

@VictorPerez-uy3pg 3 жыл бұрын

Leila always coming through

@berniceabaya9928 4 жыл бұрын

My filename is in korean language and i cannot open the workbook since it returns ?????. Help please

@mohammadasad9890 5 жыл бұрын

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

@buykabuyka5006 3 жыл бұрын

thank u

@willdobler7763 3 жыл бұрын

Hi I tried to use this to check a sharepoint directory to see if the file is located there. Its giving me a Run Time Error '52' Bad file name or number. Do you have any suggestions? I've been using your videos to catch up on a new job so any help would be appreciated

@lidajo18 2 жыл бұрын

I have the same problem

@naveensingh3267 4 жыл бұрын

Thnx leila ,bt i want make a folder with name any date -yyyy.mm..and save my file in to folder with name -DD-MM-YY

@RJ-dz1hu 5 жыл бұрын

Thank you

@LeilaGharani 5 жыл бұрын

You're very welcome.

@maihuire11 5 жыл бұрын

Excel is pretty :)

@hosseinhosseinpoor4845 3 жыл бұрын

thanks..

@abdanomer 5 жыл бұрын

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!!

@LeilaGharani 5 жыл бұрын

This video has the answer: kzfaq.info/get/bejne/nKpyZK6l3s7UY40.html to get them together in one cell you can use the 2nd formula shown there.

@abdanomer 5 жыл бұрын

Leila Gharani Thank you 👍🏻 In my excel 2016 there is no built in TEXTJOIN , so I used an array code for TEXTJOIN from this website codereview.stackexchange.com/questions/180332/test-for-array-vs-range-in-my-textjoin-udf It worked greatly using your function

@Mohammad3000100 5 жыл бұрын

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.

@LeilaGharani 5 жыл бұрын

You can define the file name to be a specific sheet name or you can have the names you want inside cells and the macro can use these. There is a good blog post here that might help you: www.contextures.com/excelvbapdf.html

@Mohammad3000100 5 жыл бұрын

@@LeilaGharani Thanks Miss Leila that's exactly what I'm looking for.

@md.saifulislamtuku9303 5 жыл бұрын

Thanks

@LeilaGharani 5 жыл бұрын

You're very welcome Saiful. Thanks for watching :)

@raone6822 5 жыл бұрын

What can I do if changes path file name??

@LeilaGharani 5 жыл бұрын

You can keep the path name in a cell or use GetOpenFileName to have the user pick the file using the open dialog box.

@shashankgupta8723 5 жыл бұрын

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

@ricos1497 5 жыл бұрын

Sub addWorksheet() Dim wbk as workbook Dim wsht as worksheet set wbk = activeworkbook 'or Workbooks("someOtherWorkbook") set wsht = wbk.Sheets.Add End sub That's it in its simplest form. You copy this into a module (in visual basic editor, select Insert/Module). You can assign to a button or whatever. The .Sheets.Add statement will allow you to position the sheet, or enter multiple sheets: set wsht = wbk.Sheets.Add After:=Sheets("test"), Count:=3 'adds 3 new sheets directly after a sheet called test. Hope this helps.

@shashankgupta8723 5 жыл бұрын

@@ricos1497 thnaks

@digildas6822 5 жыл бұрын

Showing path not found

@gauravmathur4940 5 жыл бұрын

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.

@LeilaGharani 5 жыл бұрын

Thank you Gaurav. Regarding your query, the cells that show up as A, B are they below the headers? So you have B1 - Banana, B2 - Quantity - let's say 2, then you'd like B3-B4 to show a B? Do I understand it properly? If yes, you can type this formula in B3: =IF(ROWS($B$3:B3)

@gauravmathur4940 5 жыл бұрын

@@LeilaGharani thanks for your revert. To clarify, i wrote incorrectly, data set is from A1-B4 (A1-apple,A2 banana, A3-Oranges, A4 Mangoes) and B1-B4 is quantities between (1 to 10). My basket is in cell D1 to G10. Now the fun part is if B1=5; B2=6; B3=2; B4=8 (that is 5 units of apples, 6 0f Banana, 2 of Oranges; 8 of Mangoes) then say D1-D5 will have have letter A; D6-D10 & E1 will have letter B; E2 toE3 will have letter O; E4 to E10 & F1-2 will have letter M. So, can we have it filled dynamically so that if value in units (B1 to B4) changes than the letter in the basket will also change ?

@DavidFMayerPhD 4 жыл бұрын

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.

@LeilaGharani 4 жыл бұрын

For me it depends on the task at hand. If I'm looping through columns I go R1C1 style, other times I generally prefer A1 style.

@DavidFMayerPhD 4 жыл бұрын

@@LeilaGharani I use ONLY R1C1 style so that I don't make mistakes. I see ZERO value in the A1 style. I don't want to have to remember which letter of the alphabet Q is.

@Betterifitsfree 5 жыл бұрын

OUCH! My brain is hurting 🤪

@candidoprc80 2 жыл бұрын

Great!!! Leila, please review the function below. Sub arquivo_existe() Dim NomeArquivo As String NomeArquivo = VBA.FileSystem.Dir("C:\Users\Paulo Candido\Downloads\ResultadoLotoFacilCaixa\D_lotman.htm") If NomeArquivo = VBA.Constants.vbNullString Then MsgBox "Arquivo não existe." Else MsgBox NomeArquivo '< Leila, I ask you: What function do I use to save the file "D_lotman.htm" as "D_lotman.mhtml? > End If End Sub

@peterjmaida2057 5 жыл бұрын

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.

@LeilaGharani 5 жыл бұрын

Thank you for the kind words. Yes - I have a master's degree - but honestly I don't think that makes me smart. I knew how to study and pass exams, but that doesn't mean I remember anything I learnt :) I think everyone is good at something. In my case, it might be explaining formulas or VBA code - in your case it might be something else - but then again, you're watching Excel videos in your free time! Not many people do that. The education system has a way of branding us with our grades and schools but the most important things I learnt in my life that impacted my career was actually outside the education system. So let's say, depending on the topic, we're all smart in our own ways...

@Alhusam 3 жыл бұрын

That was pretty neat, however, It would have been more useful if you had explained how to overwrite an existing folder without a prompt.

@mbj920 Жыл бұрын

did you ever figure this out?

@Alhusam Жыл бұрын

@@mbj920 Yes, I did.

@desaitejas 5 жыл бұрын

Hello,

@codymckinney4934 4 жыл бұрын

Type mismatch

@MJLCabral 4 жыл бұрын

Is there a vba code to check the name of user with workbook open on network drives

@refinededusoft Жыл бұрын

How can you check whether the file exists without having to write the full path "C:\users\LG...". let's say you're checking whether a file in the same directory as the excel sheet you're writing the macro on...or rather, how do you get the directory of the current spreadsheet? please answer soon, my assignment depends on it