Excel VBA Introduction Part 22 - Files and Folders (FileSystemObjects)

[@MyAudioBookCompilation]

This is the video that helped me created my method on how to create an exe file and installation package for excel projects. This is my most favorite channel in youtube. Everytime I found someone who is asking how to learn vba the very first answer that always came into my mind is the Wise Owl Tutorials. All the credits goes to Mr. Andrew Gould. Best mentor and teacher. 😀

[@inactiverahul]

Wise Owl, as usual is on the top. The simplest, thus - more convenient way to deliver the knowledge to the audience. Thanks for these wonderful videos, Andrew.

[@WiseOwlTutorials]

Thank you Rahul!

[@daves4026]

Just perfect. Spent a whole day researching and testing code then remembered you’ve already aced this training. Thanks again

[@WiseOwlTutorials]

I hope it doesn't feel like a day wasted! Thank you for watching!

[@zozozozo43]

Andrew Gould is a Giant, Giving and kind person . He does not skimp in listing the details neither in listing the side notes of some idea even if he does not use it in the code. He is a quiet person, and fortune for all people. So, I instancy pray Allah to Keep you and Choose for you all the best in this life and the other Eternity One!! ~ Assem

[@wowJhil]

I am here so many years later, but this is SO valuable. I am a slow learner, but when I get good information like this it's not hard to learn! Actually I watch this whole video carefully, to better understand a code I had found and already used. I had a functioning code, but this video helped me understand it better, tidy up some undeclared variables and also skip some unnecessary code since there is this extension of file functionality in Runtime library. Eventually, slowly but surely, I will have watched every video carefully :)

[@WiseOwlTutorials]

Happy to hear that you found it useful! Thanks for watching and taking the time to comment!

[@AnilSharma-sg4qw]

So in just 50 mins, you learnt how to work with FSO .......... recursive function call demonstration was outstanding.

[@WiseOwlTutorials]

Thank you Anil, I'm happy that you enjoyed it!

[@tigranhayrapetyan8357]

One of the best & useful videos for VBA programming. Recursion is explained on the highest level. I want to use it in many routines in work, but before this moment didn't understand how exactly it works. Now It's time for recursion! Thank you for such a good tutorials!

[@WiseOwlTutorials]

You're very welcome, I'm happy that you found it useful and thanks for watching!

[@DucLe-tc8ps]

I rarely comment on any videos and tutorials; however, this is one of those exceptions. Your tutorials are top-notch, and your technical explanation style is terrific, as well. I wish more people discover your channel and reap the tremendous benefit you are offering. I am going to make a donation as well. Thanks for the knowledge.

[@alanelston2330]

Of all the various VBA tutorials across different platforms that I have seen, Andrew’s are right up at the best, IMO. He very clearly and completely explains without missing things out. This seems to be a very rare talent, - few people seem capable of explaining things they know fully enough including all details, such that those that don’t know can easily learn. Of those few people, most seem to degrade, as time goes on - as time goes on they seem to miss out more and more, somehow forgetting to include all details as much as they may have done early in their career. Andrew does not appear to have this problem. Andrew is a top educator, IMO. He deserves to prosper! :) Alan

[@KM-co5mx]

Thank you for showing us several examples of using Objects (FileSystemObject, Auto Instancing, CreateObject) and the Pros and Cons thereof! Very helpful. 😁

[@WiseOwlTutorials]

You're very welcome! Thank you for watching and taking the time to leave a comment!

[@ashokkandukury2870]

deep enough to loose breathing........I must loop through this until I understand. Anyway you are the best Andrew

[@maurocastagnera8949]

Simply the best VBA video tutorials on internet! Thank you very much. You are an awesome teacher!

[@danraats5840]

Thank you for your complete and clear explanations of Excel VBA using realistic examples. I also appreciate how you also teach solid coding practices along the way.

[@Yangar03]

Andrew The Wise Owl, you are awesome!!! Your videos are great, it really digs deep and are very comprehensive. I am glad I found you online.

[@twrbells]

Excellent video. Clearly explained and moves at a good pace. Now I finally understand how FileSystemObject works!

[@tommik1283]

