Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > TechHelp > Directory > Access > Zip Unzip Files < A-Z Jump Buttons | Zip Unzip Files 2 >
Back to Zip Unzip Files    Comments List
Transcript Pinned    Upload Images   Link   Email  
Richard Rost             
3 months ago
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 ''. 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, just like that. That says I want the 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.

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)

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Zip Unzip Files.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 7/23/2024 2:49:00 AM. PLT: 1s