How to Use the File System Object in VBA

Рет қаралды 8,496

Күн бұрын

The file system object library makes working with folders and files in VBA a breeze. In this video, we will explore how to use this object to parse file paths, loop through folders and files, and how to get different properties about the objects in folders.
Video Resources:
--------------------------------------------------
Resource: GitHub File
Link: github.com/areed1192/sigma_co...
Resources:
--------------------------------------------------
Facebook Page: / codingsigma
Facebook Group: / sigmacoding
GitHub Sigma Coding: github.com/areed1192/sigma_co...
Support Sigma Coding:
--------------------------------------------------
Patreon: / sigmacoding
Amazon Associates: amzn.to/3bsTI5P **
Related Topics:
--------------------------------------------------
Title: How To Programmatically Add Library References Using VBA
Link: • How To Programmaticall...
Title: How to Work With the Command Bar Object in VBA
Link: • How to Work With the C...
Title: How to Work With the Command Bar Control Object in VBA
Link: • How to Work With the C...
Title: How to Export Macros Using VBA
Link: • How to Export Macros U...
Title: How To Run A Python Script Using Excel VBA
Link: • How To Run A Python Sc...
Title: How to Use the Shell Automation Library in VBA | Part 1
Link: • How to Use the Shell A...
Title: How To Control The Visual Basic Editor With VBA
Link: • How To Control The Vis...
Title: Web Scraping With VBA | Part One
Link: • Web Scraping With VBA ...
Title: Web Scraping With VBA | Part Three
Link: • Web Scraping With VBA ...
Title: Web Scraping With VBA | Part Two
Link: • Web Scraping With VBA ...
Title: Web Scraping With VBA | Scraping Data Tables
Link: • Web Scraping With VBA ...
Title: Making API Requests in VBA | JSON
Link: • Making API Requests in...
Title: Making API Requests in VBA | XML
Link: • Making API Requests in...
Title: How To Pull Data From Access Using VBA
Link: • How To Pull Data From ...
Title: Importing Text Files In VBA
Link: • Importing Text Files I...
Title: How to Use the File System Object in VBA
Link: • How to Use the File Sy...
Title: How to Build Windows Forms Applications in Python | Pt. 2
Link: • How to Schedule a Macr...
**Amazon Associates Disclosure:
--------------------------------------------------
I am a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.com. One of the ways I support the channel is by using Amazon Associates to earn fees on purchases you make. That means when you use the link above, it will track any purchases made from that link and give a small portion of it to the Sigma Coding. I love this approach because it allows you to do what you're already doing (shopping) but also helps support the channels you care about. Also, it makes it where I can invest that revenue to help improve and grow the channel.
Tags:
--------------------------------------------------
#Excel #VBA #FileSystemObject

Пікірлер: 24

@kovacsjanos2653 3 жыл бұрын

This was amazingly helpful. Loved the video. I have been using the file system object for some time by just blindly copy pasting sample codes off of stackoverflow. This really helped me understand what I was doing. Thank you

@ignacioa3698 5 жыл бұрын

Great stuff! Thank you for that, SigmaCoding! Got your prior mesg, but noticed this video before being able to respond earlier. Did not know there was a specific library for doing those files/folders iterations. Thankkk youu! Now additionally I should be able to leverage this same library iteration commands, as you outlined above, and build out and insert codes to also: (1) stop by each folder or file (or the designated folder I choose), (2) select the excel workbook, (3) open that file or excel workbook, (4) copy and paste whatever object type from the workbook sheet or sheet(s), (5) paste either a chart or table into whatever new excel workbook or Word doc, for example,I want to paste it into, (6) close the excel workbook I copied the info from, (7)continue the iteration to he next designated folder, (8) open up the next and designated excel workbook in the iteration, for example. And repeat the process again for all files or designated files and excel workbooks I choose. We should be able to insert those additional code instructions within this library iteration commands to achieve that?

@SigmaCoding 5 жыл бұрын

