Getting specific text from webpage.

Hi there!

I was wondering if somebody could help me with the following:

For my work, i've been creating a receipt system for incomming orders in Excel. When we get an order online, we receive it in a online web environment (Google Chrome). From there, we can print the page for our own administration in Google Chrome. But what we want, is that Excel takes the data and puts it in the right tables and cells, so that we can print our own receipts, instead of printing the whole page with a lot of unnecessary information, or even information that we don't want the customer to see.

So how do I do this? We can't edit the website because it's controlled by a different company. I've tried it with 'getElementsByClassName', but with no succes. Also, the text i'm trying to get, is not in a seperate class. The whole page counts as the only class in the page. I've heard about the option "Find" and "FindNext", but I don't know how to use them. What helps, is that everything they ordered always starts with a '#'. I need to get the value after that and put it in the right cell in my sheet.

Here's what I got so far:

Please Login or Register  to view this content.

This gives me an error.


Who can help me? Please keep in mind that I have almost no VBA experience, but I do have programming experience. I used to program games in C# and JavaScript. So this is kinda new to me.


Arnout

Hi,

Click the Data Ribbon menu item then over on the left in the 'Get External Data' select the 'From Web' option and enter the web page and select the appropriate table. It does mean that the web page has to have a suitable table that Excel can link to.

Ah yeah, I know what you're getting at. I already got this with succes, but here where my troubles:

1. I don't know how to do this in VBA (like refreshing and stuff)

2. The data is not always in the same cell. Sometimes it's in B25, but sometimes it could also be in B27 for example. Not sure how to fix this (maybe with the Find and FindNext function since there is a # for every order?)

3. I only need the get the number after the # and place that in sheet 1. How do I recognize the numbers?

Originally Posted by buran

Also, the web interface that you use is just that - interface. The data are stored in a DB. You said you don't control the website, so it's some sort of third-party cloud based order management system/service. You can check if there is API or other access to back-end DB data supported by the service provider

I thought about that too. There is not, or we can't use it.

Originally Posted by Richard Buttrey

First things first. We need to establish whether Excel CAN connect and read the table data before we consider how to automate the process and read the information you need.

What's the URL and which is the table?

The URL we can use for this example is "https://mijn.sitedish.nl/bestelling.php?id=2&src=live" . You need to logon, but it's accessible on our network. The class for this table in HTML is "bestelling bes3":

Please Login or Register  to view this content.

Originally Posted by Richard Buttrey

Unfortunately that web page requires a log in and password which of course I don't have.

Yes, that's what i said. Hang on, I'll download the page and upload it to my own website.

Originally Posted by Richard Buttrey

Unfortunately that web page requires a log in and password which of course I don't have.

This is without all the CSS files and such, so just plain text. But the HTML code is the same. I don't really want this to be too public, so I'll send you a link via PM.

I tried it again, and I got it to work suddenly. Only thing I did is retype some stuff. Weird bug in VBA?

Now that I got the data, I need to put the right numbers in the right cells. I have to use Find right?