Zip Unzip Files 2
By Richard Rost
2 years ago
Zip/Unzip Files and Folders with Access, Part 2 In this Microsoft Access tutorial, I will show you how to zip files using VBA, featuring techniques like creating an empty zip file shell and implementing a while loop for file handling efficiency. This practical approach elevates your file management tasks seamlessly. This is part 2 of 2. MembersIn the extended cut, I will show you how to put a timeout on the zip operation, ensuring it aborts if it exceeds 60 seconds, or is manually aborted. We'll also see how to extracting only specific files, or specific types of files, like jpg or text files. 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, VBA zipping files, VBA unzipping files, Access VBA tutorial, automate file zipping, automate file unzipping, Access database file management, VBA file management, file zipping VBA code, file unzipping VBA code, efficient file transfer VBA, creating zip file VBA, runtime error handling VBA
Intro In this video, you will learn how to zip an entire folder of files using Microsoft Access and VBA. I will show you how to create an empty zip file shell, write the necessary header information, and use OSObject to manage file operations. We'll set up and initialize variables, copy files into a zip file, and use a while loop along with the sleep function to monitor and control the zipping process. I will also demonstrate how to handle runtime errors, debug issues like path file access errors, and confirm a successful zipping operation. This is part 2.Transcript Today is part two of my video series on how to zip and unzip files. Part two of two, just two for now. Yesterday, in part one, we learned how to unzip a file and drop all the files that are in it into a folder. Today we're going to go the opposite direction. We're going to take a folder and zip it all up so you can send it to someone else, store it, or put it on a floppy disk. Remember those? All right, here we go. Oh yeah, it's part two, so if you haven't watched part one yet, go do that like first or something and go watch that now.
Today's video is going to be a little more complicated than part one, and one of the things that we're going to be using is a while loop to loop through all the files in the folder and add them to our zip file. So, if you don't know how to use a while loop, go watch this video first. We are also going to need to use the sleep function to make sure that we wait an appropriate amount of time between copying the files into the zip file, so go watch this too. You'll need this. All right, let's go back into our database.
We've got our destination folder here, this is the files that we unzipped out of here so I'm going to just copy this guy, copy and paste, and let's call it source folder so we get the same stuff in there, just want to make sure, I don't want to confuse anybody, it's the same small files. Let's go back in the database. Alright, let's go back to design view, let's copy our unzip button, copy paste, and we'll slide it over here and we'll call this, guess what, this will be the zip button and let's give it a good name. Let's make this zip button. Yeah, I know this guy is still hello world button, that's okay, you can live with it. Build event, let's go.
And I got a bunch of extra space. I sometimes put a bunch of extra space at the bottom of my VB editor because I hate working at the very, very bottom of the window, so I do this a lot. But then if you put something else new in, it tends to come in down here. So that's weird. Anyways. Okay.
This is going to be a little more complicated than unzipping a file. And I've tried to simplify it as much as possible but it's still a little weird. In a nutshell, the first thing we have to do is create an empty zip file shell file, basically. It's an empty zip file that we're going to then copy the files into. It's weird, I know, but it's the easiest way to do it. And we can copy some of our declarations from up here. Let's copy this, paste it down here. It's going to be OSObject again. This is going to be, down here, we're going to be source folder and then destination zip. We're going to just flip those back, okay? We're starting with a source folder and then we're going to put them into a destination zip file. All right, so first up is create an empty zip file. How do we do that? Well, again, this is just a block of code that I'm just going to write for you and you'll see how it works. All right, we're going to open our destination zip file for output as number one. Now, yes, you could have multiple file handles open, number one, two. In my more advanced developer classes, I show you how to check to see if a number is available, but this should work for most instances unless you've got lots of other file I.O. going on in the background. Now this next line, I'm going to copy from my notes. Okay, that is the header information for a zip file. It's characters 80, 75, 5, 6, and then a string of 18 zeros. Why? That's just, that's the header that has to be on the top of the zip file. That's so Windows recognizes it as a valid zip file. You just got to do it. Okay, and then when you're done with that, we can close number one. Okay, so our empty zip file is created at this point. Now we can copy this and put that down here. All right, that's our object shell. We can specify our file names now. In fact, I'm going to just copy these two, copy these, paste them down here with some slight modifications. Whoops. All right, we have a source folder and a destination zip file. So copy that, paste it there. Now the source folder is going to be this guy, source folder. So we'll put that in there. Right here, source folder. And the destination zip is going to be, let's call it new zip dot zip, just like that. Okay?
Alright, so we got our variables all declared and all set up. Next up comes the actual file command, the copy command. So we're just going to take this right here. You don't need that yes to all on the end. Alright? Paste it there. We're just going to flip two things. We're going to copy from the source file. There you go. Okay. Now, here's the weird part. This part up here happens relatively quickly and it'll just go until it's done. Here you want to, if you're going to have more stuff happen after this, you want to wait until it's finished copying because it could take a while in the background. So this is where we need that while loop to check and make sure that the number of files are actually copied to the zip file. And again, I'm just going to copy and paste this code for you. All right, here it is, loop until finished. It's going to say while destination zip items count. That's the number of items in the zip file. While that is less than the number of items in the source folder, we're going to status the word zipping, and we're going to pause a second. Because depending on the size of your files, it could take two, four, 10 seconds. So you want to loop until that's done. And yes, in the extended cut, I'm going to show you how to put a timeout on here. So if it takes more than 60 seconds, you can have it abort. But for now, it's just going to loop until it's done. Now that we're all done with that, we can do this stuff right here. Paste. Set O equals nothing. Status done, and then beep. We didn't put a start status up here. Let's say start. We'll put status unzipping up top on this one here. Okay, so back down here. All right, let's save it. Debug compile. Everything looks good. Let's go ahead and run the zip. And oh, wait a minute. Path file access error. What's going on? I realized this a few minutes ago, but I didn't say anything because I want to see if you guys can figure it out. All right, path file access error. What does that mean? Well, that usually means it can't write the file. OK. All right, what does that mean? Let's hit debug. That's the line it stops at. Now let's examine what destzip is. If you hold your mouse over it, it says empty. Anyone see why? Yeah, look what I did. I caught this earlier as I was typing it. I declared and set up destzip down underneath here, so it doesn't have a value by the time this runs. All right, so we're literally just going to go like this and move this stuff up here. Right? Declare your variables and initialize them before you actually use them. Alright. Now, see, and that's just to show you a runtime error versus a syntax error. Debug compile catches syntax errors. Like if you had something that wasn't spelled right in here, you know, like output z, for example. And it usually catches it with that. But a runtime error is vicious. Runtime errors can sometimes be very hard to hunt down. But let's give it a shot now. Ready, click, and done. See how it kind of paused a second there? Because in the background, Windows is copying those files into the zip file. Let's check our folder and we've got new zip dot zip right there. Open it up and looks good. Make sure the files are good. There's Kirk and there's Picard. And my text file. Okay. All right. See how that works now? Now that is the simplest version of the code that I can put together for you. Okay. I've got a much more elaborate version where again I'll put a timer on there so that we can say, okay, if it takes more than 60 seconds, abort or you could put in abort box on your form too. That also works. Check that inside the loop. We will check to make sure when it's done if the number of files are correct. So if it's expecting to copy three files and make sure it did copy three files, we can verify that too when we're unzipping as well. So that's all stuff. We're going to cover in the extended cut for the members. We'll verify the number of files zip and unzipped. We'll put a counter on there so you can say okay, you're allowed like 15 retries or whatever. All right, like 15 retries or whatever. If it's going to take more than a minute, then I want you to cancel out. And I'll show you how to extract specific files or file types. So if you want just the text files, that's why that text file is in there. I was playing with it earlier. Or let's say you just want to extract just Kirk.jpg. That's the only file you want. I'll show you how to do it. Silver members and up get access to all of my extended cut videos, so join today. And also, if you want to learn more about working with files and folders and the file system object and all that stuff in Access, I've got a series starting with Access Developer 30, where I cover classic VB file input and output, reading and writing text files. Developer 31 covers VB file and folder navigation, copying files, compacting your back-end files and copying them to a different location. Developer 33 covers relinking your back-end files automatically, the file system object, lots more. But that in a nutshell, folks, is how you zip and unzip files. I hope you learned something. That's going to be your TechHelp video for today. Live long and prosper, my friends. I'll see you next time. See the more link below the video to find additional resources and links. YouTube does a pretty good job of hiding it. It's right down there. See this part of the description here. The name, the videos up here, there's a little show more down there. Down the bottom. It's kind of hard to find. But once you click on that, you'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. And YouTube no longer sends out email notifications when new videos are posted like they used to do. But if you'd like to get an email every time I post a new video, click on the link to join my mailing list. And you can pick how frequently you get emails from me, either as they happen, daily, weekly, or monthly. Now if you'd like to become a paid member of my channel and receive all kinds of awesome perks, click on the join button. You'll see a list of all the different membership levels that are available each with its own special perks including my extended cut videos, access to my code vault, lots of VBA source code in there, template downloads, and lots more. I'll talk more about these perks at the end of the video. Even if you don't want to commit to becoming a paid member and you'd like to help support my work, please feel free to click on the tip jar link. Your patronage is greatly appreciated and will help keep these free videos coming. I've got some puppies to feed. But don't worry, no matter what, these free tech help videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free. Now if you really want to learn Access and you haven't tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, and more. It's over 4 hours long. You can find it on my website or on my YouTube channel. I'll put a link down below you can click on. And did I mention it's completely free? The whole thing, free, 4 hours, go watch it. And okay, okay, a lot of you have told me that you don't have time to sit through a four-hour course. So I do now have a quicker Microsoft Access for Beginners video that covers all the basics faster in about 30 minutes. No I didn't just put the video on fast forward. But I'll put a link to this down below as well. Now if you like level one, level 2 is just a dollar. That's it, one dollar. That's another whole 90-minute course. Level 2 is also free for paid members of any level, including supporters. So if you're a member, go watch level 2. It's free. Okay, want to get your question answered in a video just like this one? Visit my TechHelp page and send me your question there. Members get priority of course. While I do try to read and respond to all of the comments posted below in the comment section, I only have time to go through them briefly a couple of times a month and sometimes I get thousands of them. So send me your question here on the TechHelp page and you'll have a better chance of getting it answered. And while you're on my website, be sure to stop by my Access Forum. We've got lots of lively conversations about Microsoft Access and other topics. I have a fantastic group of moderators who help me answer questions. Shout out to Alex, Kevin, Scott, Adam, John, Dan, Juan, and everybody else who helps out on the site. I appreciate everything you do. I couldn't do it without you. Be sure to follow my blog. Find me on Twitter and of course on YouTube. Yeah, I'm on Facebook too, but I don't like Facebook. Don't get me started. Now let's talk more about those member perks. If you do decide to join as a paid member, there are different levels: Silver, Gold, Platinum, and Diamond. Silver members and up get access to all of my extended cut TechHelp videos, one free beginner class every month, and some other perks. Gold members get all the previous perks plus access to download the sample databases that I build in my TechHelp videos plus access to my code vault where I keep tons of different functions that I use, the code that I build in most of the videos. You'll also get higher priority if you do submit any TechHelp questions. Now answers are never guaranteed but you do go higher in the list for me to read them. And if I like your question, you've got a good chance of it being answered. You'll also get one free expert-level class each month after you've finished the beginner series. Platinum members get all the previous perks plus even higher priority for TechHelp questions. You get access to all of my full beginner-level courses for every subject. And I cover lots of different subjects like Word, Excel, VBA, ASP, lots of ins and not just Access. These are the full-length courses found on my website, you get all the beginner ones in addition once you finish the expert classes you get one free developer class promote so lots of training. And finally, you can also become a diamond sponsor. You'll have your name or your company name listed on a sponsors page that will be shown on each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website. So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today. Live long and prosper my friends. I'll see you again soon. Live long and prosper my friends. Live long and prosper my friends. I'll see you again soon.
TOPICS:
Creating an empty zip file shell
Writing header information to zip file
Using OSObject in VBA
Declaring and initializing variables in VBA
Copying files into a zip file
Using a while loop to monitor zipping progress
Implementing a sleep function to manage process timing
Handling runtime errors in VBA
Debugging and resolving path file access errors
Successful file zipping demonstration in VBAQuiz Q1. What is the purpose of creating an empty zip file shell in the process described?
A. To create a visual representation of the files.
B. To make the existing zip file writable.
C. To ensure Windows recognizes it as a valid zip file.
D. To delete all existing data before copying new files.
Q2. How do you initially open a destination zip file in the VB editor according to the tutorial?
A. Open it as write-only.
B. Open it for output as file number one.
C. Open it for input with exclusive access.
D. Open it as append-only.
Q3. What is the purpose of using a while loop in the zipping process as described in the tutorial?
A. To continuously check the code for errors.
B. To delete files from the source after copying.
C. To verify that copying is complete based on the number of items.
D. To increase the speed of copying files to the zip.
Q4. What issue does the 'Path file access error' indicate in the context of the video?
A. The file paths are corrupted.
B. There was a network error during file access.
C. The program lacks the necessary permissions to write the file.
D. The destination zip variable wasn't initialized before it was used.
Q5. What is a practical reason for initializing variables early in the code process?
A. To enable faster code execution.
B. To simplify the debugging process.
C. To use them throughout the code without assignment errors.
D. To prevent other programs from modifying the variables.
Q6. Why is it important to check the number of files in the destination zip during the copying process?
A. To determine the total file size.
B. To ensure all expected files are copied completely.
C. To calculate the time remaining in the copy process.
D. To compress the files during the copy.
Q7. What happens if you initialize a variable after it has been used in the project as shown in the video?
A. The VB editor adjusts the code automatically.
B. The code executes without any issues.
C. It leads to runtime errors like 'Path file access error'.
D. Syntax errors are generated during compilation.
Q8. What membership perk includes access to sample databases and a code vault?
A. Silver and up.
B. Gold and up.
C. Platinum and up.
D. Diamond only.
Answers: 1-C; 2-B; 3-C; 4-D; 5-C; 6-B; 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 focuses on the second part of my series about working with zip files in Microsoft Access using VBA. Previously, in part one, I walked you through the basics of unzipping a file and extracting its contents into a folder. If you have not seen part one yet, I strongly recommend you start there, as it lays the groundwork.
Today, we'll build on those skills and look at the process in reverse - how to take the contents of a folder and compress them into a single zip file. This is useful if you need to send a batch of files, store them more conveniently, or, for nostalgia's sake, move them onto old floppy disks.
This process is a bit more advanced than unzipping files. We will use a while loop to move each file in the folder into your new zip file. If you are unfamiliar with how while loops work, I suggest reviewing my previous lesson dedicated to that topic. You'll also see the sleep function in action here, which gives the computer enough time to complete the copying operations before the macro continues. Again, if you have not seen my lesson on the sleep function, it will provide helpful context.
Let's return to our Access database. We've got a folder called our "source folder" with all the files we want to zip. To avoid confusion, I make sure it contains the same small sample files we used when unzipping.
Inside Access, I start by duplicating our previous unzip button and adjusting its properties for the zip operation. As always, I give it a clear, descriptive name to keep everything organized.
One important note about working in the Visual Basic editor: I often add extra lines at the bottom of my code window to avoid working at the very bottom of the screen. This habit can lead new objects to appear further down, so just be aware of where your code is being placed as you edit.
Now let's consider the zipping process. Unlike unzipping, where you simply extract existing files, zipping requires an extra step. First, you need to create an empty zip file shell. This is basically a blank zip file with the proper header information so Windows will recognize it as valid. Creating that header is a small but necessary step, as it consists of specific characters (in this case, 80, 75, 5, 6, and a string of 18 zeros) at the start of the file. This signals to Windows that it's dealing with a legitimate zip archive. Once the header is written, you close the file and have your empty shell ready.
After establishing your empty zip file, you need to declare and initialize variables for the source folder (where your files are) and the destination zip (the file you are creating). It's critical to make sure all your variables are defined and assigned their correct values before you start using them in your code. Otherwise, you can run into runtime errors where the macro tries to use an undefined variable. In our case, the destination zip variable must be given its filename before the file creation step.
With your variables set up, the next step is to copy the files from your source folder into the zip archive. This is where the real work happens. While copying, it's important to monitor the process, since copying files—especially large ones—can take a moment. You do not want the macro to move on until everything is properly zipped.
This is where a while loop comes in handy. The loop checks how many files are in the destination zip. While that number is less than the number of files in the source folder, the program continues to update the status (for example, indicating "zipping") and waits for a small interval using the sleep function. This gives Windows the necessary time to finish copying before the loop checks again. This approach helps prevent issues from trying to access files before they are actually zipped.
For those interested in more advanced control, I show in the Extended Cut how to implement a timeout on this process. If it takes longer than 60 seconds, the process can automatically abort. I also cover how to verify that the number of files zipped matches what you expected, and how to extract specific files or file types from an archive. These techniques give you greater reliability and flexibility with your zipping routines.
Once the zipping is complete, I recommend resetting any object variables, updating the status to indicate completion, and maybe providing audible feedback such as a beep. I also make the distinction between runtime errors—which can be sneaky and happen only when your code is actually running—and syntax errors, which the compiler will generally catch before you run anything.
A common runtime error you may encounter is a "path file access error." This usually means Access can't write to the destination file, possibly because the variable is blank or declared incorrectly. Carefully check that your variables are declared and initialized before invoking functions that depend on them. Using debugging tools and compile checks in Access can help you track down these types of issues and address them as they arise.
After making the necessary fixes, running the macro produces a new zip file containing all the desired files. I demonstrate how to open the archive and verify its contents—making sure, for example, that all image and text files made it into the zip.
What I've shown here is the simplest, most streamlined approach to programmatically zipping files in Access with VBA. However, there's a lot of potential for customization. In the Extended Cut for members, I go over how to set up timeout safeguards, handle specific error cases, allow user-initiated aborts, check file counts, and extract only certain files from an archive (for example, limiting extraction to only text files or a specific image). If you want to master the techniques for managing files and folders, and working with the File System Object in Access, consider my Access Developer course series. Starting with Developer 30, you'll dig into classic VB file I/O, text file manipulation, folder browsing, copying, relinking, and much more.
To stay up-to-date and support my work, you can also join my mailing list, become a channel member, or simply donate through the tip jar. Memberships come in several levels, each offering increasingly valuable perks such as access to extended cut videos, the code vault, all beginner and expert courses, sample databases, and more.
If you're just getting started with Access, remember that my four-hour Access Level 1 course is completely free and covers all core fundamentals. For those pressed for time, there's also a 30-minute version that covers the basics quickly. Level 2 is just one dollar or free for members of any level.
If you have a question you'd like answered in a future TechHelp video, please use the TechHelp page on my website. I do my best to read all comments, but sending your question through that form gives it priority, especially for members.
Check out the Access Forum on my site for ongoing discussion, tips, and help from a great community, supported by wonderful moderators. You can also find me across social media and on my blog for more updates and insights about Access and related topics.
In summary, today I've shown you how to zip the contents of a folder using Access VBA, including how to manage timing and monitor copying with a while loop, handle errors, and confirm successful completion. 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 Creating an empty zip file shell
Writing zip file header information
Using OSObject to automate file operations
Declaring and initializing VBA variables
Copying a folder's files into a zip file
Using a while loop to monitor file zipping
Using the sleep function to pause between actions
Handling path file access errors in VBA
Debugging runtime errors during file operations
Verifying files zipped successfully |