This video was the core step for me to start using that MS Scripting Runtime in my MS Access project. Thank you so much! It is a real shame that they were not able to integrate full Unicode support into VBA environment and using this FileSystemObject etc. approach solves the problem. Finally I am able to handle Unicode strings on file system level straight from MS Access code!

[@WiseOwlTutorials]

Happy to hear that it helped you, thanks for watching!

[@igorbandora1047]

Dear Andy, I would like to thank you and WiseOwl for this awesome tutorial. Thanks to you I started writing VBA code which helps me a lot in my work. I even made small programs. Your way of explaining everything is just so great! Thanks again!

[@maurocastagnera8949]

Dear Andrew, now that I understand better the English language, I appreciate more and more the enormous and fantastic work you have done with regard to vba. I hope one day to get at least half of your excellent level of preparation on the subject. You are really exceptional! Thank you very much!

[@wulp1191]

Nice one again. Tip: To get the folder and the filename: Shift-right-click the file and select: "Copy as Path". Pasting this will paste the folder name and the filename between double quotes.

[@WiseOwlTutorials]

Great tip, thanks for sharing!

[@scottn9492]

This is the best video in the collection! I have been struggling trying to write VB Scripting for Adobe Illustrator. All of the examples from adobe use the CreateObject method, thus killing the IntelliSense. Not to mention the documentation from Adobe is Terrrrible. This has stopped the constant page flipping and headaches.

[@Tubedog8]

It has taken me longer to get through this very informative lesson. There are really 17 different subparts to this lesson, so I need to be patient with myself. Thank You once again for all the hard work involved in giving this gift to us!! I really appreciate it.

[@sevilladas]

Outstanding! I enjoy how you explain the logic as you step thru your code. My favorite Excel VBA site!

[@janezklun]

I just began to repeat theme with files,folders and these are just great videos for the topic, thank you, Cheers

[@mdimran1a]

So clear explanation is in your tutorials. Keep it up!

[@WiseOwlTutorials]

Thank you Mohammad!

[@tajsay]

That was a another great tutorial. Thank you, Andrew and WiseOwal for the best tutorials.

[@WiseOwlTutorials]

You're very welcome Sayyad, thank you for watching!

[@inactiverahul]

Thanks for all of the tutorials! I have learned so much. Can't wait for the next one

[@WiseOwlTutorials]

Happy to hear that Rahul, thank you for watching!

[@soodashishr]

Thank you for uploading such informative videos on complex topic like VBA. The video are structured and has a logical flow and covers all pertinent topic relate to subject. Looking forward for more videos on VBA. Thank You once again. Ashish Sood

[@piotrkuc8936]

Thank you a lot, hope you are healthy, happy and rich! Your videos are just awesome!!!

[@sribalajispaceship]

Very informative and simple..I am using your videos to program in vba..

[@amitgzb]

Excellent / Commendable way of explaining the subroutine. keep it up. you are really great "Gyan Guru".

[@shmred5705]

great explanation! this is how the code must be explained

[@rr2100]

Awesome job you have made with all these very helpful videos, thank you for your time and effort.

[@franciscobaptista7139]

Top tutorial. Everything is very well explained. Keep up the good work!

[@jbsepulveda27]

Thanks Andrew The Wise Owl, this really helped me a lot.

[@tugaric]

You are a very talented teacher, thank you for sharing :)

[@WiseOwlTutorials]

And thank you for watching and for taking the time to leave a comment!

[@donbogdala5428]

Thanks for all of the tutorials! I have learned so much. Can't wait for the next one

[@UmamaheshwarAskula]

I like your teaching method and easy follow you. Thank you for uploading all your tutorials. Waiting for video for uploading by you.

[@abubakaroaliyu1476]

your video is educative, you are rely gifted you impact knowledge in me which people look at me as one of guru thanks a lot. .

[@ventjemazzel8822]

Excellent teaching!!! Thank you very much!

[@kjesu2715]

Really useful...

[@WiseOwlTutorials]

Thanks!

[@mattvba25]

A MasterClass as usual... :)

[@WiseOwlTutorials]

Cheers Matt!

[@chizamanda5149]

Thanks very much Wiseowl, you have helped me know vba

[@rjm1540]

really enjoyed watching this and hopefully i could use these techniques in my work place (still a long way to go)

[@Amit_the_Analyst]

