Excel VBA Introduction Part 48 - Web Scraping Across Multiple Pages

[@cliftonsett5214]

I don’t know why the other KZbin videos covering Excel VBA lack the degree of knowledge or expertise that WiseOwl has displayed over and over again, but all I can say is WOW!!! Parts 46 to 48 of the Excel VBA Introduction course are the epitome of excellence in presentation and execution on educational material. I have watched all the WiseOwl videos repeatedly and every time I learn something new and interesting and for that reason I say THANK YOU! I have spent hours looking for KZbin videos to enhance my skills and proficiency in Excel and in my experience WiseOwl and Excel-Is-Fun are the only two KZbin channels that maintain a high level of quality and consistency… There are some KZbin Channels that provide Web Scrapping educational material but none of them provides the details that I have seen displayed in the newly released videos. You guys are awesome! Keep up the good work!!!

[@ogonnaanaekwe2688]

Agree 100 per cent!

[@justincortes2221]

Clifton Sett so true!!!

[@janis1]

Spot On!

[@piotrlorenc1762]

I don't leave comments usually but this time I couldn't resist: "Your videos are truly epic!!!" and Getting Data from Web playlist is simply a mastership. This is exactly what I was looking for recently. THANK YOU for all the knowledge you share in such accessible manner

[@hadireg]

Indeed, the greatest teaching skills and coding knowledge so far! Great job folks, and many thanks for pushing this valuable material for free! RESPECT!

[@shmred5705]

AN AMAZING JOB! i didn't even knew that you can go straight forward to the html document without opening the internet explorer a lot of improvement in performance.. my god i wish I'd learned this before. Thanks for your dedication it's really apreciated. greetings from México.

[@donbogdala5428]

Another day, another lesson learned! Thanks so much! These tutorials are very addictive!

[@KRichardNicola]

This is amazing! Great video. Thank you to the authors!

[@WiseOwlTutorials]

You're very welcome! Thank you for watching!

[@tuyoexcelypowerbi]

Your Videos are amazing and the way you are teaching is really good. Will you prepare any example to use "POST" method? I love this kind of videos when we can use Excel with other different applications. I discovered Web Scraping with Excel time ago and the technique is the most usefull thing that I have seen in much time Thanks for the time that you dedicate to create the videos I know that it takes long...

[@Laczorka]

Hi Andrew, I have been learning Excel VBA for more then 8 months and i wish if i have found your tutorials sooner! Your are explaining in such a detailed manner while giving us Tips, tricks best practices, Great work.! Also i was wondering if there is a great book, tutorial, article about the MSHTML /MSXML library in excel vba. I would like to learn the methods, objects of these libraries in structured way as you have been doing in your videos. Your response and daily work is greatly appreciated, Thank you!

[@AjayKumarparmar]

does anyone or if you know sir, a website has a button and if i get it clicked via vba, it opens a small pop up inside the page and i need to scrape that. I am not able to do it because this pop up is not opened in a new tab but on the same webpage...

[@WiseOwlTutorials]

Hi Ajay, I think you'll find this easier using the Selenium VBA library to work with Google Chrome. We have some videos which explain how to use Selenium starting with this one kzbin.info/www/bejne/fKDbiJasj9-MjM0 and I think you'll find this one helpful as well kzbin.info/www/bejne/n5qUfaucesuMapY I hope it helps!

[@MrSugat11]

HOw to increase session timmout in code ....i have a website where once user logs in using his id, we wanted to extract the data. Also can we do it using xml method from internet

[@Nagarajan1412]

Thanx for all your videos. It is really helpful and thank you so much for that. Can you please make one more video for handling pop up window and how to get the reference of the pop up window?

[@vinay3235]

Thanks for all your videos it is really amazing and unparalleled. Would you please cover the topic of how to take screenshots and paste it in word document. And another video on Iframe handling

[@smartbytesamit]

going through all your vids....superb...Can we get the data from Intranet server where there is a drop down list and various reports are to be downloaded one by one by selecting the reports from drop down? Can it be automated ?

