+ Reply to Thread
Results 1 to 20 of 20

Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

  1. #1
    Registered User
    Join Date
    09-13-2012
    Location
    Luxembourg
    MS-Off Ver
    Excel 2010
    Posts
    17

    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: -

    Please Login or Register  to view this content.
    Regards, Paul

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    Why not just use a webquery?

  3. #3
    Registered User
    Join Date
    09-13-2012
    Location
    Luxembourg
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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.

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

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

  5. #5
    Registered User
    Join Date
    09-13-2012
    Location
    Luxembourg
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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.

  6. #6
    Registered User
    Join Date
    09-13-2012
    Location
    Luxembourg
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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

  8. #8
    Registered User
    Join Date
    09-13-2012
    Location
    Luxembourg
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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?

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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.

  10. #10
    Registered User
    Join Date
    09-13-2012
    Location
    Luxembourg
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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

  11. #11
    Registered User
    Join Date
    09-13-2012
    Location
    Luxembourg
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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

  12. #12
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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

  13. #13
    Registered User
    Join Date
    09-13-2012
    Location
    Luxembourg
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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

  14. #14
    Registered User
    Join Date
    09-13-2012
    Location
    Luxembourg
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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

  15. #15
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    Like this:
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    09-13-2012
    Location
    Luxembourg
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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?

  17. #17
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

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

  18. #18
    Registered User
    Join Date
    09-13-2012
    Location
    Luxembourg
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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

  19. #19
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    Glad it's working

  20. #20
    Registered User
    Join Date
    09-13-2012
    Location
    Luxembourg
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Multiple Hyperlinks To Open Web Pages and Copy Information Into Excel

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1