Extracting Table from website using VBA

[Nichole09]

Hello!!


I am struggling with pulling a table from a website and would greatly appreciate any help. The code I have below currently goes to the website and logs the user in using their username and password entered in cells B1 and B2. Once the code below logs the user in, there is a table immediately shown. I want to extract that table and add it to my active worksheet. I have played with codes found online without success. I have tried running queries that end up showing nothing on my worksheet and tried pulling different table numbers (without knowing which table is what) without success as well. Unfortunately the website is a paid for site and I can not share it, but I can provide some codes listed on the website for more visibility for your assistance and a screen shot. I greatly appreciate any help!!

Sub login()
    Dim tb As Object, tr As Object, th As Object
    Dim yourValue As String, obj As Object
    Dim URL As String, IE As Object, HWNDSrc As Long
    Dim currenturl As String
    Dim wb As Workbook
    Dim ws As Worksheet
   
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("practice report")


    Set IE = CreateObject("InternetExplorer.application")
    IE.AddressBar = 0
    IE.StatusBar = 0
    IE.Toolbar = 0
    IE.Visible = True
    IE.navigate "https://www.services.com/login"
    ' i have adjusted the above website, this is not the correct site but changed it for privacy reasons
    Do Until Not IE.Busy: DoEvents: Loop
    Set Doc = IE.document
    Do While Doc.readyState <> "complete": DoEvents: Loop
    ' the below will check if you are already logged into the site. If so, it will skip the log in part
    currenturl = IE.LocationURL
    If currenturl = "https://www.services.com/login" Then
     IE.document.all("login").Value = ws.Range("B1").Value
    IE.document.all("password").Value = ws.Range("B2").Value
    IE.document.all("submit").Click
    Else
    End If


   
  ' this is where i would like to pull the table of data into this active worksheet
 
   
   
End Sub

Below is some code from the website when I hit f12 and type "table" I get this very long function that I can not really understand. Below is just a very small portion of it in hopes it might help resolve my issue. The main page when I hit12 without searching for "table" does not really show anything? It just shows what appears to be the buttons on the image shown below and does not say anything about a table or query or function. Any ideas? I am happy to provide more code to the site if any one can direct me on what to look for?

!function(a,b){"object"==typeof module&&"object"==typeof module.exports?module.exports=a.document?b(a,!0):function(a){if(!a.document)throw new Error("jQuery requires a window with a document");return b(a)}:b(a)}("undefined"!=typeof window?window:this,function(a,b){var c=[],d=a.document,e=c.slice,f=c.concat,g=c.push,h=c.indexOf,i={},j=i.toString,k=i.hasOwnProperty,l={},m="2.2.4",n=function(a,b){return new n.fn.init(a,b)},o=/^[\s\uFEFF\xA0]+|[\s\uFEFF\xA0]+$/g,p=/^-ms-/,q=/-([\da-z])/gi,r=function(a,b){return b.toUpperCase()};n.fn=n.prototype={jquery:m,constructor:n,selector:"",length:0,toArray:function(){return e.call(this)},get:function(a){return null!=a?0>a?this[a+this.length]:this[a]:e.call(this)},pushStack:function(a){var b=n.merge(this.constructor(),a);return b.prevObject=this,b.context=this.context,b},each:function(a){return n.each(this,a)},map:function(a){return this.pushStack(n.map(this,function(b,c){return a.call(b,c,b)}))},slice:function(){return this.pushStack(e.apply(this,arguments))},first:function(){return this.eq(0)},last:function(){return this.eq(-1)},eq:function(a){var b=this.length,c=+a+(0>a?b:0);return this.pushStack(c>=0&&b>c?[this[c]]:[])},end:function(){return this.prevObject||this.constructor()},push:g,sort:c.sort,splice:c.splice},n.extend=n.fn.extend=function(){var a,b,c,d,e,f,g=arguments[0]||{},h=1,i=arguments.length,j=!1;for

 

[Norie]

That JavaScript code has nothing to do with the issue.

Try passing IE.document to the following sub.

Sub GetAllTables(doc As Object)
Dim ws As Worksheet
Dim rng As Range
Dim tbl As Object
Dim rw As Object
Dim cl As Object
Dim tabno As Long
Dim nextrow As Long
Dim I As Long

    Set ws = Worksheets.Add

    For Each tbl In doc.getElementsByTagName("TABLE")

        tabno = tabno + 1

        nextrow = nextrow + 1

        Set rng = ws.Range("B" & nextrow)

        rng.Offset(, -1) = "Table " & tabno

        For Each rw In tbl.Rows

            For Each cl In rw.Cells
                rng.Value = cl.outerText
                Set rng = rng.Offset(, 1)
                I = I + 1
            Next cl

            nextrow = nextrow + 1

            Set rng = rng.Offset(1, -I)
            I = 0
        Next rw

    Next tbl

    ws.Cells.ClearFormats

End Sub

 

[Nichole09]

Hi Norie! Thank you for your help! I set up this code and ran it. What was odd is it only pulled two tables, that are just calendars. I attached my results in the image attached to this post. Do we know why those are the only tables being shown? Thank you again!!

 

[Norie]

The code I posted should extract all the tables on the page, perhaps the data you want isn't actually in a table.

 

[Nichole09]

It could not be a table. Its difficult for me to tell. The image in my original post shows what the "table" looks like. How could I tell what it is I am extracting?

 

[Norie]

Try right clicking in the 'table' you want to extract.

The right click menu should have an option like Inspect Element and if you select that you can look at the underlying code.

 

[Nichole09]

Try right clicking in the 'table' you want to extract.

The right click menu should have an option like Inspect Element and if you select that you can look at the underlying code.

I discovered this option but I am still having a hard time reading it. Image attached of a snip of this data. It appears it is not a table. I tried selecting the entire website and pasting it into excel to see what happens, and all the data I am searching for goes all into cell A1 line by line. I am trying to read these codes but not familiar with HTML. If I am not searching for a table, is there a way to gather any and all data from this site and put it into excel? Maybe I can start there and whittle it down until I find that data in the "table" I am going for. Thoughts?