Web Query pulling Table Data (HELP !)

[jalifid11]

I found this Macro in one of the many threads out there for Web Queries...

Sub Test()
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.navigate "http://equitytoincome.com/ex.htm"
Do Until .readyState = 4: DoEvents: Loop
Set doc = ie.document
GetAllTables doc
End With
End Sub

Sub GetAllTables(d)
For Each e In d.all
If e.nodename = "TABLE" Then
Set t = e

tabno = tabno + 1
nextrow = nextrow + 1
Set rng = Range("B" & nextrow)
rng.Offset(, -1) = "Table " & tabno
For Each r In t.Rows
For Each c In r.Cells
rng.Value = c.innerText
Set rng = rng.Offset(, 1)
I = I + 1
Next c
nextrow = nextrow + 1
Set rng = rng.Offset(1, -I)
I = 0
Next r
End If
Next e
End Sub


I'm a complete noob when it comes to web queries and pulling data into excel using web queries... What I'm trying to do is, my company has an intranet site that has all production numbers in various reports (blah blah blah), and what I'm trying to do is import some reports into excel where I can analyze the data easier.. The reports that we have, we can flip them around switching axis information, clicking on a particular section and it gives more in depth information to whatever I'm looking for, and stuff, and the problem I'm encountering is that I get an "Unexpected Error" when doing it through the Import Data feature in Excel. I think I've pegged it to the address string being way too long for Excel to import the information. Each time I modify the report it extends the string..

So I came across this macro that can download the info but it shows me all the tables that are on a particular page in that report... Using this I found out exactly which table I need. So my question is, how can I download just THAT specific Table? I found that there was 15 Tables on this particular report, I'm only needing table 9...

HELP...

 

[ravishankar]

Hi
insert two codes
"if nextrow = 9 then"
"End If" as follows

nextrow = nextrow + 1
If nextrow = 9 Then
Set rng = Range("B" & nextrow)

Next r
End If
End If
Ravi

 

[Norie]

Mmmm, code looks familiar.

Here's it's partner for extracting 1 table.

Sub GetOneTable(d, n)
' d is the document
' n is the table to extract
Dim e As Object ' the elements of the document
Dim t As Object ' the table required
Dim r As Object ' the rows of the table
Dim c As Object ' the cells of the rows.
Dim I As Long
Dim J As Long
    For Each e In d.all
        If e.nodename = "TABLE" Then
            J = J + 1
        End If
        If J = n Then
            Set t = e
    
            tabno = tabno + 1
            nextrow = nextrow + 1
            Set rng = Range("A" & nextrow)
            For Each r In t.Rows
                For Each c In r.Cells
                    rng.Value = c.innertext
                    Set rng = rng.Offset(, 1)
                    I = I + 1
                Next c
                nextrow = nextrow + 1
                Set rng = rng.Offset(1, -I)
                I = 0
            Next r
            Exit For
        End If
    
    Next e
    
End Sub

You would replace this:

GetAllTables doc

With something like this:

GetOneTable doc, 9

 

[jalifid11]

That did the trick...

There's no way getting around the string address being to long, is there? By doing web queries I mean... I keep getting an "unexpected error" when I use the standard import data section in Excel..

Anyway.. Thanks for the help... Question: If I run the macro tomorrow when more information updates, will it overwrite whatever is there? Essentially this is what I want it to do anyway...

 

[jalifid11]

Oh, and one more thing.. is there anyway to have the information download from the web, but not open another window..

When I run it now, it opens up another internet explorer. Is there anyway for it not to, or at least close the window when it downloads information into excel?

 

[jalifid11]

Is there a way to have the macro leave the information on a specific sheet? I may have to do 2 or 3 different queries but I'm going to need them on separate spreadsheets within the same workbook..

 

[jalifid11]

How do you have the data that is pulled, download to a specific sheet and/or specific cell on a worksheet?

 

[Norie]

4 I don't know what you mean - the code doesn't use web queries it automates IE.

5 Remove .Visible = True - note doing so is untested, normally when working with sort of thing, when developing anyway, we want to see the page.

6 Just qualify the range references, at the moment they will work with the active sheet.

For example:

Range("A" & nextrow) - this refers to the range on the activesheet

Worksheets("Sheet1").Range("A" & NextRow) - refers to range on the worksheet Sheet1.

7 See above, though sending specific data to specific locations could be complicated.
That would be highly dependent on the design of the web page.