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

Рет қаралды 176,756

Күн бұрын

If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/shop/andrew-goul...
By Andrew Gould
www.wiseowl.co.uk - The Scripting Runtime Object Library allows you to easily write code in an Excel VBA project which can manipulate the file and folder structure of your computer. It's and incredibly useful, although potentially quite dangerous thing to be able to do and this video will show you how. You'll learn how to reference the Scripting Runtime Library, what a FileSystemObject is and how to use it and how to perform various methods such as create folders, copy and move files and even how to delete them. Towards the end of the video you'll see how to loop over a collection of files in a single folder and then, as an encore, how you can loop through the complete set of folders and subfolders from a given starting point.
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!

Пікірлер: 211

@MyAudioBookCompilation 3 жыл бұрын

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. 😀

@daves4026 13 күн бұрын

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

@WiseOwlTutorials 11 күн бұрын

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

@ashokkandukury2870 7 жыл бұрын

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

@zozozozo43 6 жыл бұрын

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

@AnilSharma-sg4qw 3 жыл бұрын

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

@WiseOwlTutorials 3 жыл бұрын

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

@inactiverahul 2 жыл бұрын

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 2 жыл бұрын

Thank you Rahul!

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

@KM-co5mx 2 жыл бұрын

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

@WiseOwlTutorials 2 жыл бұрын

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

@tommik1283 2 жыл бұрын

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 2 жыл бұрын

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

@DucLe-tc8ps 4 жыл бұрын

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 5 ай бұрын

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

@tigranhayrapetyan8357 2 жыл бұрын

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 2 жыл бұрын

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

@Yangar03 9 жыл бұрын

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.

@maurocastagnera8949 6 жыл бұрын

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

@twrbells 6 жыл бұрын

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

@danraats5840 4 жыл бұрын

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.

@sevilladas 10 жыл бұрын

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

@janezklun 6 жыл бұрын

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

@igorbandora1047 8 жыл бұрын

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!

@Tubedog8 6 жыл бұрын

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.

@wulp1191 5 жыл бұрын

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 5 жыл бұрын

Great tip, thanks for sharing!

@maurocastagnera8949 6 жыл бұрын

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!

@soodashishr 10 жыл бұрын

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

@tajsay 4 жыл бұрын

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

@WiseOwlTutorials 4 жыл бұрын

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

@piotrkuc8936 9 жыл бұрын

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

@rr2100 7 жыл бұрын

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

@donbogdala5428 10 жыл бұрын

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

@scottn9492 7 жыл бұрын

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.

@mdimran1a 2 жыл бұрын

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

@WiseOwlTutorials Жыл бұрын

Thank you Mohammad!

@shmred5705 7 жыл бұрын

great explanation! this is how the code must be explained

@amitgzb 5 жыл бұрын

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

@portapere 10 жыл бұрын

Just wonderful! Great job!!

@inactiverahul 2 жыл бұрын

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

@WiseOwlTutorials 2 жыл бұрын

Happy to hear that Rahul, thank you for watching!

@franciscobaptista7139 7 жыл бұрын

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

@jbsepulveda27 9 жыл бұрын

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

@ventjemazzel8822 6 жыл бұрын

Excellent teaching!!! Thank you very much!

@rjm1540 7 жыл бұрын

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

@bin7562 9 жыл бұрын

You are the best! Thank you so much!

@UmamaheshwarAskula 10 жыл бұрын

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

@mehmetcanbulat8361 10 жыл бұрын

the Best Teacher so farrrrrrrrrrrrr (:

@Amit_the_Analyst 7 жыл бұрын

you are the best Andrew sir.

@amandafernandesramospasson5862 4 жыл бұрын

great! thanks for the amazing classes

@abubakaroaliyu1476 7 жыл бұрын

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

@chizamanda5149 6 жыл бұрын

Thanks very much Wiseowl, you have helped me know vba

@tugaric 4 жыл бұрын

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

@WiseOwlTutorials 4 жыл бұрын

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

@tarekyeh6245 7 жыл бұрын

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

@sribalajispaceship 4 жыл бұрын

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

@francist3979 6 жыл бұрын

once again, fantastic video!

@Tubedog8 10 жыл бұрын

Thank you. Great Video.

@ravinderavula2025 5 жыл бұрын

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

@torque6389 6 жыл бұрын

Excellent video!

@ethofmeyr 4 жыл бұрын

Excellent tutorial!!!

@alesloom 6 жыл бұрын

thanks , your vids helped in my project .

@91kunalm 8 жыл бұрын

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).

@0807elephant 7 жыл бұрын

Many thks for the video. They are the best!

@zouhairwarii3079 7 жыл бұрын

Thanks a lot, you do excellent videos