[@nazymbakhyt5347]

Hello, nice video tutorial thank you so much. I have one question: I click link in the website using VBA, but after clicking my IE.Document is still keeping the old HTML file. Could u please help me to figure out this problem?

[@zethlarsson4303]

Thank you for an exelent lesson. English is not my basic language, but it was no problem to understand you. I'v one problem with you examples. The function WorkSheetFunction.EncodeURL do not work for me. Have tryed several suggestions from the web, but no one works. I would be very happy for some advice about what can be wrong in my environment. 64 bit HP computer, Win11, Office 10. The error is: Object does not support the method.

[@jamieread1294]

Thank you for providing such detailed, thoroughly explained, and accurate content! I've been using Excel to interact with websites, and have recently expanded to use VBA in a MS Word context. I came across a curious thing: I accessed the same websites using XMLHTTP requests to create HTMLDocument objects, and then attempted to parse them to retrieve the content I was looking for. I found that often Word did not give me the same results as Excel VBA, even when the code was identical (i.e. using the method "getelementsbytagname" would return different results when run under Word VBA vs Excel VBA. Most often, Excel would return what I'd expect based on inspecting using the browser, whereas Word would omit results.) Further investigation showed that the XMLRequest.responsetext between the two was identical, but when I executed "HTMLDoc.body.innerHTML=XMLRequest.responsetext", the Word HTMLDocument was different than the Excel HTMLDocument. Any theories or explanations for how the same body text can result in two different documents depending on whether the assigment to an HTMLDocument was done under Word vs Excel?

[@WiseOwlTutorials]

Hi Jamie! That's a really curious problem and one for which I have no explanation! If you ever get to the bottom of it I'd love to hear the reason!

[@forzaalgiers2045]

Great Video, great tutorial. Many thanks

[@avijitnandy6662]

You are best. I love Owl

[@watttml649]

Cool tutorials bro

[@sivavuddanti3738]

Hi i need some more information on searching and take screen shots from particular website ( website is java-script web page ). i am trying but its working on normal web page only please help me out from this issue

[@waichan4001]

Is it anyway to scraping Android Application content using python or VBA? That content are not accessible in website. We can only read that content through Android Application. I know using Team Viewer to control Android phone through PC, but I not able to automate it like using VBA or Python. I need to do manually every time.

[@kashifkhanspecial]

Hi Andrew Gould, Thank you so much for these lovely video tutorials, you don’t know how much I learned from your video tutorials, God bless you and God may full fill your desire dreams, If you don’t mind I have some requests to you. 1). Please post a video tutorial about how can we download or post any files from a website through excel VBA web scraping. 2). I loved your excel VBA tutorials about class module, however, one request to you could you please post a video tutorial about how can we use class module in real project, like how can we integrate events, collection, functions, procedure and properties in real-world projects with examples. 3). Please post a video tutorial about how can we play with shapes in excel VBA. Thanks Kashif

[@kashifkhanspecial]

Hi Andrew, Thanks for the reply. 1) Thank you so much. 2) Yes, you are right, I will watch those videos again and will try to incorporate those techniques in the project. 3) Thank you so much. Thanks again for your reply. Thanks Kashif.

[@mattyswanepoel]

Epic video! Very easy to follow, i enjoy them. I wanted to know are the skills we learn in the current and last video easily salable for most webpages, or is it possible to run into some strange HTML pages.

[@igesantos9703]

Hi there, is Microsoft XML 6.0 same for 32-bit? the "GetElementsbyClassName" is not available

[@DM-py7pj]

getElementsbyClassName is not related to the XML lib as far as I know. It is part of Microsoft Internet Controls and Microsoft HTML Object Library. getElementsByTagName is part of XML. Also, byClassName is not available to IE8 or less and to late bound HTMLFile.

[@mustafabakkal8364]

dear david , some websites using java scrip is it possible to log this kind of webpage , can we write a destructer metod to ovecome the java script thx

[@oleksijm]

