Extract OLE Image 2
By Richard Rost
2 years ago
Extracting OLE Image from MS Access Field, Part 2 In this Microsoft Access tutorial, I will show you how to extract OLE images from a database and save them as JPEG files using MS Paint and VBA automation. This is part 2 of our series. PrerequisitesLinksRecommended CoursesUp Next...
Keywords TechHelp Access, OLE image extraction, VBA automation, SendKeys in Access, BMP file saving, Visual Basic for Applications tutorial, Access database tutorial, automate image extraction, Part 2 tutorial, error handling in VBA, image file path, customer ID for image filenames.
Intro In this video, I will show you how to automate the extraction of OLE images from a Microsoft Access database using VBA. We'll walk through manually copying an image to Microsoft Paint, resizing and saving it, and then build the VBA code to automate the process, including setting focus on the image field, copying images to the clipboard, launching Paint with the Shell command, and using SendKeys to control the application. Other topics include handling file paths and error management in VBA, dynamically naming files using CustomerID, and adding automation to your Access forms. This is part 2.Transcript Today's Part 2 of my "TechHelp" series on extracting an OLE image from your database. If you haven't watched Part 1 yet, go watch that first. You'll find a link down below, and then come on back.
All right. So, let me show you what we're going to do now that we have the images in the database. This is what we're going to do. I'll do it manually by hand first, and then we'll do it with a little automation. So the first thing is to open up Microsoft Paint. Now, it should be on your computer if you're using Windows, which, if you're running Access, right, should be under Start. Now, mine's right here. If not, just type in P-A-I-N-T, and Paint should show up right there. And that's going to open this guy.
Now, I've already got my image nice and small, but usually, when you start this palette, this background here is really huge. So, what you want to do is, you want to shrink this down, find the edges—you might have to scroll, right, if your window is smaller, like this, you might have to scroll up and down—find the edges, bring this thing in nice and small, like postage stamp size, smaller than any image in your database is going to be. Okay? And then just put something dumb in here and save it. I don't care what you save it as, save it as junk, whatever. The reason why is because we want this to be our default palette size because we're going to come in here and hit Paste, and that's going to expand that palette size to the exact size of whatever image we drop into it.
All right, ready? Close this. Now, we're going to come over here. We're going to click on Spock, copy, Ctrl+C, Spock is now on the clipboard. Okay? Now, we're going to open up Paint again and hit Paste, Ctrl+V. Boom, there he is. See how it resized the palette for you. Okay, now we're going to save Spock. Now we're not just going to use Ctrl+S because that's going to save it usually as a PNG file and there's no easy way to set what the default file setting is in Paint, at least I haven't found it. So I'm just going to use the keyboard, and this is what we're going to mimic with SendKeys. It's going to be Alt+F to open up the file menu, down, down, down, down, down, right, down, down. You can save it as a bitmap or a GIF or a JPEG, whatever you want. Let's go JPEG. I think JPEGs work best.
Alright, so once you've done that, press Enter. Okay, now you've got to type in the file name. Now, I'm going to send it the whole path to where I want it to go. But just for now, just for demonstration purposes, I'll just type in Spock. It's going in my My Drive folder. That's fine. Alright, press Enter, and now it's been saved to the hard drive. Now I can close Paint, Alt+F4. Okay, and now I'm back to my database. And then I would just continue on with the next record. Go to the next record. Do the same thing.
Let's have Access do this automatically. Okay, how are we going to do that? Well, let's go back to here. Alright, let's repurpose this button as saving this guy. Right-click, Design View, and I want to change this button to Extract Image like that. Alright, move it over here in the middle column. There you go. Alright, let's open up its properties. Let's change this to Extract Button, and let's see what we got in here. I know we got some other code in here that we can probably get rid of. Right-click, Build Event. Alright, yeah I got rid of these other two buttons. That's just to open up the orders in the context. We can get rid of that. I don't like a cluttered VBA window.
Okay, so what we want to do is take that image and do what we just did by hand only with VB code. So the first thing is to set the focus on that field. The field is called Profile Picture. So we're going to say ProfilePicture.SetFocus. That moves the focus from the button up to where that image is. Okay, so now the cursor, basically, the focus is sitting on that picture. Now we're going to copy whatever is there to the clipboard. And that is DoCmd.RunCommand, acCmdCopy. All right, that just got to know the command. That's what it is. OK?
Now, before we actually save it, let's put the file name in a variable. OK, let's come up here, and I'm going to say Dim FileName as a String. OK, and then down here, I'm going to say FileName equals. Let's put the full path to our database folder in here first. Which, if I go back to my folder, here it is, there's the full path right there. I'm going to copy that, right, C:\\Users\\Amicron\\DesktopewDB\\, whatever you want to put it. You can even make an Images folder in here and put it in there, whatever you want to do. Actually, yeah, let's do that. Let's put an Images folder in here. New, Folder, we'll call it Images. We'll put all of our images in here and now we can copy that. Copy. Alright, let's go back over here. The FileName is going to be that.
And then let's base this file name on something for the record. How about the customer ID. So let's call this Pic-&-CustomerID, so it's 1, 2, 3, 4, whatever. And we're going to save them as JPEGs. Okay, so that's our FileName. Delete the file if it's there. Now I don't want to go there. Yeah, you can go through and check to see if it's there with a Dir command and all that. But the easiest way to do this is to just delete it whether it's there or not. But if it's not there, then Access will generate an error which I don't want to see. So we're just going to temporarily turn error handling off: On Error Resume Next. I got a whole separate video on that. I'll put a link down below. I also have a whole separate video on variable declaration, which I will also put a link to that down below. Sometimes I get going in these videos, and I forget to, okay maybe you got to go watch this first. I always forget to put stuff in the prerequisites. Half the time, I go back, which I might go back now and put them in there at the front of the video, which by this point you've already seen.
Okay, all right, so we're going to turn off error handling. We're going to say Kill FileName. Be very careful with that. It'll try to delete it, and it'll ignore the error. And then we're going to turn error handling back on. On Error GoTo 0 is how you turn error handling back on. Normally, we want to see errors because if something's not right in our code, I want to see it, as a developer especially. Now, by the time you push this to your end-users, you should have all those errors handled, but you always got to, error handling is something you always have to take into consideration. But for this example, I know that's going to throw an error if that file doesn't exist, which is going to happen the first time you do this. Right? That only comes into play like the second, third time you do it.
Okay. Now, we're going to open up Paint. Okay. How do we do that? We use the Shell command. I've covered this in several other videos, but the Shell, I don't have a video for the Shell command itself. I know I covered it in a couple of my lessons, but it's real simple. It just opens up and launches another program. Alright, so we're going to do Shell "mspaint.exe". Now we don't need the full path and file name to that because the folder that's in is in our Windows path. So we can just say Shell "mspaint.exe". That's our Paint application. Okay. And I also want to say, comma vbNormalFocus. We don't want to minimize. We don't want to maximize we want it normal which is a normal window, and we want to give it focus so it's in the foreground, and yeah, I'm going to do a whole separate video on Shell eventually. It's one of those programs that you're going to use occasionally or commands, excuse me, you're going to use it occasionally but not all the time so it's not really critical that I made a video on it. I know I covered it a bunch.
Now, here's the point at which we have to wait because depending on the speed of your computer, this can take a second, three seconds, whatever to load. So you kind of got to gauge this based on your machine. I got a relatively fast new computer, so I'm going to pause here for, let me say one second. I believe I already have the Sleep function in my global module. Yep, I do. I got SleepSeconds which is for whole seconds, or you can just use Sleep to sleep milliseconds. Okay, I covered this in that sleep video that I told you to watch the last time, and back to my code. So here's where I'm going to sleep. Alright, let me go back to here. I'm going to say, let's try Sleep 1000. Just give me a one-second sleep there and wait for Paint to load. Okay.
Now, once it loads, we're going to do a bunch of SendKeys. Alright, so we're going to paste the image. That's going to be SendKeys "^v". Control, that's the caret, it's on the 6 key. Control "V", make sure that's a lowercase "v". If not, you're going to get Control+Shift+V. I've run into that myself. Alright, SendKeys "^v", and then optionally you can say wait here, but it generally always waits anyway. That's only for really long strings, you got to worry about that.
Alright, so now we've pasted our image in, and let's stop here and take a look and see what we got so far. Let's just make sure that what we have here is working. It should open up Paint, wait a second, and paste that image in. Alright, let's save it. Always good to throw in a Debug Compile. Alright, let's close this, come back out here, close this, back into our Customer form, click the button, and there you go. It opened up Paint and it pasted Spock in there.
Okay, now we're ready to save the image. And we'll talk about that in Part 3. Now today's Friday, I'm recording this on Monday, but this is being released on Friday, April 19, 2024. So Part 3 will be released on Monday the 22nd. So tune in Monday, same bat-time, same bat-channel—yeah I know it says tomorrow, but they didn't, I don't think Batman used to ever, they didn't, they didn't cliff-hang over the weekends at all. I don't think so. That was like the famous saying right "Tune in tomorrow, same bat-time, same bat-channel," it's great for that and old... but if you remember, you can watch it right now because I'm going to keep recording and all before you miss, so, so they don't get in there at the end of Part 2 at the top of the video for today. Live long and prosper, friends. See you Monday for Part 3.
TOPICS:
Opening Microsoft Paint on Windows
Setting default palette size in Paint
Copying images from clipboard to Paint
Pasting and resizing images in Paint
Saving image files from Paint with specific formats
Navigating and using SendKeys in Paint
Access VBA automation for image extraction
Designing and repurposing form buttons in Access
Writing VBA code to set focus on image fields
Copying images to clipboard using DoCmd.RunCommand acCmdCopy
Declaring variables in VBA (Dim FileName as String)
Creating and setting file paths in VBA variables
Deleting existing files with Kill command
Error handling with On Error Resume Next and On Error GoTo 0
Launching external applications with Shell command
Implementing Sleep function for timed delays in VBA
Using SendKeys to simulate keyboard input in applications
Debugging and compiling VBA code with Debug Compile
Running VBA code to test automated Paint operations
Closing external applications from VBA (e.g., Alt+F4)
Navigating and using Access form controls in VBA
Using CustomerID to name image files in VBA code
Creating new folders for storing images via Windows ExplorerQuiz Q1. Before automating the process of saving an OLE image from a database using VB code, which application was demonstrated for manually saving images?
A. Microsoft Access
B. Microsoft Excel
C. Microsoft Word
D. Microsoft Paint
Q2. What is the first step in manually saving an OLE image after opening Microsoft Paint?
A. Paste the image into Paint
B. Save the image as junk
C. Shrink the background to postage stamp size
D. Set Paint as the default program
Q3. What key combination is used to copy an image to the clipboard in the tutorial?
A. Alt+C
B. Ctrl+V
C. Ctrl+C
D. Shift+C
Q4. In Microsoft Paint, which key combination will NOT save an image as a .jpeg by default?
A. Ctrl+S
B. Alt+F, then navigating the menu
C. Manual selection from the Save As menu
D. Right-click and save
Q5. What should be the first action in the VB code to replicate the process demonstrated in the tutorial?
A. Open Microsoft Paint using the Shell command
B. Set focus to 'ProfilePicture' field
C. Copy the image with DoCmd.RunCommand
D. Define a variable for the file name
Q6. Which command is used in VB to copy the content to the clipboard?
A. Copy.Execute
B. DoCmd.RunCommand, acCmdCopy
C. Clipboard.SetData
D. SendKeys "c"
Q7. How is the file name for the saved image suggested to be structured in the VB code portion of the tutorial?
A. Based on the customer's email
B. Consisting of just the customer ID
C. Including "Pic-" followed by the customer ID and saved as a .jpeg
D. As a random generated string
Q8. Which VB command was suggested to use for launching Microsoft Paint from the code?
A. Launch "mspaint.exe"
B. Open "mspaint.exe"
C. Execute "mspaint.exe"
D. Shell "mspaint.exe"
Q9. How does the tutorial suggest to handle the potential error if a file being deleted does not exist?
A. Use an If-Else statement to check if the file exists
B. Ignore the error by using 'On Error Resume Next'
C. Display an error message to the user
D. Avoid using the delete command altogether
Q10. What is the reason given for introducing a "sleep" delay before sending commands to Paint?
A. To allow time for an antivirus to scan Paint
B. To give the user time to see what happens
C. To wait for Paint to load, as its speed may vary per machine
D. To ensure that the clipboard has time to store the copied image
Answers: 1-D; 2-C; 3-C; 4-A; 5-B; 6-B; 7-C; 8-D; 9-B; 10-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 TechHelp tutorial from Access Learning Zone is Part 2 in my series on extracting OLE images from your Access database. If you have not had a chance to watch Part 1 yet, I highly recommend catching up with that first to ensure you follow everything we do here.
Now that we have the images stored in the database, let me walk you through the process of extracting one manually, and then we will look at how to automate this routine with VBA code in Access. To get started, I begin by opening Microsoft Paint. If you use Windows, Paint should already be installed on your machine. Sometimes the initial canvas in Paint is quite large, so the first step I always take is to shrink it down to a very small size. I aim for something even smaller than the images I plan to extract from my database, almost like a postage stamp. Then I save this tiny canvas as a file - any throwaway name is fine. The purpose of this is simple: Paint will automatically resize the canvas to fit any image I paste in later, so by starting from a minimal size, Paint adjusts to match my images exactly.
Next, I return to Access, copy my image (for example, a picture named Spock) from the database to the clipboard using Ctrl+C, go back into Paint, and paste it in. You will notice that using Ctrl+V resizes the Paint canvas perfectly to fit your image. At this point, I am ready to save the image. It is better not to use the default Save command, since Paint usually saves as PNG format by default and setting your preferred type permanently is not straightforward. Instead, I use keyboard shortcuts within Paint, specifically to save the image in the format I want such as JPEG, GIF, or Bitmap. This manual approach involves using Alt+F to open the file menu and then navigating through the save options using the keyboard, ultimately saving the image with the desired name and in the correct location. After closing Paint, I would repeat the whole process for each additional record.
Of course, this whole procedure can become quite tedious when repeated for many images. So, the next goal is to automate everything with VBA inside Access. To do this, I repurpose a button in my Access form and rename it as "Extract Image". The button is placed conveniently in the form, and I adjust its properties as needed.
Inside the button's VBA code, I first use the SetFocus method on the image field (named ProfilePicture in my case) so that any subsequent commands are directed at the right form control. Then, I use the built-in DoCmd.RunCommand with the acCmdCopy argument to copy the image to the clipboard.
For saving the image, I need a file name and path. I declare a variable as a string to hold the file name, and I set the full path to the location where I want My images to be saved. Creating a dedicated Images folder for these files keeps things organized. I build the file name dynamically, using something unique from the record such as the CustomerID, and append the appropriate file extension. This way, each image gets a meaningful, unique name.
Before saving the image, it is wise to delete any existing file in the target location with the same name. To avoid an error if the file does not exist, I temporarily switch off error handling with On Error Resume Next, execute the Kill command to attempt to delete the file, and then turn error handling back on with On Error GoTo 0. It is a simple way to handle file overwrites without showing unnecessary error messages to users.
To reopen Paint through code, I rely on the Shell command. There is no need to specify the full path for mspaint.exe since it is included in the Windows system path by default. I set the application to open in a normal window so it gets focus for the upcoming operations.
Different computers start up applications at different speeds, so to handle this I include a quick pause by using the Sleep function in VBA, usually for a second or so, to ensure that Paint has loaded fully before continuing. I have covered creating Sleep routines in earlier videos if you want more details about how that works.
Next, I instruct Access to send keyboard actions to Paint. Using SendKeys, I simulate pressing Ctrl+V to paste the copied image into Paint. It is important to use the correct syntax and capitalization here so you do not accidentally trigger the wrong shortcut.
This is a good point to stop and verify that everything works: the Paint window should open, the image should paste in, and everything should look right so far. From here, I would expand the automation to save the file and close Paint, but we will cover that in Part 3.
This part of the tutorial was recorded on Monday, April 19, 2024, and Part 3 will be released soon. For now, you can review these techniques and get your code set up so you are ready when the next lesson arrives.
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 Opening Microsoft Paint from Windows Start menu
Resizing the Paint palette to a small default size
Copying images from Access to the clipboard
Pasting images into Paint and auto-resizing the canvas
Saving images in Paint with a specific file format
Using keyboard shortcuts to save files in Paint
Repurposing Access form buttons to extract images
Changing button properties and event names in Access
Focusing on image fields in Access forms via VBA
Copying an embedded OLE image in Access with VBA
Declaring file name variables in VBA
Composing file paths and file names in VBA
Creating a new folder for storing extracted images
Deleting an existing file in VBA with Kill
Managing VBA error handling with On Error statements
Launching Paint from VBA with the Shell command
Pausing VBA execution with Sleep to wait for Paint
Simulating keyboard input with SendKeys in VBA
Testing image extraction automation with VBA |