Yes, so your approach would work in the outline above, basically what you could do is use the GetFileName method to grab the workbook name, open it, and then run the code you normally would in that workbook. The only word of caution I would have is just to be careful with things like names, people like to change them and all that jazz. That means you could be spending a lot of time just verifying the names of workbooks, folders and stuff like that. However, you could also leverage the FileExsit and FolderExist method to be a check before you open it.

@ignacioa3698 5 жыл бұрын

@@SigmaCoding Perfect. Thank youuu!! Will most certainly do a mock run of a hypothetical scenario. And thank you again for your reply update! Much appreciated.

@MalinaC 4 жыл бұрын

Awesome! Thanks for sharing!

@danteencinas7020 2 жыл бұрын

Fantastic.... thank you!!!

@IrfanKhan-wv8rh 2 жыл бұрын

amazing explanation..!!

@scotolivera8207 4 жыл бұрын

Thanks a lot for the video, excellent content. It will be nice if you do a video on building an app from scratch using VBA

@SigmaCoding 4 жыл бұрын

Are you talking a UserForm application?

@scotolivera8207 4 жыл бұрын

@@SigmaCoding thanks for replying, yes

@SigmaCoding 4 жыл бұрын

Good news, I've already created a series that covers this topic. The series focuses on how to create a User Form application using Excel VBA. In essence, it's a simple "export manager" that will take an Excel object and export it to either PowerPoint or Word. Here is the link to the first video in the series: kzfaq.info/get/bejne/iLKTnNyUncCqlo0.html I will be adding another series related to User Form applications that will cover how to import data from the SQL database into the User Form.

@stevennye5075 3 жыл бұрын

well done!

@wolfgangmetcalf9557 3 жыл бұрын

I have my code written out the same way you do but I get a compile error: method or data member not found. When I looked this up other people said i didnt fully qualify my objects. EDIT: it works now! I had set both variables Fils and Fil as Files. Instead Fil is supposed to be set as File without the letter S at the end.

@kabukijoe99 2 жыл бұрын

thank you

@ignacioa3698 5 жыл бұрын

So towards the end, you also " built a new path", but under what scenarios would you use this option? When would you do something like that?

@SigmaCoding 5 жыл бұрын

To be honest, it really depends. A lot of times, we don't necessarily have a file path but we have all the components to build a path so it's just easier to pass through the built-in method rather than do all sorts of string formatting that could be prone to errors.

@TravisDrLee Жыл бұрын

I have a file that I get FTP'd to me everyday from the previous day. How can I check to make sure that this file is from the day previous and not from a couple of days ago. I need to make sure that this file is from the previous day and not from a later day which would indicate that the FTP process has stopped and then I can inform someone that I need the latest file?

@Sid1991kap 3 жыл бұрын

it says "runtime error 70 permission denied" when i move file between different drives

@dildokafir5058 5 жыл бұрын

Hello.. i do not see shell command using vba in your vba programming playlists . Are you going to create a video on shell command in no time.??

@SigmaCoding 5 жыл бұрын

Yes, I will be recording a video on that topic very soon.

@jasonpopek160 3 жыл бұрын

I am using VBA with SeleniumBasic and trying to run an automated occurrence of Firefox. Due to my companies policies, location, and laws for the governing area, we cannot use Chrome to log into our systems because it is a US company, as Chrome is very easy to use this way. Apparently in other programming languages, e.g. Java or C#, we can use the Firefox automation using the GeckoDriver.exe. We would achieve this by using some version of the following code: System.setProperty("webdriver.gecko.driver", "C:/path/to/geckodriver.exe"); Question: Is it possible using your method in order to run the Automated Firefox with newer versions of Firefox using the gecko.exe file path? If you can answer this question, I am sure that many people would be very appreciative as this is a very common question on the internet these days!

@ericbrenner690 3 жыл бұрын

I think you looked at the .NET DriveTypes and not the VBA Reference. 2 says its fixed, not removeable. docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/drivetype-property

@SigmaCoding 3 жыл бұрын

Thanks for sharing this :)