you are the best Andrew sir.

[@zacharyplotzker9155]

By far the best VBA tutorials! Quick question. I was putting some of the new knowledge I gained here to work. Built an application that looped through all file in a given folder and if certain requirements were met, modified some data in file and saved file as a new version within same folder. I was expecting that new file I saved to also be looped through but it didn't (which is good, would of caused an infinite loop if my expectations were correct). Just wondering why the new file was not evaluated in the for each...next loop.

[@lucabottini9302]

Great!!!!!

[@WiseOwlTutorials]

Thanks Luca!

[@tarekyeh6245]

Thank you ,great video as usual :)... i never knew about the call stack thing.

[@mehmetcanbulat8361]

the Best Teacher so farrrrrrrrrrrrr (:

[@KM-co5mx]

This is great! Thank You for sharing smile 😃

[@WiseOwlTutorials]

You're very welcome, thank you for watching!

[@portapere]

Just wonderful! Great job!!

[@amandafernandesramospasson5862]

great! thanks for the amazing classes

[@91kunalm]

For those of you who are finding that their tools -> References has been greyed out, you need to exit form break mode. Press the stop button (the grey square beside green play button, just under the run tab in VBA editor).

[@torque6389]

Excellent video!

[@ravinderavula2025]

Thanks for the video was very helpful ... thanks again

[@iamsopure1043]

Thanks for the excellent video Andrew! I am a bit confused about how this, "Sub CopyExcelFiles(StartFolderPath as string)", works. Since in your code I do not see you set a value for "StartFolderPath" yet in the local window it shows that "StartFolderPath" is actually equals to a folder path. Why will this happen? Thanks.

[@enricograndi1112]

Thank you Wise Owl! Excellent video! I have a question for you: instead of copying the files to another folder, how can I move the first sheet of each workbook in each folder into a new workbook (for each folder) and then save it?

[@andrewhamilton7475]

First of all, your videos are fantastic so thank you. Just wondering using fso has any benefits of the mkdir method in vba?

[@ethofmeyr]

Excellent tutorial!!!

[@francist3979]

once again, fantastic video!

[@GopalarathnamS]

Thanks for the set of introductory tutorials. A quick question - when we make a code referencing the Microsoft scripting run time library and share it with another user. We need to request them to check this. Is there a way out that it is automatically selected or ticked via code?? That would be a better complete solution for this course part -22. Please reply as comment. Thanks in Advance. I would try to google out as well, however thought to comment as it would give a completeness to this nice video.

[@rock78785]

Yes, we need to ask them to check, or change our code to late binding with the use of the keyword Create object.

[@0807elephant]

Many thks for the video. They are the best!

[@rajeshpuj]

Thanks for the sharing this knowledge... I am struggling to list a unique filetype data of given folder including subfolders within a listbox. Could you please show one example. I was using collection but moment it goes to subfolder loop, collection is getting appended with subfolder file types. I was expecting a unique filetype list. Appreciate your help. Thanks.

[@zouhairwarii3079]

Thanks a lot, you do excellent videos

[@DennisYeadon]

Thank you for the tutorial. It is very informative and I enjoyed the presentation until I received a Run-time Error '70': message Permission Denied when the CopyExcelFiles routine is called. I have done some research, tried it on another machine but to no avail. I am using Windows 10 with Office 365. Is there a solution to the issue?

[@Meolimo]

Hi :-) I wonder if there's a module about read and - or wrigt in a closed worbook without open it. Thanks again M. Limoges from Longueuil, QC.

[@aaronanderson1068]

Quick questions that I hope I haven't skip by mistake. Is there a way of skipping over excel files that are currently opened in a shared drive? I was thinking maybe an “if, then” looking for certain characters…? These videos have really helped at work and thanks again for the video posts.

[@aaronanderson1068]

I thank you sir!

[@aaronanderson1068]

***** One more quick question (I hope it is an easy answer). On the line "Debug.Print f.Name" I tested this and, of course, it works. However, I am wanting to skip or exit that file and continue with the next. What would that look like? I have done some research and it appears complicated and very unique to each scenario. Links are always welcomed if it's a long explanation. Thanks

[@aaronanderson1068]

***** I watch the video again and perhaps an "IF NOT...then" statement that would take the files that don't include ~$ and then Set Folder to those files? thanks

