Copy Web Page Data 2
By Richard Rost
32 days ago
Copy Web Page Data From Amazon & Walmart Part 2
In this video, we continue our experiment on copying data from a web page into Microsoft Access without using APIs or advanced browser automation tools. I'll show you how to extract product details directly from Amazon and discuss some of the challenges with large commercial websites compared to smaller ones. We'll talk about using the Windows clipboard, handling window activation for sites like Amazon and Walmart, and using basic string functions to locate the data you need. This is part 2.
Members
In the extended cut, we will cover three things: a better technique for reading clipboard data instead of relying on paste, an improved method for activating application windows using partial string matches, and a trick for extracting specific data such as prices from the copied web page text. I will also show you how I built a sample database that stores product information and prices from both Amazon and Walmart.
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!
Code Vault
Gold Members Only:
Prerequisites
Recommended Courses
Keywords
TechHelp Access, pulling data from web page, clipboard tool, string functions, app activate, error handling, reading clipboard data, send keys, partial string window activation, product price extraction, storing product data, table population, website data scraping, extracting visible browser data, workaround for missing APIs, price tracking
Intro In this video, we continue our experiment on copying data from a web page into Microsoft Access without using APIs or advanced browser automation tools. I'll show you how to extract product details directly from Amazon and discuss some of the challenges with large commercial websites compared to smaller ones. We'll talk about using the Windows clipboard, handling window activation for sites like Amazon and Walmart, and using basic string functions to locate the data you need. This is part 2.Transcript Today is part two of our little experiment where we're pulling data straight off a web page and into Microsoft Access without fancy APIs or background browser tricks. We're just grabbing what you see on the screen.
Yesterday in part one, we talked about why reading data from websites doesn't always work the way you expect. I showed you a simple, reliable technique using a real-world example from my own website.
Today we're going to pick up where we left off and try the same approach on Amazon and see how it behaves in the real world. My website is real world too, but like a big site. Mine's tiny compared to Amazon.
We now rejoin the program already in progress. For those of you who are old like me, remember when this used to happen? There would be an interruption, like the president would come on or a ball game or something. They'd be like, well, you're not going to watch the first half of it because we interrupted it for something, but here you go.
Alright, here we go. Here's a product on Amazon we can purchase. Here's my book on Amazon. Go get yourself a copy. And yes, I have purchased some copies. I got a couple on my shelf. Here's the link you want right there.
So we'll copy this. We'll switch back to our database. Let's go back into here. I'm going to save all this stuff. I'm going to save this for you guys. So I'm going to copy this and I'll paste it down here.
And we'll do get from 599 CD and we're just going to rem this stuff out. There's a button you can put on your toolbar to do all that. I did a video on it. I don't have it on my toolbar though. I almost never do this.
We're going to put that link that we copied here, which I've got my clipboard tool so I should find it right there. I love that Windows clipboard tool. You can clip and copy multiple things. I think I did a video on that too.
I like to give Amazon a little bit longer to load - maybe a second and a half. Now, app activate. Here's the tricky part. With Amazon and Walmart, sometimes the product name is first on the title bar. Amazon.com does exist on there and you can try loading that. But if it doesn't work, you're going to have to put the name of the product there because that's what they usually feed up there.
Again, it doesn't have to be exact, but it's got to be close enough for Windows to figure it out. We're going to talk about a better solution for the extended cut in just a minute. But since I know that this is the name of the product, I'm just going to copy this product. Usually the beginning of it works just fine.
Where's my code link? Back over here, we're going to switch to this. And then the rest of this should all be roughly the same.
I am going to close the browser window, save this, reopen that. So my browser is open. If you're trying to do this with a website that you have to be logged into, log into it - get into your bank or your credit card company, whatever you want to pull the data off of. Just make sure it's logged in and your browser will open up another tab. That should be fine.
Here we go. Let's see if this works. And click. And there we go. And boom. Paste it right in there.
And there it is. You'll find the prices in here somewhere. Now, how do you go about actually getting the price? So you're going to have to find it. You're going to have to do a little digging and figure it out.
I'm going to talk about a solution for that in the extended cut for the members, but this is just some string manipulation. See, it's in here. There it is. There's the paperback price. You're going to have to do a little finding. Use some string functions. I got plenty of videos on my website that cover the different string functions.
All kinds of different string functions you can use: left, right, mid, in string, to find stuff. So go watch this video to learn more.
Oh, and while I got your attention real quick, if this video is helping you out, if you like this kind of stuff, hit like and subscribe and make sure you click the bell so you know when I release new stuff. It really helps my channel out.
I'm just curious. Sometimes you can get away with just pulling it in based on... obviously, the goal here is you're going to want to store all of this in a table in your database, all your products and stuff.
Then you do the same thing with the Walmart version. In fact, in the extended cut for the members, we're going to show you a little bit of how to do both of that.
I just want to see if it will find Amazon in the window. Let's see, it might not. Let's try it. Launched and oh, it did it. Let's see. Now, it launched it but it can't find it. Let's see, let's go back to the access window here. Sometimes you got to go back to...it will give you an invalid procedure call or argument if it can't find that window. That's something we're going to talk about in the extended cut.
I've got a better function that I'm going to go over with the members. It's better than app activate because it'll find the window as long as that text is partially in the title bar. See, so Amazon.com is at the end of this guy, right? It's in there, but it's at the end of the title. You can't really see it here, but as long as it's in there, you'll find it.
If not, you'll just have to make sure that you store the title names in your database and you'll have to search for those. You have to put some error handling around this to catch any errors there, but for this case, you'll have to put the name of the product there. Whatever is at the beginning of that string, it's going to be different for every website. Walmart does this too. They put the product name at the beginning.
In the extended cut for the members, we're going to do three things. First, I'm going to show you a better technique for reading clipboard data because trusting paste isn't always the best solution. I've got a custom function that will let you read what's in the clipboard and put it where you want it without having to rely on pasting it.
Copying it, we're just...we're stuck using send keys because there are no...I mean, there are other ways that you can open up a browser and then parse all the objects in it. That's a nightmare though. The method I'm showing you is the easiest way I can think of to copy the data from the web page.
Once we've got it in the clipboard, I've got a better function for reading clipboard data and I've got a better function for app activate that will allow you to app activate with just a partial string as long as the string appears somewhere in the window like Amazon appears anywhere in there, at the end of the string, for example - it'll work.
I'm going to show you a little bit about how I get the actual price out of there. It's a little trick, but I think you're going to enjoy it. But that's only for the members. Silver members and up get access to all of my extended code videos. And of course, gold members get access to the code vault where all this stuff ends up, and you can download these databases that I build and lots of cool stuff.
I'm going to share with you a database that I've been working on that does pretty much the same thing. I got a product table that I started. It's not properly relational yet. It's just a proof of concept. But I put the Amazon product in here and I put the Walmart product in there. Then I pick, like, banana and yogurt. I give it the name and then it goes out, pulls it in, gets the price, and it stores it in the table and it works. And I'm going to show the members how I did this. So that's in the extended cut.
Anyways, to wrap things up, in both of these parts, you saw why pulling data directly from websites doesn't always work the way you expect.
We talked about APIs and background web requests and why they often don't give you the data that you clearly see on the screen. Then we saw a practical real-world workaround. Sometimes when you write something, when you type it up, it doesn't come off the tongue in the same way as when you type it and see it.
The real-world workaround lets Access grab exactly what's visible in the browser, even on modern sites like Amazon or Walmart or anybody really. The big takeaway here is knowing when to use proper tools and knowing when a good enough solution makes more sense. And in this case, I like the good enough solution.
Would I base an enterprise business on it? No. But if you just want to check your grocery prices, this is fine. Or if you want to get vendor prices - I used to have a company, I used to buy hardware from way back when we're talking when the web was in its infancy. They didn't just give me a nice easy spreadsheet with all the pricing updates. I had to go to their website and literally copy and paste stuff. I wish I would have had this technology back then.
This approach isn't fancy. It's not perfect, and it might glitch once in a while, so keep your eye on it. But when other methods fail, this gets the job done and it gives you the data that you need.
If you enjoyed today's video, post a comment down below. Let me know what you thought and how you might use this technique with your own databases.
And that's going to be it for your TechHelp video for today, brought to you by AccessLearningZone.com. I hope you learned something. Live long and prosper, my friends.
I'll see you next time. And members, I will see you in the extended cut.Quiz Q1. What is the main technique demonstrated in the video for importing data from a website into Microsoft Access?
A. Using fancy APIs and web scraping libraries
B. Copying the visible content from a web page and pasting it into Access
C. Automating Chrome with background browser extensions
D. Downloading CSV data files from the website
Q2. When using the demonstrated technique on Amazon, why might you need to customize the window activation step?
A. Because Amazon uses encrypted URLs
B. Because the product name appears at the start of the window title
C. Because Access cannot interact with web browsers
D. Because Amazon blocks all clipboard access
Q3. What is recommended if you are trying to pull data from a website that requires login credentials?
A. Log in first before attempting to grab the data
B. Use a different web browser
C. Avoid such websites as they are inaccessible
D. Only use APIs to access such content
Q4. Which Microsoft Access string functions were suggested for extracting specific data like a product price from the webpage text?
A. DateDiff, Replace, Split
B. DLookup, Sum, Format
C. Left, Right, Mid, InStr
D. UCase, Now, Chr
Q5. What is a potential error you might encounter if your window activation code cannot find the correct browser window?
A. Type mismatch error
B. Invalid procedure call or argument
C. Subscript out of range
D. Out of memory error
Q6. Why does the presenter caution that this solution may not be suitable for enterprise-level use?
A. It depends on unreliable third-party APIs
B. It is too slow for large datasets
C. It is not perfectly robust and may occasionally glitch
D. It requires advanced programming knowledge
Q7. What is one of the improvements discussed for members in the extended cut of the video?
A. How to write a custom browser extension
B. A better way to read clipboard data directly
C. Automating Amazon order placement
D. Building a NoSQL backend for Access
Q8. What does the real-world workaround demonstrated in the video actually allow you to do?
A. Scrape hidden data from web APIs
B. Get the exact data visible on modern sites by copying and pasting
C. Download proprietary price lists from vendors
D. Hack the website's backend for unlimited access
Q9. According to the video, why might copying and pasting web data sometimes be a better solution than using APIs or automated scraping?
A. It is faster and less prone to being blocked
B. It is officially supported by all websites
C. It captures exactly what the user sees, even if APIs do not expose it
D. It provides real-time synchronization
Q10. What is one of the main cautions about using this copy-paste approach as discussed at the end of the video?
A. It always fails on modern browsers
B. It can occasionally glitch, so you need to monitor the process
C. It is illegal to use on e-commerce sites
D. It only works on Mac OS systems
Answers: 1-B; 2-B; 3-A; 4-C; 5-B; 6-C; 7-B; 8-B; 9-C; 10-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 video from Access Learning Zone is part two of our experiment focused on pulling data directly from web pages into Microsoft Access without using complex APIs or browser automation tricks. We are simply capturing what is displayed right on the screen.
In part one, I explained some of the challenges you might face when trying to read data from websites. We explored a basic and reliable approach, applying it to a real-world example using my own website.
Today, we are moving forward by testing this same method with a much larger site, Amazon, to see how it performs there. While my website provides a real-world scenario, it's much smaller in scale compared to Amazon.
To pick up where we left off, imagine tuning back into a program after an unexpected break, just like those old interruptions during TV broadcasts.
Let's use my book as an example product on Amazon. I do recommend getting yourself a copy. After copying the link to the book, I return to the database and get things set up for you by pasting the link into the appropriate spot.
The process here involves a few steps you might recognize. I mention that if you ever want to comment out code quickly, there are toolbar buttons in Access for that purpose, and I've even covered this in a previous video. Now, back to the example at hand. I'll paste the Amazon book link, using the Windows clipboard tool for convenience, which lets you access multiple recent clipboard entries. It's a handy Windows feature I've covered elsewhere as well.
Because Amazon pages can take a little longer to load, I like to give it about a second and a half before proceeding. The next point to consider is how AppActivate works. For sites like Amazon and Walmart, sometimes the product name appears first in the window's title bar, not the site name. Although "Amazon.com" will still appear somewhere, it may not always be enough to search just for that. So, you'll usually want to use the start of the product name, since that's what is typically displayed.
The text doesn't have to match exactly, but it has to be close enough for Windows to recognize the window. I'll be discussing a more robust, error-tolerant solution for this in the extended cut, but for now, I simply use the product's title to activate the correct browser window.
Before running the process, I make sure the browser is open and logged in, which is especially important if you are trying to access a site that requires authentication, like your bank or credit card provider. Just make sure you're logged in, as a new tab will open for the process and that's all you need.
Upon executing the process in Access, I can easily paste the data from the browser into the database. Once the data is in Access, you need to locate the specific information you want, like the price. This part does require some manual searching and a bit of string manipulation to extract the price from the block of text you just grabbed. For this, you can use string functions such as Left, Right, Mid, and InStr. I have additional videos on my website that go into detail about these functions.
If you want to make sure you don't miss any future tips, be sure to hit like and subscribe to the channel and turn on notifications. It really does help support what I do.
The ultimate goal here is to be able to store this extracted data, such as product prices, in a table within your Access database. You can use the same method for other large sites, such as Walmart. In fact, in the extended cut for members, I'll show you how to tackle both Amazon and Walmart products with this technique.
While testing, I also wanted to see if I could activate the Amazon window just by searching for "Amazon" in the title. Sometimes it works, but not always. If AppActivate can't find the window, you might get an "invalid procedure call or argument" error. We'll be going over error handling and a better approach for activating browser windows in the extended cut. I've got a custom function that improves on AppActivate by finding the window if any part of the string, like "Amazon.com," appears anywhere in the title bar, not just at the start.
If this does not work, you may need to store the relevant title bar text in your database or put some error checking in place to handle cases when the target window can't be found. Different websites display their page titles differently, so your method might need to be adjusted for each site.
To sum up, in the extended cut for members, I'll be covering three things. First, I'll share a better technique for reading clipboard data that does not rely on pasting. This method uses a VBA function to read the clipboard contents directly and insert them where you need. For copying, we are still limited to using SendKeys, since more direct interaction with most browsers is too complex. The approach I've shown today is the easiest method I know for simply copying visible data.
Second, I'll show my improved AppActivate function, which is more reliable when only part of the window's title is known. And finally, I'll go over a trick for reliably extracting a price out of the captured text, which can be done with some clever string manipulation.
As a bonus in the extended cut, I'll share a sample product table from a database I'm working on. This table is not fully relational yet, but it serves as a proof of concept. It can hold Amazon and Walmart product information, pull the descriptions and prices from each site, and store them in Access for you. I'll walk you through this setup for members, so you can see how it all fits together.
To wrap up, both sessions have shown that pulling data straight from websites may not always work as expected. We have discussed the pitfalls of APIs and background web requests, and why they do not always return the visible data we see in the browser. The approach I demonstrated today provides a practical workaround, allowing you to grab exactly what is visible in your browser, even on sites like Amazon and Walmart.
The main lesson here is knowing which tool is appropriate for each situation and being aware when a quick, "good enough" solution is all you need. For complex business tasks, you might want something more robust, but for occasional data pulls such as checking grocery or vendor prices, this works just fine. Back when I ran a business and had to grab prices from supplier websites, I wish I had an easier option like this.
Although this method is not highly sophisticated and may occasionally have issues, it often succeeds where other solutions do not, giving you the data you need in a pinch.
If you found this video useful, I encourage you to leave a comment below letting me know how you might use these techniques in your own projects.
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 Copying product data from Amazon into Access
Adjusting wait times for web page loading in VBA
Using AppActivate to target browser windows
Identifying product titles in browser title bars
Handling dynamic window titles on Amazon
Error handling with AppActivate in VBA
Locating product pricing within copied web data
Basic string manipulation to extract data
Storing extracted data in Access tables
Limitations and caveats of this extraction methodArticle When it comes to pulling data directly from a website into Microsoft Access, many people expect it to be as simple as grabbing what you see on the screen. However, the reality is more complex, especially when dealing with large sites like Amazon or Walmart. Direct access to data through APIs or background web requests often does not give you exactly the information that appears visibly in the browser. Instead, these methods might offer incomplete data or nothing at all if the site is heavily scripted or protected.
A reliable workaround is to focus on copying exactly what is visible on the screen. The method I use in Microsoft Access involves a blend of VBA code and a bit of automation: open the target webpage in your browser, copy the content you want directly from the web page, and use Access to grab and process this data. This approach works with most websites—even complex ones like Amazon—because it only takes what you can manually select and copy.
Let's walk through how this works with a real Amazon product page. First, open your product page in your preferred browser. Copy the complete URL of that page. In your Access database, you can store this URL and use VBA to interact with your browser. Here's a basic example of the process:
1. Open your browser and navigate to the desired product page.
2. Manually log in if the site requires authentication.
3. Use VBA to activate the browser window and automate a copy-paste routine.
When using VBA, one of the key functions is AppActivate, which brings the browser window to the foreground. Note that large sites like Amazon often display the product name at the beginning of the window title, which you may need to specify in the AppActivate statement. Sometimes "Amazon.com" is in the title, sometimes it is not. For instance, if your Amazon product window title is "My Great Access Book - Amazon.com", then AppActivate will only work if you give it enough of the beginning of the string that uniquely identifies that window.
Here is a very basic sample of the VBA code you might use:
Dim browserWindowTitle As String
browserWindowTitle = "My Great Access Book" ' Start of the Amazon tab's title bar
AppActivate browserWindowTitle
Application.Wait (Now + TimeValue("0:00:01.5")) ' Wait 1.5 seconds to allow the page to load
SendKeys "^a", True ' Select all text on the page
SendKeys "^c", True ' Copy selected text to the clipboard
AppActivate "Microsoft Access"
SendKeys "^v", True ' Paste clipboard contents into the Access form or control
This simple process automates selecting all visible text on the web page, copying it to the clipboard, and pasting it straight into an Access form or text box. It works exactly as if you were doing it manually.
After pasting, you will have a large chunk of the page's text, including product details and price. Extracting the specific data you need, such as the price, means employing basic string manipulation techniques. You can use functions like Left, Right, Mid, or InStr in VBA to locate and extract the price or any other relevant text. For example, if you know the price always appears after the word "Paperback", you can search for that position and then extract the expected price a few characters afterwards.
A few practical notes: sometimes AppActivate will not find the window you want, particularly if the title is not matched exactly. You may need to experiment with different amounts of the title string. Some robust solutions can search for partial window titles, but with this basic method, exactness counts. Additionally, remember to handle errors gracefully in VBA, such as when a window title is not found.
If your goal is to track numerous products, consider creating a table in Access to store the URLs and window titles for each product or vendor. With the approach described above, you can automate copying data from various sites and inputting it into your Access database for further analysis or reporting.
This technique isn't perfect. It is susceptible to occasional glitches—a page might not load, a title might mismatch, or the copy-paste might miss an element, especially if the web page updates its structure. It is not recommended for mission-critical business processes but is perfect for casual or occasional data gathering, such as checking grocery prices or tracking online vendor prices.
Ultimately, this method allows you to bypass the complexities of APIs or web scraping frameworks and just grab on-screen data the way a human could. It is quick, easy, and flexible enough for many practical tasks, though it may require occasional troubleshooting. If you need to extract structured information regularly, take the time to refine your string searching and consider creating helper functions to better manipulate and parse the data you retrieve from the web pages.
In summary, while pulling data into Access from websites like Amazon without an API is inherently a workaround, it is often "good enough" for many real-world tasks. With a bit of VBA, some careful copying, and basic string parsing, you can pull visible web data into your Access projects and automate many tedious copy-and-paste routines. Give it a try on a sample product and experiment with enhancements, such as smarter window activation or more precise data extraction, to tailor the technique to your specific needs. |
