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. 😀
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.
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
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.
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
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 :)
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!
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!
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.
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.
Thank you for showing us several examples of using Objects (FileSystemObject, Auto Instancing, CreateObject) and the Pros and Cons thereof! Very helpful. 😁
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!
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!
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.
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.
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
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.
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.
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?
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).
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.
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?
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!
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.
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?
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!
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?
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!
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!!
How come you have taken the new variable "Startfolderpath" without assiging it any value or path string. I tried doing the same with a different name but its not even letting me run the sub routine. Please help.
Hi Pradeep, StartFolderPath is a parameter rather than a variable. Its value is set when you call the CopyExcelFiles subroutine from another procedure and pass an argument to this parameter. You can't run the CopyExcelFiles subroutine unless you pass a value to the StartFolderPath parameter, which is why you're unable to run it. This is described at around 39:22 in the video. I hope that helps!
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?
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?
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!
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.
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.
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
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.
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.
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!
Hi, I have understood the complete steps mentioned in this video. However there is one step which i couldn't relate, i.e, in the second subroutine of Copying Excel files. You have entered the line as "set OldFolder = fso.GetFolder(StartFolderPath)" This line how does it actually create link between OldFolderPath and StartFolderPath ? the reason i'm asking because how will it loop until we define "OldFolderPath" in CopyingExcelfiles subroutine ? Hope I'm able to put my question properly.
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?
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
Neither FSO nor Dir ensures that files are retrieved in the correct sequence. What is the proper way to list files in the correct order, such as: 1.jpg 2.jpg ... 9.jpg 10.jpg 11.jpg? what if, I want to stick to the Dir so that it can be used in MacOS as well?
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!
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?
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!
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
how can I copy file, but I need only to copy without pasting it? lets say I want to copy ActiveWorkbook then open outlook mail and hit Ctrl+V to paste as attachment. Is it possible?
+WiseOwlTutorials Great tutorial, but I have the same doubt from +Giorgi Tsomaia. Reason being: I create a PDF file from Excel and I would like to send it by email. However, the "sendmail" solution sends a new email while I need to reply an existing one and add the file (like a quote request being answered through pdf file). So having a solution that creates the pdf and copies it to the clipboard would solve my problem. Any ideia on that? Thanks again for this great tutorial.
Hello please can you kindly tell me how i can update Data source - Update links using VBA? I have 16 excel sheet links and doing this manually takes long time i want to be able to do this via VBA. Have you created a video on this?
Hi Kulvir! You can use the UpdateLink method to do this - the example code shown at the bottom of this page updates all links docs.microsoft.com/en-us/office/vba/api/excel.workbook.updatelink I hope it helps!
was wondering if there was a way to open a file using references to data within a cell of the active workbook? for example: cell A2 displays, "c:\Users ame.last\foldername\file 1.2.xlsx" using the data from that cell to open that file? any help is appreciated!
I've got a stupid question: the first time I created myself a template with the movies list I ended up with the modules from the previous lessons in order to save them in a safe place. Is this dangerous ? lol -MethodsForReferringToSheets -Modcopyingandmovingsheets -Moddeletingsheets -modinsertsheets -ModMovingBetweenSheets -ModRelativeSelect -ModSelectingCells -ModWorkingwithWorkbooks. Or should I keep the relevant Modules in each new Workbook I create ? BTW, I do love The Doctor and my favorite one is Tom Baker ! lol
Got it, it basically depends on the users ( my ) needs. Gravity was a good movie and never watched twilight but my kids did. The way I ran your demos is that I saved as Movielist2012_1 ,Movielist2012_2 and so on... YOU Sir are the best online. I've been using Excel for 20 years for calculations and reporting but in a manual fashion and it was time I automated my process... Other BBC gems I love are "Life on Mars" and "The Office... "
You are the awesome. You helped me a lot. I have just a query, could you please help me on this. Why "Filename has a value of "." first time and second time ".."? FileName = Dir(CurrDir & "*.*", vbDirectory)
***** I am sorry for my explanation. Just assume that my CurrDir= "C:\New Folder" FileName = Dir(CurrDir & "*.*", vbDirectory) If I run this in a loop then... Why "Filename has a value of "." first time and second time ".."?
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
Thanks for uploading such video on RUclips. 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.
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!
Hi. I have some conflicts with file names. Like I don't know the exact file name but how can I copy the file to a different location. For example I just know the number like 123456. But my exact file name will be 123-45-6 or in some cases 12-345-6. How can i move in this scenario? Do you have any idea about this?
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.
***** 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
***** 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
I'm having an issue, It's not looping through all the sub-folders. The only things i really changed was the folder path, I changed it to a user defined path and the extension name to look for. Could it have something to do with "armors = diafolder.selecteditems (1)" ? Heres the rest of the code... Option Explicit Dim fso As Scripting.FileSystemObject Dim newfolderpath As String Private Sub cmdarmors_Click() Dim armors As String Dim itemsfolder As String Dim diafolder As FileDialog Dim diafolder2 As FileDialog MsgBox " Choose Or Create A Folder On Your Desktop Named Items " Set diafolder2 = Application.FileDialog(msoFileDialogFolderPicker) diafolder2.AllowMultiSelect = True diafolder2.Show itemsfolder = diafolder2.SelectedItems(1) MsgBox " Find The Path To Your Starbound Unpacked Armors folder " Set diafolder = Application.FileDialog(msoFileDialogFolderPicker) diafolder.AllowMultiSelect = True diafolder.Show armors = diafolder.SelectedItems(1) newfolderpath = Environ("userprofile") & "\Desktop\items\armors" Set fso = New Scripting.FileSystemObject If fso.FolderExists(armors) Then If Not fso.FolderExists(itemsfolder) Then fso.CreateFolder itemsfolder End If If Not fso.FolderExists(newfolderpath) Then fso.CreateFolder newfolderpath End If Call copyitemsfiles(armors) End If
Set fso = Nothing End Sub Sub copyitemsfiles(startfolderpath As String) 'declaring some dims at module level Dim fil As Scripting.File Dim oldfolder As Scripting.Folder Dim subfol As Scripting.Folder Set oldfolder = fso.GetFolder(startfolderpath) For Each fil In oldfolder.Files If Left(fso.GetExtensionName(fil.path), 2) = "pn" Then fil.Copy newfolderpath & "\" & fil.name End If Next fil For Each subfol In oldfolder.SubFolders Call copyitemsfiles(subfol.path) Next subfol End Sub
well what it's doing is picking up the .png's in only one folder "backitems" why it picked a folder that began with the letter "B" I can't figure out yet. cause there is several that start with "A". It just stops after the "B" folder. Yeah, I'm making a mod, so far i've gotten a recipe maker done and a .pak file unpacker done. working on grabbing .png's and splitting the file name from the .png. I'm just stuck on this right now. Its pretty much the last thing to do to finish my mod. Figures it would be the last thing that gives me a problem. I wanted to learn VBA so I just picked a subject, starbound, where I could help other people and stay interested in learning code. Eventually i'll move on but for right now basic VBA is getting me through my project. Thanks for all your help. It's much appreciated.
***** I figured it out. It was looping through the folders but in each folder different items have the same name. just a different picture to go with the name. So it would pick up the first folder copy the files goto the next but the files were the same name so it skipped them. So what i need to have happen is instead of copying all the files to one file is somehow keep the same paths. so it copies the file and folder and keeps the same structure. Does that make sense? Is that possible?
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.
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?
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. 😀
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.
Thank you Rahul!
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
Just perfect. Spent a whole day researching and testing code then remembered you’ve already aced this training. Thanks again
I hope it doesn't feel like a day wasted! Thank you for watching!
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.
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
So in just 50 mins, you learnt how to work with FSO .......... recursive function call demonstration was outstanding.
Thank you Anil, I'm happy that you enjoyed it!
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 :)
Happy to hear that you found it useful! Thanks for watching and taking the time to comment!
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!
You're very welcome, I'm happy that you found it useful and thanks for watching!
deep enough to loose breathing........I must loop through this until I understand. Anyway you are the best Andrew
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!
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.
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.
Great tip, thanks for sharing!
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.
Simply the best VBA video tutorials on internet! Thank you very much. You are an awesome teacher!
Thank you for showing us several examples of using Objects (FileSystemObject, Auto Instancing, CreateObject) and the Pros and Cons thereof! Very helpful. 😁
You're very welcome! Thank you for watching and taking the time to leave a comment!
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!
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!
Happy to hear that it helped you, thanks for watching!
Excellent video. Clearly explained and moves at a good pace. Now I finally understand how FileSystemObject works!
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.
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.
So clear explanation is in your tutorials. Keep it up!
Thank you Mohammad!
Outstanding! I enjoy how you explain the logic as you step thru your code. My favorite Excel VBA site!
I just began to repeat theme with files,folders and these are just great videos for the topic, thank you, Cheers
That was a another great tutorial. Thank you, Andrew and WiseOwal for the best tutorials.
You're very welcome Sayyad, thank you for watching!
Thanks for all of the tutorials! I have learned so much. Can't wait for the next one
Happy to hear that Rahul, thank you for watching!
Thank you a lot, hope you are healthy, happy and rich! Your videos are just awesome!!!
Very informative and simple..I am using your videos to program in vba..
What if user cancels the choose window and we get a debug????
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
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.
Awesome job you have made with all these very helpful videos, thank you for your time and effort.
great explanation! this is how the code must be explained
Excellent / Commendable way of explaining the subroutine. keep it up. you are really great "Gyan Guru".
Top tutorial. Everything is very well explained. Keep up the good work!
You are a very talented teacher, thank you for sharing :)
And thank you for watching and for taking the time to leave a comment!
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.
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
Thanks Andrew The Wise Owl, this really helped me a lot.
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?
Hi Eric! We don't yet have a video on this so I'll add it to the To Do list!
@@WiseOwlTutorials spectacular!
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).
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.
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.
Yes, we need to ask them to check, or change our code to late binding with the use of the keyword Create object.
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?
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!
@@WiseOwlTutorials thanks so much...
@@jojocarrillo No problem, hope you get it working!
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.
I like your teaching method and easy follow you. Thank you for uploading all your tutorials.
Waiting for video for uploading by you.
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?
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!
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?
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!
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!!
Wow! Thanks a lot, I will try and will keep you informed of my results. Thank you thank you!
How come you have taken the new variable "Startfolderpath" without assiging it any value or path string.
I tried doing the same with a different name but its not even letting me run the sub routine. Please help.
Hi Pradeep, StartFolderPath is a parameter rather than a variable. Its value is set when you call the CopyExcelFiles subroutine from another procedure and pass an argument to this parameter. You can't run the CopyExcelFiles subroutine unless you pass a value to the StartFolderPath parameter, which is why you're unable to run it. This is described at around 39:22 in the video.
I hope that helps!
@@WiseOwlTutorials 🙂 I have got it now. Thank you so much for explaining.
No problem!
Hi Andrew,
While trying to copyfile i am getting error msg expexted end of statement
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?
Thanks for all of the tutorials! I have learned so much. Can't wait for the next one
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?
First of all, your videos are fantastic so thank you. Just wondering using fso has any benefits of the mkdir method in vba?
does this work when files are stored in microsoft sharepoint workspace?
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!
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. .
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.
Can someone explain in simple words what is an instance please......
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.
A MasterClass as usual... :)
Cheers Matt!
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
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.
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.
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!
@@WiseOwlTutorials That is a clear and sufficient explanation for me. Thank you WiseOwl for your service!
@@kjanek17 My pleasure!
Really useful...
Thanks!
Hi, I have understood the complete steps mentioned in this video. However there is one step which i couldn't relate, i.e, in the second subroutine of Copying Excel files. You have entered the line as
"set OldFolder = fso.GetFolder(StartFolderPath)" This line how does it actually create link between OldFolderPath and StartFolderPath ? the reason i'm asking because how will it loop until we define "OldFolderPath" in CopyingExcelfiles subroutine ?
Hope I'm able to put my question properly.
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?
Hi there! You might find this video useful ruclips.net/video/5pXbaVJIqec/видео.html
I hope it helps!
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
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?
really enjoyed watching this and hopefully i could use these techniques in my work place (still a long way to go)
Neither FSO nor Dir ensures that files are retrieved in the correct sequence. What is the proper way to list files in the correct order, such as:
1.jpg
2.jpg
...
9.jpg
10.jpg
11.jpg?
what if, I want to stick to the Dir so that it can be used in MacOS as well?
@ 27:05 :: Looping Over Files In A Folders
Excellent teaching!!! Thank you very much!
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!
best ask on a forum like www.mrexcel.com
great! thanks for the amazing classes
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?
***** Thank you!
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?
its Nice Video, can i get the sample file for easy reference
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?
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!
@@WiseOwlTutorials thank you so much
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
how can I copy file, but I need only to copy without pasting it? lets say I want to copy ActiveWorkbook then open outlook mail and hit Ctrl+V to paste as attachment. Is it possible?
Yea with outlook it is easy, but I am interested if there is a way to just copy, like select file and hit CTRL+C and Paste it manually
+WiseOwlTutorials Great tutorial, but I have the same doubt from +Giorgi Tsomaia.
Reason being: I create a PDF file from Excel and I would like to send it by email.
However, the "sendmail" solution sends a new email while I need to reply an existing one and add the file (like a quote request being answered through pdf file).
So having a solution that creates the pdf and copies it to the clipboard would solve my problem.
Any ideia on that?
Thanks again for this great tutorial.
Hello please can you kindly tell me how i can update Data source - Update links using VBA? I have 16 excel sheet links and doing this manually takes long time i want to be able to do this via VBA. Have you created a video on this?
Hi Kulvir! You can use the UpdateLink method to do this - the example code shown at the bottom of this page updates all links docs.microsoft.com/en-us/office/vba/api/excel.workbook.updatelink
I hope it helps!
was wondering if there was a way to open a file using references to data within a cell of the active workbook? for example: cell A2 displays, "c:\Users
ame.last\foldername\file 1.2.xlsx" using the data from that cell to open that file? any help is appreciated!
***** Thanks for the help re-watched earlier tutorials to help a bit as well. will be watching more of these excel VBAs very helpful.
I've got a stupid question: the first time I created myself a template with the movies list I ended up with the modules from the previous lessons in order to save them in a safe place. Is this dangerous ? lol -MethodsForReferringToSheets -Modcopyingandmovingsheets -Moddeletingsheets -modinsertsheets -ModMovingBetweenSheets -ModRelativeSelect -ModSelectingCells -ModWorkingwithWorkbooks. Or should I keep the relevant Modules in each new Workbook I create ? BTW, I do love The Doctor and my favorite one is Tom Baker ! lol
Because you always start with the same "Top Movies 2012.xlsm" with a different dressing !
ok, you can save each module as a *.bas should I want to before removing them. Sorry for asking stupid questions !
Got it, it basically depends on the users ( my ) needs. Gravity was a good movie and never watched twilight but my kids did. The way I ran your demos is that I saved as Movielist2012_1 ,Movielist2012_2 and so on... YOU Sir are the best online. I've been using Excel for 20 years for calculations and reporting but in a manual fashion and it was time I automated my process... Other BBC gems I love are "Life on Mars" and "The Office... "
how can i convert all excel files in a folder to pdf files
You are the awesome. You helped me a lot.
I have just a query, could you please help me on this. Why "Filename has a value of "." first time and second time ".."?
FileName = Dir(CurrDir & "*.*", vbDirectory)
*****
I am sorry for my explanation. Just assume that my CurrDir= "C:\New Folder"
FileName = Dir(CurrDir & "*.*", vbDirectory)
If I run this in a loop then...
Why "Filename has a value of "." first time and second time ".."?
This is great! Thank You for sharing smile 😃
You're very welcome, thank you for watching!
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
Thanks for uploading such video on RUclips.
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.
Yes New Instance Got me Confused Also
Thanks very much Wiseowl, you have helped me know vba
Thank you ,great video as usual :)... i never knew about the call stack thing.
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!
Hi! I'm sorry I don't know, I haven't experienced the problem you've described.
Hi. I have some conflicts with file names. Like I don't know the exact file name but how can I copy the file to a different location. For example I just know the number like 123456. But my exact file name will be 123-45-6 or in some cases 12-345-6. How can i move in this scenario? Do you have any idea about this?
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.
I thank you sir!
***** 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
***** 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
Thanks again! Its that that makes the difference here. I just have to keep practicing and watch these videos.
Advice what to do pls
you are the best Andrew sir.
Excellent video!
Just wonderful! Great job!!
I'm having an issue, It's not looping through all the sub-folders. The only things i really changed was the folder path, I changed it to a user defined path and the extension name to look for. Could it have something to do with
"armors = diafolder.selecteditems (1)" ? Heres the rest of the code...
Option Explicit
Dim fso As Scripting.FileSystemObject
Dim newfolderpath As String
Private Sub cmdarmors_Click()
Dim armors As String
Dim itemsfolder As String
Dim diafolder As FileDialog
Dim diafolder2 As FileDialog
MsgBox " Choose Or Create A Folder On Your Desktop Named Items "
Set diafolder2 = Application.FileDialog(msoFileDialogFolderPicker)
diafolder2.AllowMultiSelect = True
diafolder2.Show
itemsfolder = diafolder2.SelectedItems(1)
MsgBox " Find The Path To Your Starbound Unpacked Armors folder "
Set diafolder = Application.FileDialog(msoFileDialogFolderPicker)
diafolder.AllowMultiSelect = True
diafolder.Show
armors = diafolder.SelectedItems(1)
newfolderpath = Environ("userprofile") & "\Desktop\items\armors"
Set fso = New Scripting.FileSystemObject
If fso.FolderExists(armors) Then
If Not fso.FolderExists(itemsfolder) Then
fso.CreateFolder itemsfolder
End If
If Not fso.FolderExists(newfolderpath) Then
fso.CreateFolder newfolderpath
End If
Call copyitemsfiles(armors)
End If
Set fso = Nothing
End Sub
Sub copyitemsfiles(startfolderpath As String)
'declaring some dims at module level
Dim fil As Scripting.File
Dim oldfolder As Scripting.Folder
Dim subfol As Scripting.Folder
Set oldfolder = fso.GetFolder(startfolderpath)
For Each fil In oldfolder.Files
If Left(fso.GetExtensionName(fil.path), 2) = "pn" Then
fil.Copy newfolderpath & "\" & fil.name
End If
Next fil
For Each subfol In oldfolder.SubFolders
Call copyitemsfiles(subfol.path)
Next subfol
End Sub
well what it's doing is picking up the .png's in only one folder "backitems" why it picked a folder that began with the letter "B" I can't figure out yet. cause there is several that start with "A". It just stops after the "B" folder. Yeah, I'm making a mod, so far i've gotten a recipe maker done and a .pak file unpacker done. working on grabbing .png's and splitting the file name from the .png. I'm just stuck on this right now. Its pretty much the last thing to do to finish my mod. Figures it would be the last thing that gives me a problem. I wanted to learn VBA so I just picked a subject, starbound, where I could help other people and stay interested in learning code. Eventually i'll move on but for right now basic VBA is getting me through my project. Thanks for all your help. It's much appreciated.
***** I figured it out. It was looping through the folders but in each folder different items have the same name. just a different picture to go with the name. So it would pick up the first folder copy the files goto the next but the files were the same name so it skipped them. So what i need to have happen is instead of copying all the files to one file is somehow keep the same paths. so it copies the file and folder and keeps the same structure. Does that make sense? Is that possible?
the Best Teacher so farrrrrrrrrrrrr (:
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.
try this: msgbox environ("userprofile") & "\desktop"
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?