[@aaronanderson1068]

Thanks again! Its that that makes the difference here. I just have to keep practicing and watch these videos.

[@alesloom]

thanks , your vids helped in my project .

[@bin7562]

You are the best! Thank you so much!

[@Bradza101plays]

think im in love. so awsome at your videos

[@lonelysoul121]

What if user cancels the choose window and we get a debug????

[@musashi1763]

There is one thing not yet clear to me. When do you use fil. method and when do you use fso.method? I can't seem to differentiate between fil and fso

[@sumantkalamkar1190]

Great video man..!

[@juandavidb]

Great tutorials! I have a question, I have one Excel column with numbers and a folder with photos where each one matches a number in Excel. Then I want to automatically link each number in Excel so that it opens that file (hope I'm clear explaining). Is it covered in one of your videos? If so, can you lead me to it? Thanks in advance!!

[@juandavidb]

Wow! Thanks a lot, I will try and will keep you informed of my results. Thank you thank you!

[@jaysolanki8366]

Can someone explain in simple words what is an instance please......

[@alanelston2330]

I will try a simple Layman explanation. Partly instance used here is just technical jargon. Go back to about minute 3 of Andrew’s video, the bit about making the object. The Dim Fso bit tells you what sort of object you are talking about, but it doesn’t make one. The Set As New bit makes a new object of that type. The word instance is just a bit of technical jargon saying something like that, so you could say these two mean the same thing The Set As New bit makes a new one of that object type defined by the Dim Fso bit. The Set As New bit makes a new instance of that object type. Sometimes we might go on to say something like we have instanciated the variable. Before doing that Set As New bit, the object does not really exist, we have just prepared to make it by defining what sort of thing it will be. Instance in this respect means that everything possibly with the thing is made available. The Dim is done at compile and so Excel VBA Knows about / is aware of it allowing intellisense. The Set effectively “makes it live.” You could not do much with the variable until you Set it. I sometimes think of it like this: When I use Set As New then, really I am saying something like Set As a New instance of I am not always allowed to do that. For example I can Dim a variable as a worksheet. I can’t then Set that variable to a New instance of it. Microsoft don’t like us to do that. They make a few worksheets, and we can makes some more with the WorkSheets.Add-Method. We can Set a variable to any of those worksheet objects if we do the Set code line but miss out the new and so do something like Set = an existing worksheet, something like Dim Ws As Worksheet Set Ws = Worksheets(1) I am not sure I like so much this explanation of mine. It’s the best I got so far.

[@ALEXSHUNCAI]

Wise Owl, Perfect! I was wondering we transfer one file from the source folder to the desired locations, what if I want to transfer multiple files in one go, rather than transferring one by one, what is the best and easiest way to do that? Thanks!

[@WiseOwlTutorials]

Hi Alex! You could either loop through the files, applying the Copy or Move method to each file in turn. Alternatively, you could just apply the Copy or Move method to the entire folder. Is that what you meant?

[@ALEXSHUNCAI]

Again, thanks for your quick response, I got the loop to work, however, here is a tricky part, what if I want to copy or move files according to the date and time of these files created? How I can do that? Thanks!

[@chrissampson6861]

Hi, great video, is there a similar way to the create object method to add a reference to the windows script host object model? Need to use it to check if copying/zipping files has completed before moving to next step and don't want to have to manually set reference on each users machine, keep getting either user defined type not recognized or ActiveX can't create object error messages

[@jojocarrillo]

Thank you. but i would like to copy multiple files within different folders into one folder based on a database which contains the reference e.g. WF-002811 R2 (database ref) > WF-002811 R2 EMBEDDED LAYOUT OF C1 4TH FLOOR (actual folder name)... I was thinking of using the search function in VBA to solve this and I need it to loop through the whole database and copying all the files. I'm new in VBA.

[@b.petrushchak]

Thank you!

[@jenluanhoi7209]

Hi Mr. Wise Owl. It is ME again. Thank you for another wonderful tutorial (so advance for ME. Although the subject is very complex, your way of explaining and demonstrating the scenario has made it possible for me to understand, which is not an easy task for someone who is new at these VBA stuff). As usual, I NEED HELP, a lot of help. What I have in my worksheet is list of HYPERLINK for external link files, which is about 1,200 files. What I have managed was able to write code to open the FOLDER where all these linked files are located. Here is what I have written followed. But what I am trying to do is to open the folder and it would TAKE ME TO THE EXACT LOCATION OF THAT FILE. Example, like when I download a file from Internet or save an attachment from an email, after the file done downloading, I would right click and it give me the option to "OPEN THE FILE LOCATION"..What I would like do is something like that. (I am exhausted from searching for the answer, would you help me!). Thank you. Dim MyFolder As String Myfolder = "C:\users\HPJan2016\desktop\bss" Call Shell("explorer.exe " & Myfolder, vbNormalFocus)

[@jenluanhoi7209]

Thank you for "ANSWER" my call for help. In the worksheet, colum A, I have this function: INDEX(BSSTCList,ROWS($A$2:A2)) which to get a list of all the file in my BSS folder. Then, colum B, I have this function: HYPERLINK(A1). Well, as I explained previously, when I click on the HYPERLINK, I would get the linked file open. IN the VBA, I have this code written: Myfolder = "C:\users\HPJan2016\desktop\bss" So when I run the VBA, I would just get the FOLDER open but would not directly to where the file is located. What I would like to be able to do is when I run the VBA, the folder would open but also take me to the location of the file where I have the HYPERLINK. (I am very sorry for not be able to explain what I need. English is my NOT only problem, VBA is like the language of Mars.) Your help is always greatly appreciated.

[@edtardaguila3599]

Hi sir I know this could be late, but what if I transfer the file to another user does he or she needs to activate scripting library? To his vba to work to his pc or laptop those scripting library or it will become inherent to the file being transferred?

[@EricHartwigExcelConsulting]

Hello Wise Owl Team, Thank you for all your amazing content! Could you create a video on shortcut folders and how to update their folder path using VBA or if you already have please direct me to it?

[@WiseOwlTutorials]

Hi Eric! We don't yet have a video on this so I'll add it to the To Do list!

[@EricHartwigExcelConsulting]

@@WiseOwlTutorials spectacular!

[@esdkingston]

I like this.

[@amitbodhwani9119]

Hi, I have a button called UpLoad on my webpage which ask to upload the .csv file. I am able to click that button using VBA code and after clicking that button on webpage a popup appears similar to open dialog box which ask to upload a file for example say "Star.csv" but i want to insert the file name called "Star.csv" which is stored in my local directory automatically using VBA without user interaction. Any help will be appreciated . Thanks!

[@undrawingend]

This has helped me a lot great video - watched a few others of yours and they are great, I’m having some difficulties on some code I have cut form another macro that does something similar but I need to move files into subfolders with only a reference to the name - eg headersheet 012 into file 012, any advice?

[@WiseOwlTutorials]

Hi there! You might find this video useful kzbin.info/www/bejne/a6G7k5SMf66km8U I hope it helps!

[@TheLarigo]

Hello and thank you for this helpful videos Please if it's possible , can you show us how we can creat folder and subfolder without to have THE BUG window every time when the file is already exist and also if you can , the vba code that we can run via a buttom in the worksheet who display in html page Thank you very much for your help

[@ethansubbu1768]

Hi Andrew, While trying to copyfile i am getting error msg expexted end of statement

[@mkanalysis]

THANK YOUUU!!!! that was awesome

[@ethansubbu1768]

Source:= complie error expected: expression

[@abdulsafwan1840]

its Nice Video, can i get the sample file for easy reference

[@jojocarrillo]

Thanks for this. But I have a project that needs to loop through a list of partial folder names and copies PDFs to a new location... and so on. I have 200+ folders with PDFs but I only want to open and match the folders in the list and not all folders. How is this possible?

[@WiseOwlTutorials]

Hi Jojo! You might find the Dir function useful here as you can use wildcard characters in the path docs.microsoft.com/en-gb/office/vba/Language/Reference/User-Interface-Help/dir-function For example, I have a folder at "C:\Wise Owl". If I enter this code: Debug.Print Dir("C:\Wise*", vbDirectory) It returns "Wise Owl" You need to be careful with this as it returns the first folder matching the pattern, so if I enter this: Debug.Print Dir("C:\Wi*", vbDirectory) It returns "Windows" You could list all the folders matching the pattern by calling the Dir function again without any arguments. The following code would list all the folders matching the pattern "C:\Wi*" Sub ListAllFoldersMatchingPattern() Dim FolderPath As String FolderPath = Dir("C:\Wi*", vbDirectory) Do Until FolderPath = "" Debug.Print FolderPath FolderPath = Dir Loop End Sub The result would be: Windows Wise Owl I hope that helps!

[@jojocarrillo]

@@WiseOwlTutorials thanks so much...

[@WiseOwlTutorials]

@@jojocarrillo No problem, hope you get it working!

[@wendychen4468]

Hi Wise Owl, thanks for your tutorials! They're clear and easy to follow. I have two questions I was hoping you could help me answer: 1. I am trying do a simple fso.copy folder (source:=folder path1, destination:=folder path2) where the folder paths are set as string. However, it is not copying the folder to the destination I want, do you know why that would be? 2. Also, is it possible to copy a FILE to a new FOLDER? I tried to write that into my coding, but I got a permission denied (error 70) error. Thanks!

[@semperfiparatus7736]

best ask on a forum like www.mrexcel.com

[@mohitmjoshi5172]

Thanks for uploading such video on KZbin. I have some questions kindly answer it Q1. why we have to define new instance for fso and some information about instance. I have not any idea about instance in coding . If you can answer it will help me a lot.

[@scotolivera8207]

Yes New Instance Got me Confused Also

[@darcylehtonen9539]

This is amazing stuff. Unfortunately I don't have the time to learn it all but I do need a little form designed to input customer info, save as records and print receipt. Can you help? Darcy

[@MySandClock]

does this work when files are stored in microsoft sharepoint workspace?

[@adityapatel7779]

Thanks, for creating the videos and helping us in learning. I need some help: I want to browse the folder and wants to get it's last modified date/ created date in excel vba. Please help me regarding the same. Thanks in advance. Regards, Aditya Patel

[@danilomikarolla9048]

Very good!! Congrats! Just a question, is there any function with fso to find files within a folder? Do you know how can I do that?

[@danilomikarolla9048]

***** Thank you!

[@kjanek17]

How does the data Scripting.File differ from Scripting.FileSystemObject? Is it just a "weaker" form of FileSystembject you used to dim a variable becouse it uses less memory? Or is it something else? Thank you for the tutorials, they are amazing.

[@kjanek17]

To be honest I don't understand when and why to use fil and fso. First you wrote fil.name but then when you wanted extensions of files you wrote fso.GetFileExtension and I don't get why.

[@WiseOwlTutorials]

Hi Chris! File and FileSystemObjects are two classes defined in the Scripting Runtime library. Classes are like blueprints for a particular type of object and describe the properties the object has and the methods the object can perform. The File class describes an individual file - you can use this class to reference and manipulate a single file object. A nice way to think of the FileSystemObject class is as an invisible version of a Windows Explorer window - you use it almost like an application for performing all the tasks you want to perform on files and folders. The classes in the Scripting Runtime library provides lots of different ways of doing the same thing (FileSystemObject.CopyFile vs. File.Copy as a simple example). Like learning any language you'll build your vocabulary with practice and experience. I hope that helps!

[@kjanek17]

@@WiseOwlTutorials That is a clear and sufficient explanation for me. Thank you WiseOwl for your service!

[@WiseOwlTutorials]

@@kjanek17 My pleasure!

[@Mitology]

Thanks for the video! If I share the file with that module and the user doesn't have the scripting unticked on his references - does the code still run?

[@WiseOwlTutorials]

Hi Pedro! References to other libraries are saved along with the VBA project in the Excel workbook. As long as your user has the same library installed on their computer the code will still run. I hope it helps!

[@Mitology]

@@WiseOwlTutorials thank you so much

[@ethansubbu1768]

Advice what to do pls

[@Tubedog8]

Thank you. Great Video.

[@kailash680]

If I use this method "Set MyFso = New FileSystemObject" The macros are not working on other computers as long they also have added the Microsoft scripting runtime Library?? Maybe I have to change it after programming is finished to: Set fso = CreateObject("Scripting.FileSystemObject") How do you handle this?