VBA code (Macro) to select, copy and paste web data into my excel spreadsheet

[gsdanger]

Good Day all,

I hope someone on this great site can assist me with my question
I want to copy and paste a web page from a web site. The web site contains two levels of information.

The first level is the Location (example - Sydney, Melbourne, Perth...etc).
The second level (within each level1 location) has up to 12 dedicated pages (call them site 1, site 2, site 3, etc…).

I want a macro that asks the user for the Location, then the site number.

With this information, the code goes to the web site, locates the requested web page (location and site number), then copies and pastes the data into my excel 2019 spreadsheet.
The data collected is not dynamic and will be updated at midnight each day.

I’m sure this is not that hard, however, I’m not up to speed with VBA or web scraping, Hence I’m asking the excel guru guys (that’s you guys!!..lol) for assistance.

Please assist is possible.

Regards....gsdanger.

 

[Saurabhj]

Hi gsdanger,

Please share the website URL. Without that web scraping is not possible.

Regards,
Saurabh

 

[gsdanger]

Hi Saurabhj,
Thanks for your prompt reply..
I am an Australian horse racing fan that likes to analyze the form through downloading the horses' form into excel and assess their winning prospects.
the url's of the web site is as follows:
for the Location it's: www.skyracing.com.au/tab/form/index.php
This gives me the meeting locations (example- a couple of today's race meeting locations - Orange, Stoney Creek etc...).
the url for each race in that area is:
www.skyracing.com.au/tab/form/summary.php?meetingid=260969&raceid=2387648&plusday=0
(I listed these as Site 1, Site 2.. etc in my previous post).
This gives me the form for each horse in each race.
Naturally, the meetingid and raceid change for each day.
Note: I need the form to be expanded prior to downloading, to give me the detailed form for each horse.
(Under the word Distance, on the left is a plus sign. This expands all the form for all the horses in this race)

I hope you can assist me with this.

Kind regard...gsdanger

 

[Saurabhj]

Hi gsdanger,

I am getting "Access Denied" error when trying to access the site.

Sorry, unable to open it.

Regards,
Saurabh

 

[gsdanger]

Hi

Saurabhj​

That is strange, as the web site is not password protected or anything?
I don't have any problems accessing the sites, however if you cannot access them, then that's it.
Thanks for trying for me.

Kind Regards...gsdanger

 

[gsdanger]

Hi Again Saurabhj,

Further to my recent posts, and your response re: “Access denied” on both web sites.

I can access both sites, so if you can educate me on how do I get VBA code to open a selected meeting page, and then the race details.

I can already copy and paste a race detail into my spreadsheet. That is no problem, however, if you can give me some direction as to how I set up my VBA code to be able to select the location and then the race.

Example: Today’s Meeting is: _____________ Race number is: ___________

I can fiddle with the code after this, and hopefully get everything working.

Appreciate you input.



Kind Regards….gsdanger

 

[Saurabhj]

Hello gsdanger,

I want to help but without looking into Site and HTML code of page, it's not possible. Meanwhile writing some code to start the code.

'This program requires following references. In Tools -> References, select :
'1 Microsoft Internet Controls
'2. Microsoft HTML Object Library

Sub web_scrapping()
    On Error Resume Next
    
    Dim HTMLDoc As New HTMLDocument
    Dim ieBrowser As New InternetExplorer
      
    'To open and show Internet Explorer
    ieBrowser.Visible = True
    
    'To Open website in Internet Explorer
    ieBrowser.navigate "[URL='http://www.skyracing.com.au/tab/form/index.php']www.skyracing.com.au/tab/form/index.php[/URL]"
    
    Do
    ' Wait till the Browser is loaded
    Loop Until ieBrowser.readyState = READYSTATE_COMPLETE
    
    Set HTMLDoc = ieBrowser.document
      
    'Three methods to do web scrappings -
        'A. getElementByID
        'B. getElementsByClassName
        'C. getElementsByTagName
    
'Code to extract information. We need to check the HTML code of page to extract any information.
    'MsgBox HTMLDoc.getElementsByTagName("td")(0).innerText

    Set ieBrowser = Nothing
    Set HTMLDoc = Nothing
End Sub

 

[gsdanger]

Hello gsdanger,

I want to help but without looking into Site and HTML code of page, it's not possible. Meanwhile writing some code to start the code.

'This program requires following references. In Tools -> References, select :
'1 Microsoft Internet Controls
'2. Microsoft HTML Object Library

Sub web_scrapping()
    On Error Resume Next
   
    Dim HTMLDoc As New HTMLDocument
    Dim ieBrowser As New InternetExplorer
     
    'To open and show Internet Explorer
    ieBrowser.Visible = True
   
    'To Open website in Internet Explorer
    ieBrowser.navigate "[URL='http://www.skyracing.com.au/tab/form/index.php']www.skyracing.com.au/tab/form/index.php[/URL]"
   
    Do
    ' Wait till the Browser is loaded
    Loop Until ieBrowser.readyState = READYSTATE_COMPLETE
   
    Set HTMLDoc = ieBrowser.document
     
    'Three methods to do web scrappings -
        'A. getElementByID
        'B. getElementsByClassName
        'C. getElementsByTagName
   
'Code to extract information. We need to check the HTML code of page to extract any information.
    'MsgBox HTMLDoc.getElementsByTagName("td")(0).innerText

    Set ieBrowser = Nothing
    Set HTMLDoc = Nothing
End Sub

 

[gsdanger]

Hi Saurabhj,
Thanks for this code.
I will have to do some self education to understand the HTML requirements of reading the web site code....(daunting!).
I will be absent for a few days (holidays with the family), however, I will fiddle with this code on my return.

Hey Saurabhj, I know you cannot access the web sites I require...(bummer!!).
Can you use a web site that you have access to, to set up a similar scenario that I have outlined and then post it to me.
Maybe a stock market site where you can use the company code to find the product, then ? use another company code....Just a thought.
That way I might be able to substitute the relevant websites and code to get this working.
I fully understand that this is a huge favor I'm asking you to do, and I will understand it if you are too busy.
That's OK.

Anyway Saurabhj, thanks for you help.
Have a great new years holiday and hopefully 2021 will be better that 2020.

Kind Regards...gsdanger