Quick Queries #73
By Richard Rost
14 days ago
Why Access Won't Import Excel Images & How to Fix It
In this video, we'll talk about common challenges when working with pictures in Excel spreadsheets and why Microsoft Access does not recognize those images during import. We'll also discuss what the Dataverse button means for Access developers, the pros and cons of using class modules, issues with reading older YouTube videos, and how one-to-many relationships apply across different types of business databases. This is Quick Queries 73.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Excel image import, Dataverse button, Power Platform, Power Apps, Power Automate, Teams integration, SQL Server migration, class modules, object-oriented design, one-to-many relationships, invoicing database, workflow database, parsing HTML export, VBA image extraction, check register video
Intro
In this video, we'll talk about common challenges when working with pictures in Excel spreadsheets and why Microsoft Access does not recognize those images during import. We'll also discuss what the Dataverse button means for Access developers, the pros and cons of using class modules, issues with reading older YouTube videos, and how one-to-many relationships apply across different types of business databases. This is Quick Queries 73.
Transcript
If you've got Excel spreadsheets with pictures in them, and Microsoft Access keeps acting like those images don't even exist, you're not crazy. Today, I'm going to show you why that happens, and the weird but workable way around it.
Welcome to another TechHelp Quick Queries video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. We're on Quick Queries 73, folks.
Today, we're going to talk about that Excel problem, and we'll answer your questions from YouTube, my website forums, emails, and more, including what the Dataverse button really means for Access developers, when class modules are helpful versus overkill, why some older YouTube videos are hard to read, and how one-to-many relationships solve problems no matter what industry you're working in.
Are you ready? Here we go.
Leading off, we have a question from Alan, one of my expert-level students. He says, my current version of Access has an additional button called Dataverse on the external tab on the ribbon. Richard doesn't include this export option in these videos. Made 11 years ago? Yeah, the Dataverse wasn't around 11 years ago. Actually, close though, I just looked it up, and it was initially launched in 2016 as the Common Data Service, and then it was renamed to Dataverse in 2020. So it's been around for five or six years now.
Anyway, does it cover what this does anywhere else? Well, I did talk about this briefly back in Quick Queries 55. I had to look it up. At that time, a couple of months ago, I was considering putting together some lessons for beginner Access developers who want an easy way to get their data online, and I still might do that. However, for serious Access developers, which make up the vast majority of my students, Dataverse is kind of a bump in the road on the way to a proper solution like SQL Server.
So what is Dataverse? Dataverse is a managed cloud-based platform designed primarily for what Microsoft calls the Power Platform. It's not a full-featured relational database engine. It works well when you're building Power Apps solutions that need built-in security, authentication, auditing, and tight integration with things like Power Automate, Teams, and Microsoft 365. So, Dataverse is really designed to let non-developers build apps quickly without worrying too much about server management. That convenience is its strength, but it's also its limitation.
For serious Access developers, Dataverse starts to feel restrictive pretty quickly. I know I felt restricted when I started playing with it. It doesn't offer true TSQL, which is what SQL Server has. It doesn't have advanced query optimization, stored procedures, the same level of control over indexing, performance tuning, and a lot of the things that experienced developers expect and that you need for a really good solution. You also have to accept licensing costs, storage limits, and platform constraints that you don't control.
If you enjoy designing normalized databases, writing complex queries, and scaling applications, Dataverse is going to feel like it gets in the way more than it helps. Again, this is why SQL Server remains the natural next step for Access developers. The migration path is clean, the concepts transfer directly, and Access continues to work extremely well as the front end. You gain scalability, performance, and professional-grade database features without losing the development model that you already understand. Dataverse isn't a bad tool, but it's a platform convenience layer, not a destination for people who want to get serious about databases.
Now, the smart move is knowing both and using each where it makes sense. SQL Server should be your foundation, and Dataverse can be something that you reach for when Power Platform requirements demand it. In Star Trek terms, Dataverse is the holodeck and SQL Server is the engineering room.
So, the short version is this. If your goal is to spin up a simple Power App, maybe a small mobile app or a lightweight internal tool (someone's beaming in - where was I before? I was rudely interrupted by those clients), anyway, if you're building a small mobile app or lightweight internal tool that needs to live in the Microsoft ecosystem with minimal setup, then Dataverse is absolutely worth a look. Dataverse is designed to get non-developers productive and up and running quickly.
But if you're building something that actually runs your business, where performance, scalability, long-term control, and serious database design matter, then SQL Server is the right foundation. So use Dataverse if you want to work with the Power Platform, and build on SQL Server when the database itself actually matters. Different tools, different missions.
Now, I am currently working on a new course that is designed for Access developers to migrate to SQL Server. You still keep your Access front end; that doesn't change. You put all your data up in the server, and then we optimize that and get it running. Lots of benefits to that. That'll be coming soon. I might do a Dataverse Seminar or a couple of lessons on it. I haven't decided exactly what yet, but SQL Server is definitely coming first.
Great question, and thanks for posting that.
Next up, Robert, one of my Gold Members, posts: With the help of CodePilot I've been making a workflow database with class modules. It has an interface class, a concrete class for each state, a context class, a state factory class. I like it because the logic for each state is stored in its own class, and the permissions are checked in the context, so it's very organized. But for someone who's never done any object-oriented programming, it seems pretty complicated. Just wondering if this is the way you'd go about making your security seminar database with class modules, or if there's a simpler way.
Well, no, I probably wouldn't implement my security seminar using class modules. I've been very upfront about this whenever I talk about class modules, which, by the way, I start covering in Access Developer Level 50, or up to Level 50. It's pretty advanced stuff. However, and I say this right in the first class, in my 20-plus years of consulting, building thousands of different databases for companies of all sizes, I almost never needed class modules to get a database project done.
They're powerful, and you can do some really cool things with them, and I show a lot of really cool things in my classes. But they are absolutely optional in Access. For most applications, including what I teach in my security seminar, a simpler, more procedural approach is usually easier to build, understand, and maintain.
So, if you're comfortable with object-oriented design and enjoy that style, great. If not, you're not doing anything wrong by skipping it. I used to program a lot in C and C++ way back in the 90s. Object-oriented stuff is really cool, but do you need it for an Access database for a business system? No, not really. Learning it will definitely give you another tool in your box for some really cool enhancements. But it's not something that you have to burn brain cells over if you're not getting it. So don't panic. But I do have more lessons on class modules coming up, so stay tuned. Developer 51 and 52 should be finished soon, and I've got more class module stuff in there.
Next up, let's go to YouTube.
TCLinks, one of my members, says: Having a heck of a time getting the syntax right based on the video. The video isn't zoomed in enough for me to see what is actually being typed for the expression, and the audio doesn't always say what character is being used and when. I'm having to try to figure it out by trial and error, and I wish you used the zoom feature in order to type the stuff in so we could see it in the video better.
Now, she's talking about my check register video, which is this guy, about five years old. Yep, five years old. I try to be better nowadays in my more modern videos about zooming in and letting you guys see stuff that needs to be typed. I used to use the Shift+F2 zoom box.
Another thing that I will tell you is that YouTube sometimes compresses the video based on your Internet speed and lots of different factors. I noticed that sometimes video, let me scroll to a different spot in the video here, sometimes this stuff isn't as clean and crisp as it should be. Here, for example, you can see it's kind of tough to see even zoomed in a little bit.
Now, if you go to my website, you'll usually find the link down here for where it is on my website. Let's see, did I put these in there? I don't know if I put them in there back then. But nowadays you'll always find a link on my website. But just go and search for this video, the check register database, on my website.
All right, here's the TechHelp section, here's the check register video right there. Now, down here you'll see different levels where you can also zoom in here. So you can go to like Excel, for example, and it gets much bigger. But my site doesn't compress the video at all. So if I skip ahead here, notice how that's a lot crisper. I don't play any compression games. You're getting the full video. Plus, you can always right-click and go "Save video as" on my site. You can download this video and you'll get the full-size video and you can play it in whatever video player you want. That's another way that you can get crisper, cleaner video.
YouTube plays, what, billions of minutes of video every second or something like that? So they do a lot of compression; I don't. So if you're having trouble watching a video on YouTube, try watching it on my website. You can zoom in, you can download the file, there's all kinds of tricks you can try.
Next up, Richard, I am your follower. I want to develop an entry form of patients, daily registration, and payments. How can I develop the daily payments that add prices to similar services every request?
This is a really good question. The key to understanding is that the industry type, what you're doing, doesn't matter. Patients and visits, customers and orders, phone calls, transactions, workouts, your daily food diary - it's all the same database problem. What you're really building is a one-to-many relationship. One patient can have many visits. One customer can have many orders. One visit can have many services, that kind of thing.
If you're charging for the same services over and over, and you don't want to hard code prices in each visit, you create a services table or a products table. Then you link those services to each visit or transaction. That way you pick a service, it pulls in the price, you total it up.
I've covered this exact concept in dozens of videos, especially my order entry and my invoicing videos, and also several times throughout my fitness database series. Everyone's always saying they're skipping the fitness series, but there's lots of cool stuff in there. I show how to pick an item from a list of food items and add it to your daily food log. That's the exact same concept that you're covering here - same idea, different labels.
Once you understand one-to-many relationships, you can build any of these systems. The business type does not matter. I'd recommend starting with this invoicing video. It'll teach you the basics.
Next up, Peter asks: I have an Excel spreadsheet that is a column containing a picture per line, and when I try to import it into Access, it doesn't copy that column. Is that something that can be done?
The easy answer is no, not automatically. Or I should say the short answer. I get this question a lot. I'm surprised it hasn't come up yet in a Quick Queries video.
When Excel has pictures in it, those images aren't actual cell data. They're floating objects layered on top of the spreadsheet. So, here's a sample spreadsheet, for example. These images here, they're not really in those cells. Excel does a really good job of keeping things together. Like, if I cut this and try to move it down here, insert cut cells, it'll move these around, so you think that's part of this row or this column, but it's not really. It's an object that's floating on top of that. You can move it anywhere you want.
This is one of the reasons why Excel and Access are very different. Access thinks in terms of records with fields, and Excel is just a big combination of rows and columns. You can put stuff anywhere you want.
The big question: is it possible to export this and pull it into Access? Not easily, no. You can export this and you'll get the data, but you're not going to get the pictures. And if you go from the other way and try to import it from Access, same thing - you'll get the data, you won't get the pictures. Because these aren't really stored in the row.
The only thing I can think of that you might be able to do is you could automate it with some Excel VBA, with some automation. You'd loop through the rows, grab the picture object that's visually sitting on that row, and then export it to a folder. Save the file path and file name. At the same time, write out the record in a text file with the ID so you could match it back to the correct picture. Then you could import all that data into Access and store the file path of the image that you just saved out of Excel. It's doable, but it's a decent amount of VBA, and it might not work 100 percent. Because you're dealing with floating images here, and if this guy is just slightly up a little bit, it won't register as being in that row when you figure out its coordinates.
It's not something I'd recommend doing unless you've got a gigantic spreadsheet with thousands of images and you need to bring them over to Access, because that's how they should properly be stored. If you just have a few dozen, or maybe even like a hundred or so images, and it's only a one-time job, I would just do it by hand: save each image and then import the file. But that's not easy to do.
Another thing, and I've been asked this before too, if you click on one of these pictures, there's a little box that appears. That says, "Place in cell." If you do that, it puts the image in the cell. Same with this one here: puts the image in the cell, "Place in cell." This makes Excel happy, and it actually makes the Excel sheet easier to work with, because you're not dealing with images that are floating easily over the cells. But it's still not real cell data. All that does is anchor the picture to a specific cell so it moves and resizes with the cell instead of floating freely on the sheet.
Right, so you can just come over here and do this, and now that picture stays in here. If you move it, it just stays with it. It did a pretty good job of moving it before too, but I don't know. That just kind of makes it stick with that cell. If you drag the cell, it stays with it. So, it's easier for you to work with in Excel than floating images.
So, it makes the spreadsheet easier to manage, but it doesn't change the import story. Access still can't import that image directly and automatically because there's nothing technically in the cell itself to import.
Another thing you could do, and this isn't easy, but it's definitely doable: you could save this as an HTML webpage. What'll happen is, Excel will create an HTML file that you can read because it's a simple plain text file. It will actually export these pictures to a folder and give them a file name, so this might be a better solution for you.
Go to File, then Save As, and then pick "Webpage" (HTML). Click Save. You're going to lose some features, that's fine, keep the format. Yes.
So, it saved this now as an HTML file. If you go to your folder where your spreadsheets are living, you will see the new HTML file. Now, look at this files folder that comes with it: here are all your pictures that got saved out of the spreadsheet - image123. Pay no attention to the HTML file; this thing is pretty much useless at this point.
But inside this folder, you're going to find a sheet1.htm, or whatever the name of that sheet was in your book. If you open this guy up, it's going to open up in your browser. There it is in my web browser. This is an easier format to try to grab in Access than just trying to do it the other way with automation.
If you open this up in Notepad (Edit in Notepad), here's that HTML file in Notepad. HTML files are basically just text. You can read this stuff into Access line by line and find your records. Where are our records? Here, right about here. Here's where it starts. Here's a TR (table row). There's the header row: Time, Custom Rename, Phone, etc. Here's the first record. There's Frodo Baggins, stuck with the Rainfall number. Oh, look at that. There's the image information right there, and the file name.
Now you got the data, you got the name of the file. Now you could put that data into your Access table, save that file name, and as long as you keep it in the right folder, then you got the image information that you need. So I guess I would say this is probably the easiest way to do it.
You have to learn how to read and write text files with Access, which I've got several classes on. In Access Developer 30, I covered classic VB file input/output, reading and writing text files. You'd probably get by with just this stuff in this class. Obviously, you're going to need to know how to parse a file as far as reading it in line by line, looking for where the data starts.
If you have access to the spreadsheet, you could put something in there or just even come in here manually and just chop all this stuff off, select all this, hit delete, boom, there's the data you got to parse through. Each record is now going to be a row, so just look for the TR tag and the /TR tag. Each record is inside of here, there's the time. So it's just a matter of parsing it now.
Like I said, I got lots of classes on this stuff. If you guys want to see me do an example, then let me know. If enough people want to see it, I'll either make a TechHelp video out of it or I'll make another developer lesson where I show you how to do it. So it's definitely doable, just a matter of how much effort you want to put into it.
Oh, and one more thing. I just did a test. I dropped another picture in here because I wanted to see what it would look like if this was still a floating image instead of merged in the cell. If you do that and export it, the images that are in the cells like I showed you come out clean still (here it is right there), but if it's a floating image, it looks like this. See that? It encodes it and tries to stick it in the HTML as binary data. So that's not pretty. Here are the other images down here: there's Aragorn and Gandalf and stuff. So you definitely will need to save all of your images into the cells like I did. Click on this little button here to merge that into the cell. Then you can export them and get clean file names instead of all that mess.
So, I hope that helps. It's one of those things where if it's something that you have to do every day, it's going to be cumbersome. If it's something that you have to do once, there's a solution for it that's not too bad. Hope that helps.
All right, that's going to do it for today. Don't forget, Access Day 2026 is coming up on Friday, March 27th in Redmond, Washington. Save the date. I'll post more information as I get it.
Don't forget to stop by my website and check out what's new. I am always adding new videos, updates, templates, random bits of Access goodness here and there, so it's always worth a quick look to see what you might have missed. My friend and colleague Alex Headley has been releasing some VB.NET stuff, so check that out.
While you're there, don't forget to subscribe to my mailing list so you get notifications every time I release a new video. Don't forget to check out my Captain's Log where I post my thoughts about whatever I happen to be thinking about that day. Sometimes it's about Access, sometimes it's about science, sales, technology, or whatever happens to be on my mind. Recently, I wrote an article about repetition and how it can be good for your brain. I dropped my holiday classic, 'Twas the Night Before Christmas on the bridge. And lots more.
Check out my merch store, get yourself some mouse pads, t-shirts, hats, hoodies, and all that good stuff. Be sure to grab a copy of my Access Beginner 1 book. It's on Amazon. You can get it in printed form and it's available for Kindle.
So that's going to do it. That's your Quick Queries video for today. I hope you learned something. Live long and prosper, my friends. Enjoy your weekend. I'll see you next time.
If you enjoyed this video, hit that thumbs up button right now and give me a like. Also, be sure to subscribe to my channel, which is completely free. Make sure you click that bell icon and select 'All' to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It's a directory I put together personally of Access experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mentioned in the video can be found in the description text below the video. Just click on that 'Show More' link right there. YouTube's pretty good about hiding that, but it's there. Just look for it.
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below you can click on. Did I mention it's completely free? If you like Level 1, Level 2 is just one dollar. That's it. It's free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there's hundreds of them by now. They also get one free beginner class each month, and yes, those are from my full courses. Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos. Plus, you get access to my code vault where I keep tons of different functions and all kinds of source code that I use. Gold members get one free expert class every month after completing the beginner series. Platinum members get all of the previous perks plus they get all of my beginner courses— all of them from every subject, and you get one free advanced or developer class every month after finishing the expert series. You can become a Diamond Sponsor and have your name listed on the sponsor page 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 next time.
Quiz
Q1. Why does Access not import images from Excel spreadsheets automatically?
A. Because images in Excel are floating objects, not actual cell data
B. Because Access only supports importing text data
C. Because Access does not support any kind of image import
D. Because Excel images are encrypted
Q2. What is one workable solution for transferring images from Excel to Access?
A. Export the spreadsheet as an HTML web page and import image file names
B. Simply copy and paste the images into Access
C. Export the Excel file to CSV and import it into Access
D. Use Excel's Save As PDF and import the PDF into Access
Q3. What is Microsoft Dataverse?
A. A managed cloud-based platform for the Power Platform
B. An advanced Access add-on
C. A full-featured relational database like SQL Server
D. A backup tool for Access databases
Q4. Which is a reason serious Access developers may find Dataverse restrictive?
A. Lack of true TSQL and advanced database features
B. It requires programming in C++
C. Dataverse is not cloud-based
D. It only runs on Mac computers
Q5. What remains the natural next step for Access developers wanting scalability and professional features?
A. Migrating to SQL Server
B. Upgrading to Excel Premium
C. Using Google Sheets
D. Switching to PowerPoint
Q6. Is using class modules essential for most Access projects?
A. No, for most business databases, procedural code is sufficient
B. Yes, every Access database should use class modules
C. Only for databases under 10 records
D. Only if the developer knows C#
Q7. What is the main benefit of anchoring images "in cell" in Excel?
A. Images move and resize with the cell, making sheets easier to manage
B. It automatically makes them importable to Access
C. It changes them into real cell data
D. It hides them from view
Q8. When exporting an Excel sheet as HTML, what happens to the images?
A. They are saved separately in a folder with file names
B. They are embedded in the HTML as audio files
C. They do not export at all
D. They are deleted
Q9. What is the best way to handle a one-to-many relationship in Access for things like patients and visits or customers and orders?
A. Create related tables and link them using one-to-many relationships
B. Store all information in one big table
C. Store images in Access as OLE objects
D. Create a separate database for each visit or order
Q10. Why might videos on YouTube appear blurry compared to those on the AccessLearningZone website?
A. YouTube compresses videos, reducing quality
B. YouTube does not allow zooming
C. The website uses more advanced cameras
D. YouTube only supports audio files
Q11. What is a reason you might need to use Excel VBA or automation in the image import process?
A. To loop through rows, save images, and match them with data
B. To compress images for smaller file size
C. To convert images to PDF format automatically
D. To change Excel images to text values
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A
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
In today's Quick Queries video from Access Learning Zone, I'm addressing some of the common questions and frustrations that come up when working with Microsoft Access and Excel, particularly around importing images, plus a variety of other topics from the community.
Let me start with the issue of Excel spreadsheets that contain pictures. If you've tried moving those into Access and it seems like Access completely ignores the images, you're not imagining things. This is a limitation in how the two programs handle data. Access expects actual field values, while in Excel, most images are objects that just float on top of the sheet rather than living in a cell. So, when you try to import your spreadsheet into Access, the cell data transfers, but the images do not. They're simply not recognized as part of the table you're importing.
There are workarounds, but none are perfectly seamless. If you absolutely have to migrate a large batch of images, one approach is to use Excel VBA to write automation code that finds each image, exports it to a folder, and creates a mapping file or record that matches the image file to the row it came from. You can then pull those file names into Access and store them as file paths in your database. However, this process can get tricky, especially if your images are not perfectly lined up with their intended rows.
A more manageable option, especially if you have a modest number of images, is to export the Excel file as a web page (HTML). When you do this, Excel will export the data and create a folder with all your images saved as separate files. The HTML table will include links to these images. At that point, you can parse the HTML with Access to extract each record and its associated image file name, and bring that data into your database. This approach is cleaner and usually works better than attempting to automate everything via VBA. You'll just need to know how to read and parse text files in Access, which I've covered in other classes.
Another situation some people mention is the "Place in cell" option in Excel. Using this keeps the picture attached to a specific cell, which certainly makes things easier to manage in Excel, but for importing into Access, it doesn't make a meaningful difference. Access still won't find an actual image value in the cell, so importing those pictures still isn't automatic.
If you do try exporting as HTML, ensure that your images are placed "in cells" instead of floating, since floating images sometimes get encoded as raw binary data in the HTML, making it very hard to extract them cleanly. With images in cells, the export will be straightforward with clearly named image files, which you can then reference from Access.
This is one of those scenarios where database best practices really matter. Storing image file paths in your Access tables and keeping the images in a folder is generally much better than trying to store images directly inside the database.
Now onto a different question: the Dataverse button that users are seeing in newer versions of Access under the External Data tab. You did not see this feature in my older videos because Dataverse (formerly known as the Common Data Service) was released several years after those recordings. Dataverse is a managed cloud platform that connects easily with Microsoft's Power Platform, designed mostly for non-developers building quick apps inside the Microsoft ecosystem. It's handy for lightweight solutions, but for serious database work that needs performance and scalability, SQL Server is far superior. Dataverse is limited in areas like TSQL support, query optimization, and overall control, so advanced Access developers still rely on SQL Server as the logical next step for back-end data.
I plan on releasing a new course focusing on helping Access developers migrate their back ends to SQL Server, keeping Access as the front end. Dataverse lessons may be covered later, but the priority is on solutions that offer greater performance, flexibility, and long-term control.
Now, another question came up about using class modules in Access. While class modules allow for more advanced and organized programming (and can mimic object-oriented logic), they're almost never necessary for the majority of Access projects. In my own years of consulting, I barely ever had a real business need to reach for class modules, even in large-scale systems. They can make some code more organized but often add unnecessary complexity for procedural-style solutions, especially in something like a security database or typical business applications. If you're experienced with object-oriented programming and enjoy it, use them by all means. If not, sticking to simpler approaches is perfectly acceptable, and in most cases, easier to maintain.
Questions also come up from time to time about older videos not being zoomed in enough to clearly see code or formulas on the screen. In the past, I didn't always use zoom features, but I make an effort to do so in newer videos. YouTube sometimes further compresses videos for streaming, so if clarity is ever an issue, I recommend watching through my website, where you can see uncompressed versions and even download full-quality copies for even better visibility.
On the topic of database design, I was recently asked how to build forms for daily registration and payments for patients or services. The industry doesn't matter much - whether it's patients and visits, customers and orders, or logging food or workouts, the critical design is a one-to-many relationship. One record (like a patient or customer) connects to many related transactions (like visits or orders). The underlying structure is universal. Reference tables (like services or products) hold your prices so those can be automatically linked and tallied in each visit or order.
Whenever you have repeated transactional events, you don't want to store the price in the same place every time. Instead, you keep a separate table for your items or services so the system always pulls the current correct price or info. This is a core database principle that makes your system flexible and maintainable, and it's covered thoroughly in my invoicing, order entry, and fitness series videos.
For anyone interested in keeping up with the latest videos, updates, templates, or to get involved with my Access Developer Network, visit my website. You'll also find articles, my Captain's Log, merchandise like books and shirts, and opportunities to subscribe to my free courses or join as a channel member for access to extended tutorials, sample databases, and exclusive content.
If you want more details on anything discussed here or step-by-step instructions, a complete video tutorial is available on my website at the link below.
Live long and prosper, my friends.
Topic List
Why Excel images are not imported into Access
Explanation of how images are stored in Excel
The difference between floating images and cell data in Excel
Limitations of importing Excel images into Access
Automating Excel image export with VBA
Anchoring images to cells in Excel and its effects
Workaround by saving Excel as HTML to extract images
Parsing HTML to retrieve image file names for Access
Using Access VBA to read and parse text files
Associating exported images with Access records
Handling floating vs. in-cell images during export
Best practices for one-time or bulk migration of images
Recommendations for handling a small vs. large number of images
Article
If you have Excel spreadsheets that contain images and you have tried to import these into Microsoft Access, only to find that Access ignores the pictures, you are not imagining things. This is a common problem, and there are ways to work around it, although not perfectly seamlessly.
The main issue is that Excel and Access handle data in fundamentally different ways. Excel lets you add images as floating objects on a worksheet. These images are not embedded in the actual cell data. Even if it looks like each picture lines up with a particular row, Excel is simply layering these image objects on top of the grid. When you cut or move rows and columns, Excel may try to move the pictures with them, but technically the images are floating above the cells, not residing "in" the cells themselves.
Access, in contrast, is built around the concept of records and fields—structured, relational data. When you import a spreadsheet, Access expects each cell value to correspond to a data value for the table. Images floating on an Excel sheet do not translate into cell content, so when you try to import from Excel, Access brings in any text or number data, but ignores any images.
So, can you automate the import of those images? Not directly and not automatically, because the pictures are not part of the cell data. However, there are some workaround techniques that can help you migrate those images together with your data into Access.
One VBA-based approach in Excel involves writing code to loop through each row, find the image sitting on top of the row, and then export that picture as a file. For each image, you can save it to a folder on your computer and write down its file path. Then, as you process your data, you can create a record in a text or CSV file that pairs the original data with the exported image file name. Later, when importing into Access, you can store the data plus the image path, allowing you to display the image in Access (by referring to the image file, not embedding it). This solution requires significant VBA coding and may not be perfect because if images are not perfectly aligned with rows, your code may not match them up correctly.
For most small projects, it is much easier and less error-prone to save each image manually, then record its file name with the appropriate data before importing into Access, especially if you have only a few or maybe up to a hundred images.
Excel now offers a "Place in cell" feature when you right-click a picture and select that option. This helps keep images anchored to specific cells so that if you move or resize the cell, the image moves with it instead of floating arbitrarily on the sheet. It's a useful Excel feature for managing images, but it's important to realize that "placing in cell" does not actually make the image cell content — it just anchors the floating image more conveniently. Access still will not recognize it as data during import.
There's another workaround that is often easier, particularly for extracting many images at once. You can save your Excel spreadsheet as an HTML page. When you select "File," then "Save As," choose "Webpage" or "HTML" as the file type. When you do this, Excel creates an HTML file and a companion folder containing all images from the sheet, assigning each a file name. The HTML file itself will reference those images within an HTML table.
Open the resulting HTML file in Notepad or any text editor and you will see that each row corresponds to a <TR> tag, and each image is specified by an <IMG SRC="filename"> tag. Your data, such as names or other fields, will appear as text between table cell (<TD>) tags. You can read this HTML file line by line in VBA within Access to extract the data and image file names, then insert them into your Access tables as needed. Make sure to keep the image files in the referenced folder so that the paths remain valid.
If any images remain as floating objects on the spreadsheet — not placed in cells — Excel may export some of them directly as images, but others it might embed inside the HTML file as encoded binary data, making extraction messy. So, before exporting to HTML, make sure all your images are "placed in cell" to ensure clean extraction as separate files.
Reading and parsing a file like this in Access is not very difficult once you know how. You can use classic VBA file I/O techniques to open the HTML file, scan for the tags that indicate a new row, extract the cell values, and grab the <IMG> tag's SRC attribute for the image filename. You can then build Access records holding both your data and file paths to the images.
Here's a simple conceptual snippet for reading a file in VBA:
Open "C:\\Path\\To\\File.html" For Input As #1
Do While Not EOF(1)
Line Input #1, strLine
' Parse the strLine for data and image filenames here
Loop
Close #1
You would need to enhance this with code that understands where the actual data begins and ends, and code to pull apart the contents between the <TD> tags and extract filenames from <IMG SRC="filename">. Once you have the data extracted, you can build your Access records, perhaps storing the image path in a Text field in your table, then displaying the image using an Image Control with its ControlSource bound to that path.
In summary, the limitation is that Excel images are not part of the grid data and Access imports only cell data. Solutions involve either using VBA to process the Excel file and images manually, or exporting the spreadsheet as HTML to extract all images and data together, which you can then parse and import into Access. For large numbers of images this can save you a huge amount of time. For smaller spreadsheets, a manual process might be just as quick. Familiarity with reading and working with text files in Access will be helpful for the HTML export method. With these strategies, you can work around the limitations and migrate both your data and images from Excel into Access efficiently.
|
