Finding and copying specific text from a website, using a URL.

[JayBurn]

Afternoon Mr Excel and Co.

I have been given a spreadsheet with a list of nearly 800 URLs. Each of the URLs is for a specific product.
I have not however been given the product codes, and the URL does not include the product code at all, nor does it give any identifying hints...

So, my question, is there a way to use a Web Query to effectively pull the product code from the website? There is a very specific bit of text before the code, but the websites are not in a table format.

I've been trying to get my head around getting Excel to effectively open the source code of the URL, find the specific text, then copy the following X number of characters, and paste it into Excel.

Is it possible, or do I need to get someone to load every single page and copy the code manually?

Thanks,

Jay

 

[John_w]

Are the URLs all for the same website? If the website doesn't use HTML tables then a web query which imports the entire page to a separate sheet might work. The VBA code then looks for the specific text and copies the product code to the master sheet. If a web query doesn't work then look at IE automation or XMLhttp requests.

 

[JayBurn]

Are the URLs all for the same website? If the website doesn't use HTML tables then a web query which imports the entire page to a separate sheet might work. The VBA code then looks for the specific text and copies the product code to the master sheet. If a web query doesn't work then look at IE automation or XMLhttp requests.

They're all on the same website, but different pages. I've been trying to get a web query to work for it, but it is only pulling in a very small portion of the page, not including the part I need. Using IE automation, I'd have to go to each page individually, which is what I'm trying to avoid doing.
I've not used XMLhttprequests before, I'll do some reading and see if it would help. Is there a way to build XMLhttprequests into Excel?

 

[JayBurn]

I have noticed that the pages have the product code in the page title too, is there a VBA method to query a webpage title without having to load the enitre page through web query?

 

[Kyle123]

Yes, but we'd need to see the source code for the page really. Ideally though a url we can access