VBA_Module_1

Option Explicit

Sub dataBizBuy1()                             's()

    Dim IE As InternetExplorer
    Dim listedBy As Object
    Dim adP As HTMLDocument
    Dim HTMLdoc As HTMLDocument
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim intRows As Long
    Dim rowNo As Long
    Dim adID As Variant
    Dim askingPrice As Variant
    Dim brokerFirm As Variant
    Dim cashFlow As Variant
    Dim doc As Variant
    Dim ebitda As Variant
    Dim ele As Variant
    Dim elediv As Variant
    Dim element As Variant
    Dim employees_1 As Variant
    Dim established_1 As Variant
    Dim ffe_1 As Variant
    Dim grossRevenue As Variant
    Dim inventory_1 As Variant
    Dim listedBy_1 As Variant
    Dim location_1 As Variant
    Dim mtbl_1 As Variant
    Dim mtbl_2 As Variant
    Dim realEstate_1 As Variant
    Dim rent_1 As Variant
    Dim subTitle_1 As Variant
    Dim title_1 As Variant
    Dim txt As Variant
    Dim url As Variant
    Dim http As Object
    Dim urls() As Variant
    Dim html As MSHTML.HTMLDocument


    Set http = CreateObject("MSXML2.XMLHTTP")
    Set html = New MSHTML.HTMLDocument

    Dim url2 As Long
    Dim results() As Variant

    Set IE = New InternetExplorer
    IE.Visible = True

    Set ws1 = ThisWorkbook.Sheets("websiteURL")
    Set ws2 = ThisWorkbook.Sheets("Labels")
    Set ws3 = ThisWorkbook.Sheets("ScrapingResults")

    ws1.Range("B1").value = "=CountA(A:A)"
    intRows = ws1.Range("B1").value

    For rowNo = 2 To intRows

        url = ws1.Range("A" & rowNo).Text
        IE.navigate url

        Do While IE.Busy Or IE.readyState <> 4
            Application.Wait DateAdd("s", 1, Now)
        Loop

        Set doc = IE.document


        With doc.querySelectorAll("div.span8 > h1,h2")
            If .Length > 0 Then
                If Left(.Item(0).innerText, 36) <> "This listing is no longer available." Then

                    title_1 = doc.getElementsByClassName("bfsTitle")(0).innerText
                    ws3.Range("C" & rowNo).value = title_1

                    subTitle_1 = doc.getElementsByClassName("span8")(0).innerText
                    ws3.Range("D" & rowNo).value = subTitle_1

                    For Each ele In doc.getElementsByClassName("title")
                        txt = ele.parentElement.innerText

                        If Left(txt, 12) = "Asking Price" Then
                            askingPrice = Trim(Mid(txt, InStrRev(txt, ":") + 1))
                        ElseIf Left(txt, 9) = "Cash Flow" Then
                            cashFlow = Trim(Mid(txt, InStrRev(txt, ":") + 1))
                        ElseIf Left(txt, 13) = "Gross Revenue" Then
                            grossRevenue = Trim(Mid(txt, InStrRev(txt, ":") + 1))
                        ElseIf Left(txt, 6) = "EBITDA" Then
                            ebitda = Trim(Mid(txt, InStrRev(txt, ":") + 1))
                        ElseIf Left(txt, 4) = "FF&E" Then
                            ffe_1 = Trim(Mid(txt, InStrRev(txt, ":") + 1))
                        ElseIf Left(txt, 9) = "Inventory" Then
                            inventory_1 = Trim(Mid(txt, InStrRev(txt, ":") + 1))
                        ElseIf Left(txt, 11) = "Real Estate" Then
                            realEstate_1 = Trim(Mid(txt, InStrRev(txt, ":") + 1))
                        ElseIf Left(txt, 4) = "Rent" Then
                            rent_1 = Trim(Mid(txt, InStrRev(txt, ":") + 1))
                        ElseIf Left(txt, 11) = "Established" Then
                            established_1 = Trim(Mid(txt, InStrRev(txt, ":") + 1))
                        End If
                    Next ele

                    ws3.Range("E" & rowNo).value = askingPrice
                    ws3.Range("F" & rowNo).value = cashFlow
                    ws3.Range("G" & rowNo).value = grossRevenue
                    ws3.Range("H" & rowNo).value = ebitda
                    ws3.Range("I" & rowNo).value = ffe_1
                    ws3.Range("J" & rowNo).value = inventory_1
                    ws3.Range("K" & rowNo).value = realEstate_1
                    ws3.Range("L" & rowNo).value = rent_1
                    ws3.Range("M" & rowNo).value = established_1

                    Set mtbl_1 = doc.getElementsByClassName("broker")(0)
                    Set listedBy = mtbl_1.getElementsByTagName("a")(1)
                    listedBy_1 = listedBy.innerText
                    ws3.Range("N" & rowNo).value = listedBy_1

                    brokerFirm = doc.querySelector("div.broker > h4").innerText
                    ws3.Range("O" & rowNo).value = brokerFirm

                    Set mtbl_2 = doc.getElementsByClassName("disclaimer")(0)
                    Set adP = mtbl_2.getElementsByTagName("p")(0)
                    ws3.Range("P" & rowNo).value = Split(adP.innerText, ":")(1)


                End If

                If ws3.Range("C" & rowNo).value = "" Then
                    ws3.Range("B" & rowNo).value = "Not available"
                End If

            End If
        End With

    Next

    IE.Quit
    Set IE = Nothing
    MsgBox "done"

End Sub