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

gsdanger

Board Regular
Joined
Jul 12, 2010
Messages
56
Office Version
  1. 2019
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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi gsdanger,

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

Regards,
Saurabh
 

gsdanger

Board Regular
Joined
Jul 12, 2010
Messages
56
Office Version
  1. 2019
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

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi gsdanger,

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

Sorry, unable to open it.

Regards,
Saurabh

1609140279130.png
1609140279130.png
 

gsdanger

Board Regular
Joined
Jul 12, 2010
Messages
56
Office Version
  1. 2019

ADVERTISEMENT

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

Board Regular
Joined
Jul 12, 2010
Messages
56
Office Version
  1. 2019
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

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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.

VBA 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

Board Regular
Joined
Jul 12, 2010
Messages
56
Office Version
  1. 2019
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.

VBA 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

Board Regular
Joined
Jul 12, 2010
Messages
56
Office Version
  1. 2019
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,612
Messages
5,625,839
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top