Zip Unzip Files
By Richard Rost
2 years ago
Zip/Unzip Files and Folders with MS Access VBA
In this Microsoft Access tutorial, I will show you how to automate zipping and unzipping files using VBA, simplifying file management for Access databases. Learn to programmatically unzip your vendor's frequent product updates and zip your own files for efficient storage and transfer.
Curtis from Mansfield, Texas (a Platinum Member) asks: I get an email with product pricing updates from my major vendor a couple of times a week, and it comes as a zip file with a bunch of text files in it. I know how to automate the process of importing the data, but I still have to manually unzip the file after I save it to my disk. Is there a way I can have Access automatically just unzip the file so I can then run the import?
Members
There is no extended cut, but here is the database download:
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!
Prerequisites
Links
Up Next...
Recommended Courses
Keywords
TechHelp Access, VBA unzip files,VBA zip files,Access VBA file handling,automate unzipping Access,automate zip extraction Access,Microsoft Access automate tasks,unzip with Access VBA,zip automation in Access,VBA shell application,createObject VBA,namespace method VBA,copyHere VBA,variable declarations VBA,VBA programming tutorial,Access database automation,VBA file system access,TechHelp VBA Access
Intro In this video, we will talk about how to unzip files directly from a Microsoft Access database using VBA. You'll learn how to automate extracting zip archives to folders, set up your database environment for file operations, use object variables and the Shell.Application method to manage files, handle file overwrite situations with constants, and add simple user notifications. This tutorial covers key steps for working with zip files in Access without leaving your database.Transcript In today's video, we're going to talk about zipping and unzipping files directly from your Access database. Today's question comes from Curtis in Mansfield, Texas, one of my Platinum members. Curtis says, "I get an email with product pricing updates from my major vendor a couple of times a week, and it comes as a zip file with a bunch of text files in it. I know how to automate the process of importing the data, but I still have to manually unzip the file after I save it to my disk. Is there a way I can have Access automatically just unzip the file so I can then run the report?" Yes, Curtis, absolutely. Let me show you how to unzip a file to a folder using Microsoft Access VBA, and then after that, I'll show you how to create your own zip files. All right, so before we get started, this is going to be a developer lesson, so if you've never done any VBA programming before, watch this video. It will teach you everything you need to know to get started in about twenty minutes, and also go watch my video on variables and variable declarations. These are free videos. They're on my website. They're on my YouTube channel. Go watch them and come on back.
Alright, so whenever I'm working with files and folders, I always want to make sure I've got a database folder set up just to run my database off of. We'll do our zipping and our unzipping in our database folder. So, right now, I just got this file on my desktop. I'm going to make my database file in that database folder. It just keeps things cleaner. Let's open her up. I'm also going to need a zip file to play with, so I'm going to grab one from another folder that I already made. There it is; it's called 'myzip.zip'. Really unique file name, right? And let's see what's in this folder. I don't think it's anything important. Oh, it is, of course, it's stuff that's very important. I got a picture of Captain Kirk, there we go. I got a picture of Captain Picard. And just a simple little text file, just stuff to test our unzipping and zipping. Okay, all right, let's go into our database file here, open her up. This is just a copy of my TechHelp free template. This is a free database; you can download it off my website if you want to. And let's go into Design View, we're going to make this our unzip button. Instead of hello world, it's now our unzip button. All right, and let's right-click, build event. That's going to open up our code editor and put me right inside the hello world button click. We're going to get rid of the status that's in there, and here's where we're going to do our work. Now, first up, we're going to need a couple of variable declarations. First, we're going to need an object variable to allow us to work with files and folders on the system. You don't exactly have to understand what an object variable is at this point. Just know that it has to do with working with the file system, okay? So we're just going to dim o as an object. We also need a source zip file and a destination folder. So we're going to go into right now. Just trust me. And we need a destination folder, also a variant. A variant is basically, it can be anything. It can be any type. It could be a number, a string, or another object. Basically, you're saying it's an undeclared type. All right. Let's set what our variables are. We're going to set o equals createObject, and it's going to be a shell.application. It's basically saying this object is going to work with the application shell that allows us to work with files and folders on the system. Okay? I go over a much more in-depth explanation of what all of this is in my advanced developer classes, but for now, the goal is just to get the code to work. Sometimes, especially when you're starting out, you might come across some code that you know you just need it to work. You don't have to understand exactly what every single line does. Take me for example, I don't know much about the engine of a car but I know how to drive. All right, so you don't have to understand every single line of code here to know that it works. All right, now we're going to set what we want our source zip file to be. So the source zip is going to be equal to, now I want to use that my zip file that I put in the folder that's in the database folder, this guy. I can refer to the current path of the database as currentProject.path, and then we'll put the file in, backslash and it's myzip.zip, just like that. That says I want the myzip.zip file in the current database folder. You can put a full path there if you want to put you know C colon backslash blah blah blah whatever. All right but this works inside the database folder. Now we need the destination folder equals currentProject.path, and where do you want to put these files? Let's put them in a destination folder called desk folder like that. Now it's a good idea for you to make that folder ahead of time because it won't always create it for you, so let's go and just make that folder. All right, we're going to go new folder, and we're going to put desk folder. Okay, I'm pretty sure if memory serves this code will create the folder but it doesn't always. All right, so we've got our object variable declared, so we can work with the file system. We've got a source zip file, we've got a destination folder. Now comes the line of code that does the actual work. Are you ready? It looks like this, o.namespace(destFolder).copyHere. That basically says, in my destination folder, I want you to copy here whatever comes next. What are we copying? o.namespace(sourceZip).items. And there you go. It basically says, in the file system's namespace, which basically is a fancy word of saying the file system, okay. We're going to copy here all of the stuff that's in that source zip file. That's just the syntax you've got to use. I'll be completely honest with you, I've got to look this stuff up sometimes too. I don't remember it off the top of my head. It's not something that I use every day. Now that we're done with o, we have to destroy it. Since we used the set keyword to create it, we have to set o equals nothing, free up that memory. Access doesn't always do a good job of cleaning up after itself, and then we can beep or something to declare that we're done. That just lets the user know, hey, you're finished. All right, get rid of whatever extra spaces we got down here. And that, my friends, should be the final code that we need right there. Let's test it. Save it. Debug compile is always a good idea. Come back over here. Let's close the form. Reopen it. I'm going to click on my unzip button. All right, nothing appeared to happen. And let's go back to our folder and check in our destination folder. And there they are. There are the files that were in the zip file. Okay? Now, there's a lot we can add to this code. There's a ton we can add to this code. For example, if you already have those files in that folder, it'll create an error message. Watch this. If I click on it again, it pops up this guy. It popped up on my other monitor. You want to replace the files because they already exist. I'll say, yeah, go ahead and replace them. Now, it'd be nice if you could reply all to that and just say, just overwrite them by default, and you can. There's a switch you can add over here, okay, to say answer yes to overwrite the files and that is 16. What is 16? There's a whole list of constants on Microsoft's website. If you want to remember what that is, you can make it a constant up here and say yesToAll equals 16 like that, and then if we'll put yesToAll over here. So you remember that that's what that means. Okay, but now if you click the button, it just does it. And it's nice to have a little done down here too. I'll use my status function. Status done. Save it. All right, come out here, click the button. Done. All right, go check your folder and there they are. Those handsome devils. Now, can you verify that the files were extracted? You can do a file count, and I'll cover that in the extended cut for the members. You can basically say, okay, there are three files in the zip file, three files were copied, and so we should be good. There's lots of other stuff we can add to this. We can make this a function, so instead of putting the file names in here, you could just send them in as a function. Okay, so much you can do with this. All right now that's unzipping a file. How do we create our own zip file? If you've got a bunch of text files that you've exported and you want to be able to zip that up and send it to someone. Well, we'll cover that tomorrow in part two. So tune in tomorrow. Same bad time, same bad channel or members, you can watch it right now because I'm going to record it in just a few minutes. But that is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.
TOPICS:
Unzipping files with Access VBA
Creating zip files using Access VBA
Setting up a database folder for file operations
Using object variables to manage file systems in VBA
Declaring variable types in VBA
Utilizing the Shell.Application method
Navigating file paths with CurrentProject.path
Implementing file operations with .namespace and .copyHere
Memory management in VBA with the Set keyword
Handling file overwrite scenarios in VBA
Defining and using constants in VBA for file handling
Debugging and testing VBA code in Access
Adding user notifications to VBA processes
Checking file extraction accuracy in VBA (extended cut)Quiz Q1. What is the primary focus of the video tutorial?
A. Learning to use SQL commands in Access
B. Unzipping and zipping files using Microsoft Access VBA
C. Enhancing graphical user interfaces in VBA
D. Advanced database security measures
Q2. What does Curtis from Mansfield require help with regarding his Access database?
A. He needs help with automating the sending of emails.
B. He needs to learn how to import data into Access manually.
C. He wants Access to unzip files automatically.
D. He is looking for a way to encrypt zip files.
Q3. Before programming in Access VBA for file handling, what did the presenter suggest should be watched?
A. A tutorial on advanced Excel functions
B. A video on ERP software installation
C. Videos on VBA programming basics and variables
D. A seminar on web development fundamentals
Q4. What method does the presenter use to manipulate files and folders in Access VBA?
A. Using a SQL query
B. Using an object variable with the shell.application
C. Directly manipulating Windows OS settings
D. Through manual file system control without coding
Q5. Which method does the presenter provide for checking if the unzipping process worked correctly?
A. He does not provide any method.
B. He suggests using a data analysis tool.
C. He recommends a file count comparison.
D. He advises checking the system's memory usage.
Q6. Which operation does the code 'o.namespace(destFolder).copyHere(o.namespace(sourceZip).items)' perform?
A. It copies files from the source zip into the destination folder.
B. It deletes the contents of the destination folder.
C. It moves the Access database file to a new location.
D. It encrypts the files in the destination folder.
Q7. What does the presenter suggest to handle the error of files already existing in the destination folder?
A. Restarting the computer
B. Ignoring any errors that arise
C. Using a constant with a value of 16 to overwrite files
D. Deleting the original files manually
Q8. What future topic indicates the presenter will cover in the following part of the video series?
A. How to delete a zip file using VBA
B. How to create a zip file using VBA
C. How to encrypt database content in Access
D. How to use SQL for advanced data manipulation
Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-A; 7-C; 8-B
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 TechHelp tutorial from Access Learning Zone will show you how to zip and unzip files directly from your Microsoft Access database using VBA. I often get questions about how to streamline the process of working with compressed data files, and today we'll focus on how Access can help automate these tasks.
Here's the scenario: Perhaps you receive product pricing updates from a vendor in the form of a zip file containing multiple text files. Maybe you've already automated importing the data into your database, but you're still having to manually unzip the received file before you can do anything else with it. There's actually a way to have Access handle the unzipping for you, making your workflow much more efficient.
Before you jump in, keep in mind this is a developer-level lesson. If you're new to VBA programming, I recommend watching my beginner's introduction to VBA, as well as my videos on variables and variable declarations. These will give you the foundation you need to follow along. These resources are available for free on my website and YouTube channel. Once you have a handle on those, come back here to tackle file and folder operations with Access.
When working with files in Access, it's a good practice to set up a dedicated database folder for your database file and any related operations, like zipping and unzipping files. This helps keep things organized and avoids the clutter that can accumulate on your desktop. For our example, I have a simple zip file called 'myzip.zip' stored in this database folder. Inside, I've included a few basic files: two images and a text file. These are just placeholders for demonstration purposes, but you can, of course, use your own data.
The next step is to go into Access and set up a form with a button for unzipping files. Starting with my TechHelp free template, which you can download from my website, I'll repurpose one of the buttons to serve as our "unzip" trigger. By opening the form in Design View and building an event on the button, we access the VBA code editor. Here's where the actual interaction with the file system happens.
To work with files and folders, we need to use object variables in VBA. If you're not familiar with object variables yet, know that they allow Access to interface with other programs and services, like the Windows file system. Declaring an object variable is straightforward, and for our purposes, we'll create one to handle our file operations. In addition to that, we'll declare variables to specify the location of our source zip file and the destination folder where we want to extract the files.
The magic happens with a specific method provided by Windows: Shell.Application. This method gives you the tools to manipulate the file system from within VBA. By setting your variables correctly, you can specify which file to unzip and where to place the unzipped contents.
To keep your file paths flexible, you can take advantage of Access's CurrentProject.Path property, which always refers to your active database's folder. This way, you're not hardcoding any directory paths, and your code will work no matter where your database is stored, as long as the relative file structure is maintained.
Once everything is set up, the core operation involves copying the contents from the zip file to your destination folder. The syntax may seem a little strange if you've never done it before, but it's just the way Windows handles these types of operations behind the scenes. Be sure to clean up the object after you're done to avoid memory issues. This is as simple as setting the object variable to Nothing at the end of the process.
After implementing this solution, press your new unzip button. If everything runs successfully, visit your destination folder and you'll see the files that were previously hidden inside the zip file.
Now, there are quite a few ways you might want to expand upon this process. For example, what happens if the destination folder already contains files with the same names? Windows will prompt you to confirm whether you want to overwrite those files. In VBA, you can include a constant that automatically answers "Yes" to these prompts, avoiding manual confirmation and speeding up repeated file extractions. Just define the constant at the top of your procedure and include it in your copy operation.
It's a good idea to include user notifications, like a simple status update or beep, to let your users know when the process is done. This improves usability and helps avoid confusion, especially when actions happen behind the scenes.
For those who want extra error checking, you can also verify that the correct number of files were extracted. In the extended cut for the members, I cover how you can count the files in the zip archive and the destination folder to make sure everything worked as planned.
Today's lesson has focused on unzipping files automatically. In tomorrow's tutorial, I'll show you how to create or zip files yourself using Access VBA, perfect for when you want to bundle up text exports or other data before sending them off. Members can access part two right away, but everyone else, be sure to come back for the next installment.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Unzipping files with Access VBA
Setting up a database folder for file operations
Using object variables to manage file systems
Declaring variable types for file handling
Utilizing the Shell.Application method
Referencing file paths with CurrentProject.path
Extracting files using .namespace and .copyHere
Managing object memory with Set keyword
Handling file overwrite prompts in VBA
Defining constants for file operations
Adding status notifications in VBA |