You are a God.

[@ericnascimento4106]

Why can i only use .getelementbyclassname to the HTMLDoc? What this method have to be the particularity ? Is it the only one ?

[@DM-py7pj]

Not sure what you mean but getElementsByClassName is not restricted only to document node. It is affected by which element type you attempt to use it on and which methods you attempt to chain with. It works for example with developer.mozilla.org/en-US/docs/Web/API/Element/getElementsByClassName and then look at result of Debug.Print ie.Document.getElementById("content").getElementsByClassName("bc-browser-name").Length

[@ashishbaliyan7776]

Hello sir. This is amazing video. But sir i am stuck while dealing with JavaScript content in XMLHTTP GET request. Please guide how to get through that

[@WiseOwlTutorials]

Hi Ashish! If you need to process JavaScript content then you're better off using a method which uses a web browser.

[@tedmontoia]

Your video series is unparalleled. Terrific work! I’m following along line by line and learning quickly. Thank you! Now, just one issue. I get this error “User-defined type not defined” on this line: Dim HTMLDoc As New MSHTML.HTMLDocument What did I do wrong? Ted

[@ryanbein8759]

Make sure you have the reference Library enabled. @3:12 he starts to show you how to enable this.

[@Grahamedmonstone]

Great tutorials, excellent teacher. I am busy with Lesson 48. For some unknown reason I cannot get the ListVideoOnPage to run and so get error 404. Anu suggestions.

[@Grahamedmonstone]

I am going to try use a different computer over the weekend in case the problem us this one. Thanks for your help.

[@bondlavern]

I received the same error too. Status 404 means the file cannot be found on the web. The reason: there were extraneous characters in the code after modifications to the WiseOwl website. But after modifying the code a little, the subroutine works perfectly now. Here is the code: Change one line of code as follows from this: NextURL = WolVidURL & Mid(NextHref, InStr(NextHref, ":") + 9) TO THIS: NextURL = WolVidURL & Mid(NextHref, InStr(NextHref, "/") + 1) I hope this helps.

[@nikospountzas5304]

i get an error MsqBox Compile error: sub or function not defined

[@nikospountzas5304]

dam my blindness thnx a lot Yoda of VBA

[@lucasmansier]

I keep getting an "access denied" message when using your shortcut method (not using IE), even when accessing WiseOwl. Any way around that?

[@morganclendennin1283]

Check that you have the following website URL wiseowl.co.uk/videos/ I was getting the same error because I had http not https

[@jaywilliams3423]

Hi .. Great tutorial ! I was just wondering how do you refresh the data (using vba) after running the code again after there has been changes in the data/web pageThanks in advanceJay

[@jaywilliams3423]

Unfortunately no.. If I update the auction site with a Price change on an item I have listed, rerunning the program shortly after still picks up the old price. However, the changes are picked up when the program is executed again a long time (5+ hrs) after the changes. But this kind of defeats my objective.

[@jaywilliams3423]

Hi there sorry for my late reply and thanks for your information. I tried cookies but this did not work. Unfortunately some sites restrict the amount of api calls that you make which is it bit annoying. The problem might have something to do with that or that it just does not register immediate updates instantaneously .

[@SasiKumar-te1ir]

How to click on pop up box in internet explorer using vba

[@ricardosantos7884]

Hi Andrew, I'm facing some problems since the last video. I can't find the"Children" parameter in the object list. When I try to run the code like you, it doesn't work and an error appear like that: Run-time error '91': Object variable or With block variable not set. I don't understand because my code is exactly like your code. I have put in the references : Microsoft HTML Object Library and Microsoft XML, V6.0. May you please help me with this issue ??? Regards,

[@ricardosantos7884]

