Training videos for learning VBA - SCRAPING WEBSITES

Hi Andrew, 

I hope you and your family are keeping safe during these tough times.

First of all, we can’t really thank you enough for videos and another resources you made available in this very informative site. I have really benefited from your videos - and in particular during the current lockdown - (which was a great opportunity for many to improve their overall skills). I have already started using what I have learned to automate many of the tasks related to my work.

With regards to web scrapping videos: there is still two/three things that I can’t find: 

1. Filling an auto complete/auto select text. For example : entering a city name in Booking.com ( data entered should also be verified/matches from the auto- complete saved city , which is normally the first city they appear in top, example, putting London > London, then Greater London, United Kingdom option would be the correct one). As you can see, the data entered needs to be verified / matches first before hitting the search button otherwise an error would occur. 

2. Choosing travel/return data from the pop-up selection board.(this one is a direct choice rather than an auto-complete option like the city choice).

3. Pretty close to 2:  choosing date, month and then year before hiiting go on https://www.imf.org/external/np/fin/data/rms_sdrv.aspx.. (I can then parse the result in Excel using one of the method you explained in your videos) .  Note please that I was able to use URL concatenation on other section on the site, but this one as you can see requires to use the drop down method instead. 

Many thanks once again and looking forward to hearing from you

Stay safe

Marklah

Hi Andrew,

I'm Kartick from India. I've learned a lot of things about VBA from your Videos on YouTube. You're absolutely genius. But, recently I've faced a problem. Can you please give a solution on how should I count the number of tables on a certain Webpage? For your information, I tried the below code. But, instead of giving me the count of Total no of Tables on that Webpage, it's giving the count of Total no of Rows of all Tables on that page. Please, Andrew, give me a solution.  I'm counting on you. I'm giving my code below... ------------

Sub Count_Tables_on_a_Webpage ()

Dim IE as SHDocVw.InternetExplorer

Set IE = New ShDocVw.InternetExplorer

IE.Visible = True

IE.Navigate "https://www.wsj.com/market-data/quotes/AAPL/financials/annual/balance-sheet"

Do While IE.ReadyState <> ReadyState_Complete

Loop

Dim Doc As MSHTML.HTMLDocument

Set Doc = IE.Document

Dim TBL as MSHTML.IHTMLElement

Dim TBLs as _ MSHTML.IHTMLElementCollection

Set TBLs = Doc.getElementsbyTagName("Table")

MsgBox TBLs.Length

End Sub

Hi  i was wondering if you have anything on scraping websites using Chrome and Excel VBA?

Regards

Colin

Hello Andrew,

Thanks alot for the videos, i have a problem when i login to the a website a new pop up window will be opened and all the required content to be gathered will be there in the new window. Please help me out how to activate the sencod window and get data from the newly opened window.

Thanks in advance.

Hi Andrew,

First of all, thank you very much for your videos, I've learnt a lot with them.

I have a problem with this web page

https://www.cenace.gob.mx/SIM/VISTA/REPORTES/PreEnergiaSisMEM.aspx

I'm able to web thorught the web, but I can't make click on the checkbox which is near the text "Precios de la Energia". With you videos I was able to change the status of the checkbox from rtUnchecked to rtChecked, but the result is very different than when I click on the web   ..

I think when I click on the web page some code is executed, but I can see how to do it ...

Hi Andrew

Are you able to help on below, I am attempting to scrape the "Bid Price" from a website but get "[object] as a return value.

I'm sure have faulted with element collections, would appreciate you assistance.

Thnak you

Sub Price_from_BB()

Dim XMLPriceRequest As New MSXML2.XMLHTTP60
    Dim HTMLPriceSheet As New MSHTML.HTMLDocument         
    Dim ProductPrice As MSHTML.IHTMLElementCollection   

    XMLPriceRequest.Open "GET", "https://barxis.barcap.com/ZA/10/en/instruments.app?statusId=4,5#/details/350041", False
    XMLPriceRequest.send

    HTMLPriceSheet.body.innerHTML = XMLPriceRequest.responseText

    Set ProductPrice = HTMLPriceSheet.getElementById("buttonSELL350041")

    Sheet1.Range("A1").Value = HTMLPriceSheet
    Sheet1.Range("B1").Value = Now
    Set XMLPriceRequest = Nothing
   
End Sub

Hi Andrew,

I am trying to upload a file to a web page, the following are the steps I followed:

1. Open the web page 

2. Wait for until the page is getting loaded

3. In this webpage I am uploading the file into the first “Upload a File” browser.

4. Get the input element by tag name as “input”

5. Hit the “browse” button, since the paste portion is disabled.

6. Enter the file path in the “Choose File to Upload” window (path is in excel eg :D:\MArgin Discovery (Businness Assurance)\practice2.xlsx )

7. Enter After 5th step, I am not able to enter the file path in the “Choose File to Upload” window, looks like the macro is not supporting for this.

Here is my code:

Sub File_Test()
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim HTMLButtons As MSHTML.IHTMLElementCollection
    Dim HTMLButton As MSHTML.IHTMLElement
    Dim ie As Object

    Set ie = CreateObject("internetexplorer.application")
    ie.Visible = True
    ie.navigate "http://www.htmlquick.com/reference/tags/input-file.html"

    Do While ie.readyState <> READYSTATE_COMPLETE
    Loop

    Set HTMLDoc = ie.document
    Set HTMLButtons = HTMLDoc.getElementsByTagName("input")

    For Each HTMLButton In HTMLButtons
        If HTMLButton.Type = "file" Then
            HTMLButton.Click
            HTMLButton.Value = "C:\Documents\Test\Temp.txt"
            Exit For
        End If
    Next

Kindly help how to enter path name when input type is file.

Thank you

Regards,

Jouhar

 Andrew,

Thanks again for your awesome videos. 

On VBA part 47 video, you show us how to input a value, click the "Go" search button, and scrape contents from queried URL.  I used the same technique to scrape the page after the search button is clicked.  It didn't return value (innertext).

Wonder if you can help me out.

Thanks,

Suntao

Thank you sir for your tutorials

 please help me, i'm trying to run your example code, but it gives me error "access denied",

how can I solve this kind of error?

Viewed Video #47 on youtube.

Any plans to expand on XHR in Excel VBA, especially with session cookies?

Hi Andrew,

Thank you so much for the 3 videos about Web scraping. It did guide me a lot on how to identify HTML code. Your approach is great. I seem unable to find any routine of auto login (username, password) to web site via VBA code.

Greetings,

Dude6571