Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

Hi

I have a list of Hyperlinks in Column A. These are for a horse racing information website. I usually open each page individually and then copy and paste that information into Excel, which can be labourous at times. I found some code below and added some Sendkey lines to automate this procedure, however it is not working 100% all the time. Sometimes it won't copy the information, even though I put some "wait" time in there. Does anyone know by looking at the code where I am going wrong?

As an example I have listed just 3 hyperlinks: -

http://www.besttipping.co.uk/?action...t&time=14%3A00
http://www.besttipping.co.uk/?action...t&time=14%3A35
http://www.besttipping.co.uk/?action...t&time=15%3A10

Please Login or Register  to view this content.

Regards, Paul

Why not just use a webquery?

Hi Kyle

Each day the url changes, I am not sure how to select the cell and have that new url each day pasted into the address bar in the web query? When I did a normal macro record it put the url in Cell A1 (for today) in the code, but this url will be different tomorrow.

What data is it that you're actually wanting from the page?

To be exact it's the list of Newspaper tips but even with a webquery it will only copy the whole page, which is not a problem. So, it's a simple open the page, copy the information and paste into excel. As I said I took the code above and tried to modify it to suit but for some reason it works sometimes and sometimes it doesn't.

I changed the waiting time from 03 to 10, that gave it time to paste into Excel, guess my PC is slow at the moment

Please Login or Register  to view this content.

It copied and pasted all 28 races today. We'll see how long this lasts LOL

If you do have a more efficient way of doing it I would be really interested

Thanks
Paul

Send keys is very flakey at best

I've just tried this with 20 addresses and it took less than 2 seconds:

Please Login or Register  to view this content.

It puts the data in sheet 2, give it a whirl and see what you think

Thank you for your help kyle...I have just tried it but it is stuck at this line.

Please Login or Register  to view this content.

I copied the code and ran it but it said "run-time error 424"

I created a new book and it has Sheet1, Sheet2 and Sheet 3 worksheets

Any ideas why?

I'm going to take a guess and say that your Excel installation language isn't English.

Try this:

Please Login or Register  to view this content.

Unbelievable...I am BLOWN AWAY Kyle !!! That is why I use Sendkeys, I would not have known 1 line of what you did..it did all 28 of todays races in like 5 seconds...amazing !
Now I just have to work out how to grab all the results too LOL !
Thanks again Kyle
Regards
Paul

P.S. It really is all above my head but how did you know what the list/table was, is that information in the source code or something? I ask because maybe I could go and grab results page information, if only I know what to look for LOL

Glad it helped

Yes, all the data is in the source code of the page. There are only 3 tables on the page, one with the race tracks, one with the times and one with the tips.

There is only one row in the first two tables and so we can get the selected course and time quite easily.

The final table is the table with the tips in, using the htmlFile object, we can loop through this like a regular excel table - it's then just a case of putting the data in the right place

Well that is brilliant...to get results would be a kettle of fish but what you just did will save me a lot of time...thank you for your explanation too

I have to ask the question Kyle, how do you modify the code for the follwing url?

http://www.sportinglife.com/racing/r...-maiden-stakes

I am only interested in the actual list of results, that is between the title "Full Result" to the title "Betting Breakdwon" i.e. 1st until whatever is last, in this instance 9th

Is it easy to convert the code, I wouldn't want to put you out too much

Regards
Paul

Like this:

Please Login or Register  to view this content.

I created a new module and it conflicted with the previous one. I temporarily deleted the existing and replaced it with the new code yet get another error:

Please Login or Register  to view this content.

Any clues?

The name of the function isn't GetTable, it's GetTableSportingLife

That worked...and it has literally blown me away !!!! Fantastic...!!!

Now all I need Sporting Life to do is put a standard structure to their urls, as it changes considerably each day with some sode of number reference...once I work that out I then I'm cooking

Amazing Kyle..thank you again

Glad it's working

It's working brilliantly Kyle...I managed to see where the url's were in the source code, and with a bit of macro magic can create the hyperlinks automatically and grab all the results almost instantly, which is amazing.

However, I'm having problems with the days' cards as I use a different site but Sporting Life provides a more detailed version. I would love to use a similar "grab" procedure if possible? I've tried working out what you did with your code and what you changed but to be honest it's well above my head...I don't know what to change on the code to grab the following url link, would you be able to help again?????

...here is the link for a typical racecard

http://www.sportinglife.com/racing/r...selling-stakes

Regards
Paul