Yes, of course. I think that the problem is related to "children" parameter.Because this parameter doesn't appear in the list of objects. Thank you in advance. Follow the code bellow: Option Explicit Sub GetVideoPage() Dim XMLReq As New MSXML2.XMLHTTP60 Dim HTMLDoc As New MSHTML.HTMLDocument Dim VidCatList As MSHTML.IHTMLElement Dim VidCats As MSHTML.IHTMLElementCollection Dim VidCat As MSHTML.IHTMLElement XMLReq.Open "GET", "www.wiseowl.co.uk/videos/", False XMLReq.send If XMLReq.Status 200 Then MsgBox "Problem" & vbNewLine & XMLReq.Status & "-" & XMLReq.statusText Exit Sub End If HTMLDoc.body.innerHTML = XMLReq.responseText Set VidCatList = HTMLDoc.getElementsByClassName("woMenuList")(0) Set VidCats = VidCats.Children Debug.Print VidCats.Length For Each VidCat In VidCats Debug.Print VidCat.tagName, VidCat.innerText Next VidCat End Sub

[@ricardosantos7884]

Hi Andrew, How's going? Thank you man !!! You nailed it ! Your channel is the best one by far. Regards.

[@4thehell]

good tutorial, how to use it if web must login first

[@jamesduffy318]

Is it possible to use the bstrUser and bstrPassword features of the GET method for XML.Req? If so, could you do a video or provide some guidance on that? I am having a similar problem, and, for the moment, a good solution is eluding me. I suspect, however, that many people must be interested in getting data from sites that require user login. Thank you in advance for your consideration, and, more important for your excellent series of videos. They have enormously expanded my knowledge and understanding of vba coding.

[@JohnFromKentucky]

Simply awesome!

[@dylanjones2682]

Hello! I have found your videos hugely helpful. I am having one issue that I cannot seem to figure out. I have been using the IE browser method you cover in the first half of the previous video. In order to access the site I would like to pull data from, I need to log into 2 different websites. For the first website, I am able to set htmldoc as the IE.document, and input my username and password via the "getelementsbyname" method. However, when I do the same for the second website, I get a "Runtime Error '91': Object variable or With block variable not set" when I try to input my second username and password via the "getelementsbyname" method. It seems as though it is not able to set htmldoc as the IE.document. Your, or anyone's, support would be greatly appreciated. Thank you!

[@tonyleo3219]

Hello sir, I am Learning VBA by watching your video. I have one question: How can I scrape multiple page which have no pagination. For example www.riskmap.com/incidents After scrolling down it's loading more item and seems like it's a long page. Can you please make a tutorial for scraping this type of pages? Thanks

[@giorgitsomaia9832]

Hello WiseOwl, I want to say many thanks for such a great videos, I need your help regarding this video. I am trying to Login into Url using MSXML2.XMLHTTP60 but unfortunately I can't. Can you make small video regarding that issue? that will help a lot.

[@ryanbein8759]

Sorry for a late replay. Within Microsoft Excel. Press ALT+F11 to open the VBA screen for developers. Once at this screen select the "tools" option at the top. Select the "References" option from the drop-down menu. Make sure you have the "Microsoft XML, v6.0" or a similiar one "v3.0" etc. Press the OK button to enable this library. Then run your code again.

[@FistDaMonkey]

Hi Andrew. Really enjoying these videos. I had a quick question. I was on a website that had an tag with a URL inside the tag. The children inside the was inaccessible by my code until I navigated to the URL and was able to get the table from there. However when I try to do the same with an XML method instead of explorer, it can't seem to get a responsetext from the url in the . Does the XML method not work with tags? This is the URL below. www.nba.com/freeagents/2020-tracker And The URL inside the was the same url with a hash at the end. It's the same page except only the table instead of the entire website. www.nba.com/freeagents/2020-tracker#

[@WiseOwlTutorials]

