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

Подписаться 208 тыс.

Просмотров 177 тыс.

50% 1

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!

Опубликовано:

31 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 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 Месяц назад

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!

@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

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

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

@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 6 месяцев назад

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

@sevilladas 10 лет назад

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

@maurocastagnera8949 6 лет назад

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

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

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

@twrbells 6 лет назад

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

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

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

@janezklun 6 лет назад

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

@rr2100 7 лет назад

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

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

@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

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

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

@donbogdala5428 10 лет назад

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

@piotrkuc8936 9 лет назад

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

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

@amitgzb 5 лет назад

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

@franciscobaptista7139 7 лет назад

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

@mdimran1a 2 года назад

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

@WiseOwlTutorials 2 года назад

Thank you Mohammad!

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

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

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

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

@jbsepulveda27 9 лет назад

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

@shmred5705 7 лет назад

great explanation! this is how the code must be explained

@rjm1540 7 лет назад

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

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

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

@ventjemazzel8822 6 лет назад

Excellent teaching!!! Thank you very much!

@sribalajispaceship 4 года назад

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

@mehmetcanbulat8361 10 лет назад

the Best Teacher so farrrrrrrrrrrrr (:

@portapere 10 лет назад

Just wonderful! Great job!!

@chizamanda5149 6 лет назад

Thanks very much Wiseowl, you have helped me know vba

@bin7562 9 лет назад

You are the best! Thank you so much!

@Amit_the_Analyst 7 лет назад

you are the best Andrew sir.

@amandafernandesramospasson5862 4 года назад

great! thanks for the amazing classes

@francist3979 6 лет назад

once again, fantastic video!

@ravinderavula2025 5 лет назад

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

@alesloom 6 лет назад

thanks , your vids helped in my project .

@91kunalm 9 лет назад

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

@Bradza101plays 7 лет назад

think im in love. so awsome at your videos

@0807elephant 7 лет назад

Many thks for the video. They are the best!

@zouhairwarii3079 7 лет назад

Thanks a lot, you do excellent videos

@torque6389 6 лет назад

Excellent video!

@ethofmeyr 4 года назад

Excellent tutorial!!!

@Tubedog8 10 лет назад

Thank you. Great Video.

@mattvba25 2 года назад

A MasterClass as usual... :)

@WiseOwlTutorials 2 года назад

Cheers Matt!

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

@sumantkalamkar1190 5 лет назад

Great video man..!

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

@b.petrushchak 9 лет назад

Thank you!

@SyedKollol 10 лет назад

excellent!

@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

@andrewhamilton7475 9 лет назад

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

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

@reinaldodelacruz8788 8 лет назад

Excellente !!!!! Gracias...

@esdkingston 8 лет назад

I like this.

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

@vireshkolagimath4264 9 лет назад

thank you so much:)

@lonelysoul121 5 лет назад

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

@MySandClock 7 лет назад

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

@natraj6716 8 лет назад

Hats Off :)

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

@kjesu2715 3 года назад

Really useful...

@WiseOwlTutorials 3 года назад

Thanks!

@johnabram4159 3 года назад

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

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

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

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

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

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

@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

@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

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

@lucabottini9302 3 года назад

Great!!!!!

@WiseOwlTutorials 3 года назад

Thanks Luca!

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

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

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

@ethansubbu1768 4 года назад

Source:= complie error expected: expression

@vannurswamy9000 6 лет назад

how can i convert all excel files in a folder to pdf files

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

@manishagrawal417 8 лет назад

Hi, I am working on a MAC Yosemite excel 2011. I am unable to find how to write a code to access a file path. can you help please?

@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

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

@rkelectronicservicesprivat306 2 года назад

Thanks

@WiseOwlTutorials 2 года назад

You're welcome!

@ethansubbu1768 4 года назад

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

@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

@darcylehtonen9539 9 лет назад

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

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

@mohitmjoshi5172 6 лет назад

Thanks for uploading such video on RU-vid. 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.