MkDir
By Richard Rost
36 days ago
Create File Folders with VBA MkDir Statement In this video, we discuss how to use the MkDir command in Microsoft Access with VBA to automatically create folders for each customer, allowing invoice PDFs to be organized by customer rather than stored in a single directory. I show how to add VBA code that will generate the necessary folder structure when exporting invoices, handle basic error situations like folders already existing, and structure the exported PDF files by customer. We will also briefly mention possible improvements and related topics covered in other videos, with more advanced handling reserved for the extended cut. Colin from Charleston, South Carolina (a Platinum Member) asks: In my Access database, whenever an invoice is created we automatically save a copy as a PDF using the tip from your Export to PDF video so our sales and shipping teams can access the invoices without opening the database. Right now all of the PDFs go into a single invoice folder, but after years of use that folder now contains tens of thousands of files and it's getting difficult to manage. I would like to save invoices into separate folders for each customer instead, but I don't know how to automatically create those customer folders from Access if they don't already exist. How can I do this? MembersIn the extended cut, we will check if folders exist using the Dir command, add more robust error trapping for cases like missing parent folders, and show how to clean up and move existing files into the proper subfolders based on customer IDs. I will also discuss the moral implications of Tribble Jerky as a lighthearted bonus. We will learn how to implement these improvements to better manage and organize your files programmatically in Access. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesRecommended Courses
Keywords TechHelp Access, VBA MkDir, create customer folders, export invoice PDF, organize invoice files, On Error Resume Next, CurrentProject.Path, DoCmd.OutputTo, database file management, customer subfolders, error handling, Dir command, recursive Make Directory, folder exists check
Subscribe to MkDir
Get notifications when this page is updated
Intro In this video, we discuss how to use the MkDir command in Microsoft Access with VBA to automatically create folders for each customer, allowing invoice PDFs to be organized by customer rather than stored in a single directory. I show how to add VBA code that will generate the necessary folder structure when exporting invoices, handle basic error situations like folders already existing, and structure the exported PDF files by customer. We will also briefly mention possible improvements and related topics covered in other videos, with more advanced handling reserved for the extended cut.Transcript Ever open a folder and find 10,000 invoice PDFs dumped into one giant mess?
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
Today, we are going to talk about creating folders automatically in Microsoft Access using VBA and the MkDir command, specifically how you can have Access create a folder for each customer so your invoices or other files do not all pile up in one big directory.
Today's question comes from Colin in Charleston, South Carolina, one of my Platinum members.
Colin says: In my Access database, whenever an invoice is created, we automatically save a copy as a PDF using the tip from your Export to PDF video so our sales and shipping teams can access the invoices without opening the database. Right now, all of the PDFs go into a single invoice folder, but after years of use that folder now contains tens of thousands of files and it is getting difficult to manage.
I would like to save invoices into a separate folder for each customer instead, but I do not know how to automatically create those customer folders from Access if they do not already exist. How can I do this?
Well, we can create file folders using the MkDir command, which stands for Make Directory, and it is a simple statement. Let me show you how to use it.
Before we get started though, this is going to involve a little VBA, so if you have never done any VBA programming before, go watch this video. It is about 20 minutes long and it will teach you everything you need to know to get started.
And of course, go watch this video. This is the video that Colin is referencing, where I show you how to export reports to a PDF with a single click.
We are going to be using my TechHelp free template, the invoice that I build in here. If you want to see how this is built, go watch this video.
Go watch this video on error handling. We just need a little bit of basic error handling because if for any reason, Access is unable to create that folder, we do not want it to throw weird errors, so we are going to handle those ourselves. Go watch this video too.
These are all free videos. They are on my website, they are on my YouTube channel. Go watch them and then come back.
All right, here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want to.
In here, I have customers, and customers have orders, and in here we can generate an invoice.
Let us make this guy save as a PDF instead of displaying it here. In fact, we can actually make another button to do both. Here is our invoice if you want to print it. I am going to copy and paste that button; we will stick it over here.
We will change the caption to PDF and let us open up the button's properties by double-clicking on that border. We will make the name PDFButton like that - PDF_BTN.
All right, let us go into the events, go into its OnClick event, or if you like, you can right-click and go to Build Event. That is how I usually get in there.
Here I am in the VB editor dark mode and let us come in here.
The first thing I always do whenever I am making a button that opens up a report or does anything with the data in that report is a Me.Refresh or at least you can say Me.Dirty = False. Me.Refresh is a little bit different in that it refreshes the data from the table. Me.Dirty = False just commits the data in the form to the table to make sure that they are both on the same page.
Both will work in this instance. We want to make sure when we open up that invoice report, whether we are exporting it or displaying it, it has current data. If someone comes in here and makes a change and they hit the button without saving that change, then the invoice is going to have wrong data. I talk about that in the invoicing video.
Now that we have done that, we can export this invoice. We are going to use DoCmd.OutputTo. We are outputting a report. It is acOutputReport, comma, which report - it is our order invoice, the same thing from above right up there. Comma, what is the format?
Again, there is no Intellisense for this, which I do not understand why not, Access team. Write in acFormatPDF, comma, let us continue to the next line.
Now here is where we specify the file name, the output file name. Just for now, let us put all the invoices in the database folder. It is going to be CurrentProject.Path. That is wherever your database file is stored. If it is in Z:\Database or if it is on your desktop or wherever, it is going to be in that same folder.
Then let us tack on backslash, let us go "Order-" and then the OrderID and then .PDF on the end. The next one, AutoStart, do you want to see it? In other words, for a PDF, it is going to launch your PDF viewer, which in my case is my web browser, but that is fine.
Yeah, let us take a look at it. That prevents you from having to do a separate step. If all you want to do is just export it, you do not care to see it, then set that to false and you do not have to look at it.
All right, and then of course, when I am done, I like to throw a beep in there so I know that it did something.
Okay, let us save it. Debug, compile once in a while. Close it, save it, close it, open it and hit the PDF button.
You ready? There we go. It did its job and then it opened my web browser. All right, let us close that down.
Let us go take a peek. Now, where is my folder? I put it in the folder. Here is the folder that we are in. There we go. There is order 3 and it is in the TechHelp make the folder.
Now, what Colin is saying is they have been doing this for a few years now and he has got thousands of these in here.
We want to try to put these in an invoices folder under here. Or wherever you want to put it, you might want to have it on a separate share on your server or wherever you want to have it on your network. It does not matter; I just put it in the database folder.
What we can do is create an Invoices folder in here and then under this Invoices folder, make a separate folder for each customer based on their CustomerID.
Now, I am going to have Access make that Invoices folder. So, we are going to delete that; we are not going to assume it exists.
The only folder we are going to assume exists is the parent folder, whatever folder you want to start at the top - whether it is your share folder or your database folder, whatever.
Let us go back to the database, let us go back to our code, and let us come in here. I am going to create the folders where I want to put this stuff before I actually export it.
The first thing I am going to do is create that Invoices folder. MkDir is the command and then CurrentProject.Path is our database folder or wherever else you want to put it. This could be Z:\Database or C:\MyShare, whatever. Wherever you want to put it, that is fine. Then "\Invoices" just like that. We are going to create that folder.
Now, the first time it runs, it is going to create that folder. The second time you run this, it is going to cause an error because the folder already exists. So, we are going to intercept that error. We know it is possible. We know it is going to be coming. I am just going to throw an "On Error Resume Next" right here.
Then when it is done, "On Error GoTo 0" turns error handling back off again.
Only put that around stuff you know is likely to cause an error, but you do not care about it.
Now, is this the best solution? No, you can use the Dir command that I covered yesterday to see if that folder exists first, but honestly, 99.9 percent of the time the only time you are going to get an error with this is if you put in an invalid parent folder or this guy already exists, which is expected.
If you want to put better error handling on here with an error handler down at the bottom of the sub, that is fine. Again, this is a short 10-minute video, so I am not going to go over all of those instances. There is a lot more involved, but this is a simple solution to get your work done today.
Not every house we build is going to be a 13-room mansion.
After you create that, then we need to create a folder under that folder with the CustomerID in it.
Now, we know what the CustomerID is; it is right here, and this guy here is called CustomerCombo, so I am going to use that.
Next, I am going to say MkDir. I am going to copy this whole thing - MkDir that Invoices folder backslash CustomerCombo - and it will make that folder too. So now we have got an Invoices folder, and then under that we have got a CustomerCombo folder.
Again, the second time it runs, it might throw an error, but that is okay. Ignore it. We know the folder probably already exists after the first time you run it.
While I have your attention, go smash that like button. And if you are not subscribed, subscribe. It helps me out. It helps the channel. It helps me make more free videos.
All right. So now that we have got these folders created, now we can output that file to that folder.
So just instead of CurrentProject.Path, we are going to put that whole thing in here. And if you do not want to have the same code everywhere, you can put it in a variable if you want to.
You can come up here and say "Dim fp for fullpath as String," and then in here, see, here is what I would do. I would say fp = that. MkDir fp so you have made it. So down here, we will just say fp and "Order" OrderID ".PDF", so this whole thing is going to go right in there.
I try not to have this complicated stuff in multiple places. You can even start off with fp and then add to it. There are a lot, a million things you could do. Again, we are keeping it simple.
All right. Let us give it a shot. Debug, Compile. Close it. Close it. Save it. Open it. Open it. I always close and reopen forms if I have worked on their VBA. That is just a thing I always do.
All right. Ready? Hit it. And it is. It opened up on my browser.
All right. Let us close that. Let us check the folder. Where is the folder? Oh, there we go. We got Invoices and then a 1, because I am CustomerID 1, and there is my invoice. That is pretty cool.
Let us try another one. Let us go back here and go to a different customer. Let us go to Jimmy Kirk. He has got an order. PDF. Found. There it is. It is opening up. It is in a different window. There it is. Just approved it. It is working.
All right. Let us go check the folder: Invoices. Oh, there is a 2 folder, and there is his order 2. Pretty good.
One more if you do not believe me. Let us go. Let us make another. Does Jimmy Kirk have another order in here? Let us make Jimmy Kirk another order. So today is date. Stuff. And he is buying... He is buying Tribble Jerky. I do not want hate mail. Let us make it an invoice. There it is. It is order 17.
Let us go check the folder, and there is order 17 in folder 2 for Jimmy Kirk.
You can put the customer's name or whatever on here to make it easy to find, but I am assuming your salespeople know they are looking for CustomerID 2 and then there is order 17. If not, you can make these as descriptive as you want. You can do whatever you want with it. It is your code. Do whatever you want. I am just showing you how to put the Legos together. Take the Legos now and make your own little toy.
Now, in the extended cut for the members, we are going to check to see if that folder exists properly using the Dir command. We will trap any other unnecessary errors because sometimes it could throw another error if your parent folder does not exist, for example.
And then, for Colin, we are going to clean up those existing files. He has tens of thousands of files in there. As long as they are all named the same way, like we did "Order-" and then an order number, we can take those, look up who the customer is, and put them all in proper subfolders. We will do that in the extended cut.
Also, if you want to learn more about the MkDir command and other file-folder commands and stuff like that, I cover that in Access Developer 31. I will put a link to that down below. We go through all kinds of stuff, including navigating files and folders, the file picker, Dir, GetAttributes, all that stuff: file copy, checking to see if a file or folder exists. You name it. It is all kinds of cool stuff.
I just released Access Developer 53 where we have a recursive Make Directory command. That is so you can give it a full path like that - personal desktop, document, whatever - and it will make all of those at one shot. You do not have to worry about making each one of those. That is in Access Developer 53.
Also, in the extended cut, we are going to talk about the moral implications of Tribble Jerky. I mean, it says it is made from 100 percent authentic Tribble meat and that is just not right. To be honest, I asked the AI to make me a picture of Captain Kirk eating Tribble Jerky, and I am amazed at the quality it comes up with nowadays. So again, no hate mail, but you can post your funny comments down below.
Today we learned how to use the MkDir command in Microsoft Access to automatically create folders so your files stay organized instead of piling up in one giant directory.
Post a comment down below and let me know how you liked today's video and how you plan to use this in your database.
That is going to be your TechHelp video for today, brought to you by Access Learning Zone. I hope you learned something. Live long and prosper, my friends. I'll see you next time, and members, I will see you in the extended cut.Quiz Q1. What is the primary issue being addressed in the video?
A. How to print invoices directly from Access
B. Managing thousands of invoice PDF files dumped into a single folder
C. How to send email from Access
D. Backing up Access databases automatically
Q2. What command in VBA is used to create folders automatically in Access?
A. Dir
B. ChDir
C. MkDir
D. CreateFolder
Q3. What is the main benefit of creating a separate folder for each customer?
A. To improve the security of invoices
B. To manage and organize invoices more efficiently
C. To reduce disk space usage
D. To speed up Access database queries
Q4. If the folder already exists when MkDir is called, what happens by default?
A. The folder is overwritten
B. An error is generated
C. The folder is automatically renamed
D. MkDir skips the folder
Q5. How does the video handle the error that occurs if MkDir tries to create a folder that already exists?
A. Ignores the error by using On Error Resume Next
B. Deletes and recreates the folder
C. Displays a message box to the user
D. Cancels the operation
Q6. What is the purpose of the DoCmd.OutputTo method in this solution?
A. To delete old invoices
B. To export a report as a PDF file
C. To import data from Excel
D. To send emails to customers
Q7. How is the file path for saving the invoices constructed?
A. By combining a hard-coded path and a random number
B. By appending the order ID to the file name in the database folder
C. By requesting user input for each file name
D. By automatically choosing a network share
Q8. Why is Me.Refresh or Me.Dirty = False used before exporting a report?
A. To speed up database performance
B. To ensure the report shows the most up-to-date data
C. To close the database form
D. To delete unsaved data
Q9. What is suggested as a best practice for specifying the location of exported PDFs?
A. Always use the root of the C drive
B. Store the path in a variable to avoid repeating code
C. Hard-code the file path multiple times in your code
D. Use a temporary folder only
Q10. In the example, what identifies each customer's folder?
A. Phone number
B. CustomerID
C. Name and date of birth
D. Order total amount
Q11. What command is mentioned in the extended cut for verifying folder existence before creating it?
A. FileExists
B. Dir
C. ChDir
D. ExistsFolder
Q12. What does the video recommend regarding error handling for MkDir?
A. No error handling is needed at all
B. Use On Error Resume Next only around code likely to cause known errors
C. Place On Error Resume Next at the top of all modules
D. Let Access handle all errors automatically
Q13. Which of the following is a benefit of organizing invoices into subfolders per customer as described?
A. It enables automatic emailing of invoices
B. It prevents the Access database from becoming corrupt
C. It makes finding specific invoices much easier
D. It increases the maximum number of reports Access can export
Answers: 1-B; 2-C; 3-B; 4-B; 5-A; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-B; 13-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on keeping your files organized in Microsoft Access by having the database create folders automatically using VBA and the MkDir command. If you've ever found yourself wading through thousands of PDF invoices dumped into a single folder, this lesson will help you keep things much more manageable. Instead of letting everything pile up in one place, you can have Access create a separate folder for each customer and put their invoices neatly into those folders.
This topic was inspired by a question from a viewer who wanted to know how to automatically create customer folders in Access for their PDF invoices. After years of saving every invoice to a single directory, they found it difficult to quickly find invoices and needed a solution to keep things organized moving forward.
To accomplish this, I'll show you how to use the MkDir command in Access VBA, which stands for "Make Directory." This command is a simple way to create new folders, and with a little bit of code, you can automate the whole process in your invoice system. Before we get into this, though, you should have some familiarity with VBA. If you haven't programmed with VBA before, I recommend you check out my beginner videos on VBA as well as my earlier tutorial on exporting Access reports as PDFs.
We'll be using my free TechHelp template for this demonstration. In the template, customers have orders, and from these orders, you can generate invoices. Normally, the export-to-PDF function in Access saves everything in one directory. I'll show you how to adapt this process so that when a new invoice is created, Access first checks if the necessary folder exists for that customer. If it doesn't, the code will create it.
The first step is to create the main "Invoices" folder, which will sit under your database's current folder, a shared network location, or wherever you prefer. If the folder already exists, attempting to create it again would cause an error, but this is easy to handle. We simply tell Access to ignore that specific error because it just means the folder is already there, which is exactly what we want. For most situations, this simple error handling is sufficient.
Once the main "Invoices" folder is set up, the code creates a subfolder within it for each customer, using something unique like their CustomerID. Again, if the subfolder already exists, Access just ignores the error and moves on.
After these folders exist, the new invoices are exported directly into their respective customer folders. This process makes it much easier for you and your team to navigate and find the correct invoice later. You can customize the process further - such as by adding the customer's name to the folder or giving the PDF files more descriptive names - depending on your own needs.
During the demonstration, you'll see how the process works for different customers and how new folders are automatically set up as needed. If you want to tidy up your existing backlog of invoices and sort them into folders, I'll also go over some strategies for bulk organizing those files in the extended cut for members.
In the extended cut, I explain in more detail how to use the Dir command for more precise folder existence checks, how to handle more complex errors, and how to create a batch routine to move your existing files into newly created folders based on their customer association. I also discuss some advanced file management techniques and highlight features from my advanced Access Developer courses.
Overall, with a few lines of VBA and careful organization, you can keep your invoice directory neat and make file retrieval a breeze. If you'd like detailed, step-by-step instructions, you can find the complete video tutorial with everything covered here on my website at the link below.
Live long and prosper, my friends.Topic List Creating folders using the MkDir command in VBA
Setting up folder paths using CurrentProject.Path
Creating customer-specific subfolders dynamically
Adding basic error handling for folder creation
Exporting Access reports to PDF via DoCmd.OutputTo
Dynamically building output file names with variables
Integrating folder creation with a PDF export button
Using Me.Refresh and Me.Dirty for data integrity
Testing folder and PDF creation per customerArticle If you have ever opened a folder and found thousands of invoice PDFs all sitting together in one big, unorganized mess, you know how quickly things can get out of hand. This article will walk you through how to organize your invoices - or any files - by automatically creating separate folders for each customer using Microsoft Access and a bit of VBA code.
Let's say you use an Access database to generate invoices, and whenever an invoice is created, you automatically save a PDF copy so your sales or shipping teams can find and use these invoices even if they do not have the database open. Over time, if all these PDFs go into a single folder, you might wind up with thousands of files, making it difficult to manage or find the ones you need.
The solution is to save each invoice PDF into a separate folder for each customer. Automating this process in Access is straightforward with the MkDir command in VBA, which stands for "Make Directory."
To begin, you need a form in your Access database where you can trigger the export of your invoice to a PDF file. For demonstration, imagine a form that displays orders, and you have a button to generate the invoice report. What you want is a second button that, when clicked, exports the invoice as a PDF and saves it in the right customer folder.
Open the button's VBA code behind its OnClick event. The first thing you should do before doing anything with the report or data is to ensure the form's data is up to date and saved to the database. You can do this with:
Me.Dirty = False
This small step makes sure that the latest data from your form is saved to your tables, so your invoice has the correct and current information.
Next, you will generate the path where the PDF will be saved. Instead of dumping all PDFs into one folder, you will create an "Invoices" folder, and inside that, a folder named after the CustomerID (or some unique identifier for the customer).
Here is how to handle the folder creation. The command to create folders in VBA is:
MkDir "Your\Path\Here"
If you try to create a folder that already exists, Access will throw an error. For this reason, you want to briefly disable error handling around the MkDir command, so you do not see an error if the folder is already there.
Here's a safe and simple way to set this up. Let's suppose your database is in a folder, and you want the invoices to go into a new folder named "Invoices" inside your database directory, and under that, folders for each customer by CustomerID:
On Error Resume Next
MkDir CurrentProject.Path & "\Invoices"
MkDir CurrentProject.Path & "\Invoices\" & Me.CustomerCombo
On Error GoTo 0
Here, "Me.CustomerCombo" refers to a control on your form that contains the CustomerID or some unique value for the customer. The first MkDir creates the "Invoices" folder if it does not already exist. The second MkDir makes a subfolder for the customer. By using "On Error Resume Next," you tell Access to skip over errors (such as the folder already existing). Afterwards, "On Error GoTo 0" turns error handling back to normal.
Now that the folder exists, you can export the invoice to that path. Use the DoCmd.OutputTo command to export the report as a PDF. Here's an example of the full code inside your button's click event:
Me.Dirty = False
On Error Resume Next
MkDir CurrentProject.Path & "\Invoices"
MkDir CurrentProject.Path & "\Invoices\" & Me.CustomerCombo
On Error GoTo 0
Dim fp As String
fp = CurrentProject.Path & "\Invoices\" & Me.CustomerCombo & "\Order-" & Me.OrderID & ".PDF"
DoCmd.OutputTo acOutputReport, "OrderInvoice", acFormatPDF, fp, True
Beep
Let's go through this step by step. We make sure the form data is saved. We try to create the "Invoices" folder, and then the customer's folder. We set "fp" (standing for file path) to the location and name of the PDF file, which in this example is "Order-" and then the OrderID, so it will be something like "Order-105.PDF". The DoCmd.OutputTo line exports the report named "OrderInvoice" to PDF format and saves it to that path. The "True" at the end opens the PDF after saving, which can be set to "False" if you do not want to immediately view the document. The Beep at the end is optional and simply plays a sound so you know the process has finished.
Once this is set up, each time you run the process for a new invoice, Access will make sure all the necessary folders exist and save the PDF into the correct customer folder.
If you want to see the results, check your database folder; you should now see an "Invoices" folder, and inside there, folders with names matching your customers' IDs. Each folder will have its respective invoice PDFs inside, like "Order-1.PDF," "Order-2.PDF," and so on.
If you would rather name folders with the customer's name instead of their ID, you can modify the MkDir command and the file path to pull the value from the control that holds their name instead. Just keep in mind that folder names should avoid any characters that are invalid for folder names in Windows, so you may want to add some validation or replace those characters just to be safe.
This approach uses simple VBA and is quite flexible. You can expand on this to add better error handling, check for the existence of folders before creating them, or even organize files by other criteria, such as year or invoice type. For more advanced solutions, you can look into using the Dir function to check if folders exist before trying to create them, or even automate the movement of old files into their new folders if you have an existing backlog.
In summary, organizing invoice or report files per customer using folders created with the MkDir command in Access greatly improves file management, making it much easier to locate, back up, and work with your documents. This technique keeps your files organized and saves you time and headaches as your database grows. |