Hi there, I've had a bit of a poke around and this seems to work pretty nicely. I added a bit of code to add the hyperlinks to the cells but you can easily delete that part if you don't need it: Sub ScrapeNBAUsingXMLHTTP() Dim XMLRequest As New MSXML2.XMLHTTP60 Dim HTMLDoc As New MSHTML.HTMLDocument Dim HTMLDiv As MSHTML.IHTMLElement Dim HTMLTable As MSHTML.IHTMLElement XMLRequest.Open "GET", "www.nba.com/freeagents/2020/tracker", False XMLRequest.send If XMLRequest.Status 200 Then MsgBox XMLRequest.Status & " - " & XMLRequest.statusText Exit Sub End If HTMLDoc.body.innerHTML = XMLRequest.responseText Set HTMLTable = HTMLDoc.getElementById("dataFA") WriteTableToWorksheetWithLink HTMLTable End Sub Sub WriteTableToWorksheetWithLink(TableToProcess As MSHTML.IHTMLElement) Dim TableSection As MSHTML.HTMLTableSection Dim TableRow As MSHTML.HTMLTableRow Dim TableCell As MSHTML.HTMLTableCell Dim LinkAnchor As MSHTML.HTMLAnchorElement Dim AnchorURL As String Dim RowNum As Long, ColNum As Long Dim OutputSheet As Worksheet Dim OutputCell As Range RowNum = 0 ColNum = 0 Set OutputSheet = ThisWorkbook.Worksheets.Add For Each TableSection In TableToProcess.Children For Each TableRow In TableSection.Rows RowNum = RowNum + 1 For Each TableCell In TableRow.Cells ColNum = ColNum + 1 Set OutputCell = OutputSheet.Cells(RowNum, ColNum) OutputCell.Value = TableCell.innerText Set LinkAnchor = TableCell.getElementsByTagName("a")(0) If Not LinkAnchor Is Nothing Then AnchorURL = LinkAnchor.getAttribute("href") If TableCell.className = "pnRow" Then AnchorURL = Mid(AnchorURL, InStr(AnchorURL, "/")) AnchorURL = "www.nba.com" & AnchorURL Debug.Print AnchorURL End If OutputCell.Hyperlinks.Add _ Anchor:=OutputCell, _ Address:=AnchorURL Set LinkAnchor = Nothing End If Next TableCell ColNum = 0 Next TableRow Next TableSection End Sub

[@WiseOwlTutorials]

By the way, if you're curious about the # symbol in the URL (and why it's not needed for the XMLHTTP method), you might find this thread useful stackoverflow.com/questions/5395330/what-is-the-sign-in-the-url-what-does-it-mean Hope it helps!

[@FistDaMonkey]

@@WiseOwlTutorials Thank you so much for taking the time to write out the code that works perfectly! I must have done something wrong originally because I had the getelementbyid("dataFA") in my code and it returned nothing back at the time. cheers

[@WiseOwlTutorials]

@@FistDaMonkey No worries, glad you got it working!

[@123snab]

bravo. love it!

[@brianmarshall3064]

Great Videos, but is it possible for some help please? I can't seem to get VBA to recognise the 'tr' tags, and the 'classname' "listingsRow" from the following website tv.dartconnect.com/events/pdc, much appreciated if you can help.

[@DM-py7pj]

Are you getting an error message?

[@힐베르토]

Mr.Adrew, Thank you for the great video. Learned a lot and I would like to know more. The problem I am facing is poping up the error message 'Problem 404- Not found'. I assume this is because of your webpage edition. The WolVidURL should be changed to "www.wiseowl.co.uk/vba-macros/videos/vba-basics/" or somthing different? Still does not work but hope you can let the viewers know what should be corrected. Anyway, thank you million for your hard work.

[@MagnusAnand]

excellent!

[@tobiewaldeck7105]