@mkanalysis 8 жыл бұрын

THANK YOUUU!!!! that was awesome

@KM-co5mx 2 жыл бұрын

This is great! Thank You for sharing smile 😃

@WiseOwlTutorials 2 жыл бұрын

You're very welcome, thank you for watching!

@Bradza101plays 7 жыл бұрын

think im in love. so awsome at your videos

@mattvba25 2 жыл бұрын

A MasterClass as usual... :)

@WiseOwlTutorials 2 жыл бұрын

Cheers Matt!

@b.petrushchak 9 жыл бұрын

Thank you!

@reinaldodelacruz8788 8 жыл бұрын

Excellente !!!!! Gracias...

@sumantkalamkar1190 5 жыл бұрын

Great video man..!

@SyedKollol 10 жыл бұрын

excellent!

@esdkingston 8 жыл бұрын

I like this.

@vireshkolagimath4264 9 жыл бұрын

thank you so much:)

@kjesu2715 2 жыл бұрын

Really useful...

@WiseOwlTutorials 2 жыл бұрын

Thanks!

@natraj6716 8 жыл бұрын

Hats Off :)

@enricograndi1112 7 жыл бұрын

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?

@lonelysoul121 5 жыл бұрын

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

@zacharyplotzker9155 9 жыл бұрын

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.

@andrewhamilton7475 8 жыл бұрын

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

@rkelectronicservicesprivat306 2 жыл бұрын

Thanks

@WiseOwlTutorials 2 жыл бұрын

You're welcome!

@chrissampson6861 7 жыл бұрын

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

@lucabottini9302 3 жыл бұрын

Great!!!!!

@WiseOwlTutorials 3 жыл бұрын

Thanks Luca!

@ethansubbu1768 4 жыл бұрын

Source:= complie error expected: expression

@DennisYeadon 6 жыл бұрын

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?

@johnabram4159 3 жыл бұрын

@ 27:05 :: Looping Over Files In A Folders

@MySandClock 7 жыл бұрын

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

@Meolimo 9 жыл бұрын

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.

@iamsopure1043 9 жыл бұрын

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.

@deeputhomas9887 4 жыл бұрын

Note that Windows has a default limit on the path length. It is possible to hit this limit while recursing through the folders when its nested deep. Refer to this link to know more. docs.microsoft.com/en-us/windows/win32/fileio/naming-a-file?redirectedfrom=MSDN#short_vs._long_names

@WiseOwlTutorials 4 жыл бұрын

That's a good tip, thank you for sharing!

@badassack 10 жыл бұрын

hey 40:00 in to the video, you called on a different sub. Does that sub have to be on the same form or module or can it be on another form or module?

@juandavidb 9 жыл бұрын

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 9 жыл бұрын

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

@danilomikarolla9048 9 жыл бұрын

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 9 жыл бұрын

***** Thank you!

@kailash680 5 жыл бұрын

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?

@rajeshpuj 2 жыл бұрын

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.

@TheLarigo 9 жыл бұрын

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

@jojocarrillo 4 жыл бұрын

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.

@GopalarathnamS 8 жыл бұрын

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 4 жыл бұрын

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

@mohitmjoshi5172 6 жыл бұрын

Thanks for uploading such video on KZfaq. 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 5 жыл бұрын

Yes New Instance Got me Confused Also

@amitbodhwani9119 7 жыл бұрын

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!

@edtardaguila3599 4 жыл бұрын

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?

@musashi1763 2 жыл бұрын

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

@adityapatel7779 6 жыл бұрын

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

@wendychen4468 8 жыл бұрын

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 7 жыл бұрын

best ask on a forum like www.mrexcel.com

@Karkhash 2 жыл бұрын

Hi! Does this object helps to open OneDrive files via VBA? Because when I execute a macro which opens Excel files from a OneDrive file, the macro close the application (using Dir and Workbooks.Open) Thanks!

@WiseOwlTutorials 2 жыл бұрын

Hi! I'm sorry I don't know, I haven't experienced the problem you've described.

@undrawingend 2 жыл бұрын

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 2 жыл бұрын

Hi there! You might find this video useful kzfaq.info/get/bejne/a9aIlcSHr6_VlZc.html I hope it helps!

@EricHartwigConsulting 2 жыл бұрын

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 2 жыл бұрын

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

@EricHartwigConsulting 2 жыл бұрын

@@WiseOwlTutorials spectacular!

@grantdawson9786 8 жыл бұрын

Tutorials are great. Can anyone tell me how to copy the location of the desktop from the Title Bar in Windows 10? other locations are easy but the Desktop does not have a text string.

@semperfiparatus7736 7 жыл бұрын

try this: msgbox environ("userprofile") & "\desktop"