Hi. I'm trying to complete the revised version using Selenium but I'm struggling with 'GetVideoSubCategory' part of the sheet. Out of frustration I found a way but as in the following code I'm trying to understand and do it in a more traditional way. Option Explicit Public cd As Selenium.ChromeDriver Const WolVidURL As String = "www.wiseowl.co.uk/videos/" Sub GetVideoPage() Set cd = New Selenium.ChromeDriver Dim VidCats As Selenium.WebElements Dim VidCat As Selenium.WebElement Dim VidCatList As Selenium.WebElement 'Dim VidCatList As Selenium.WebElements Dim VidCatID As Integer cd.Start cd.Get WolVidURL Set VidCatList = cd.FindElementsByClass("woMenuList")(1) Set VidCats = VidCatList.FindElementsByClass("woMenuItem") 'WebElements 'Debug.Print VidCatList(1).tagname 'Debug.Print VidCats.Count 'For Each VidCat In VidCats ' Debug.Print VidCat.FindElementsByTag("a").tagname, VidCat.FindElementByTag("a").Attribute("href") 'Next VidCat For VidCatID = 1 To VidCats.Count Set VidCat = VidCats(VidCatID) ListVideosOnPage VidCat.FindElementByTag("a").tagname, VidCat.FindElementByTag("a").Attribute("href") Next VidCatID End Sub Sub GetVideoSubCategory(VidCatName As String, VidCatURL As String) Dim n As Integer cd.Start cd.Get VidCatURL Dim thTable As Selenium.WebElements Dim thURLS As Selenium.WebElements Dim thURL As Selenium.WebElement Dim p As Integer 'Set thTable = cd.FindElementsByCss("[class = 'bpTable']")(1) ''Set thURLS = thTable.FindElementsByTag("a")(1) ' 'For Each thURL In thTable.FindElementsByTag("tr") ' In thURLS 'Set thURL = thTable.FindElementsByTag("td")(1) ' 'For Each thURLS In thURL 'If thURL(1).tagname "TH" Then 'Debug.Print thURLS.FindElementByTag("a").Attribute("href") 'End If ' ' 'Next thURLS 'Next thURL 'Set thTable = cd.FindElementsByTag("tr") '> table > tbody > tr:nth-child(3) > td:nth-child(1) > a 'For p = 1 To thTable.Count 'Here I just found a cheat Set thURLS = cd.FindElementsByCss("td:nth-child(1) > a") For Each thURL In thURLS Debug.Print thURL.Attribute("href") Next thURL 'Next p cd.Close End Sub

[@WiseOwlTutorials]

Hi Tobie! While it may feel like cheating, the beauty of CSS selectors is the ability to take shortcuts like this! I think you've found a very nice solution to obtain the results you need and that's the important thing, I like it!

[@tobiewaldeck7105]

@@WiseOwlTutorials Thank you, I appreciate it.

[@WiseOwlTutorials]

@@tobiewaldeck7105 No problem Tobie!

[@tobiewaldeck7105]

@@WiseOwlTutorials Is there any way using Selenium basic method to avoid a timeout error for a slow loading page or pop ups.

[@WiseOwlTutorials]

@@tobiewaldeck7105 Hi Tobie, SeleniumBasic methods which attempt to find an element each have an implicit wait of, by default, 3 seconds. Each method has an optional timeout parameter which you can set in milliseconds - this would wait 5 seconds before throwing the error: cd.FindElementById ID:="MyElement", timeout:=5000 You can also change the global implicit wait time so that you don't have to specify this every time: Set cd = New Selenium.ChromeDriver cd.Timeouts.ImplicitWait = 10000 I hope that helps!

[@kabounga75]

i have seen the video series and i have learnt a lot of things but unfortunately i couldn't achieve my mission which get both of arrivals and departures flights for tomorrow from the web site www.cairo-airport.com/en-us/Flights/Flights-Schedule due to failed to get both of id and class i believe that mentioned website use little bit different technique so i will be highly appreciated if you could help me for this

[@domfabi1]

HI wiseowl, I tried to run the code but kept getting "Runtime Error 91- Object variables or with block variables not set" at this line: HTMLDOc.body.innerHTML = XMLReq.responseText I've ran out of ideas trying to fix the problem, any help will be greatly appreciated.

[@WiseOwlTutorials]

Hi Austin! The message suggests that either the HTMLDoc or XMLReq variable isn't set. You can use the Locals window (View | Locals Window) when your code is in Break Mode (click Debug when the runtime error dialog appears) to check. If either of the variables has a value of Nothing that should point you in the right direction!