Fetching data from multiple URL

pandaaram · Nov 16th 2012

Dear Friends,

I have recorded a Macro and its something like this:

Code

Sub Macro1()
'
' Macro1 Macro
'




'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://URL NAME-1.html", _
        Destination:=Range("$A$1"))
        .Name = "1st_executive"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "6"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Display More

This Macro would fetch details from URL mentioned and paste the data in "A1" on Sheet-1.

Requirement: I have over 3000 such URLs from which data needs to be downloaded. the URL links are present in a separate worksheet. How do i reference "Connection:=_" to dynamically get it from the work sheet and increment it by 1?

Also, how do i set the Destination:=Range to dynamically refer to last row + 1 rather than hardcoding it as $A$1 or $A$20, etc?

Any help in this regard is highly appreciated.

Kyle123 · Nov 16th 2012

Re: Fetching data from multiple URL

I wouldn't use a web query for this, can you share the url?

And what output are you wanting - you need to mock something up

pandaaram · Nov 16th 2012

Re: Fetching data from multiple URL

Kyle,

Attached excel contains the list of URL's from which i would the data to be extracted. I am sorry for jumping onto the other thread. I posted bcz i felt both queries were relevant. Apologies if its against the forum rules.

Output - Requires formatting. I think i will be able to do once sheet 2 has information about Webtable 6 from all URLs..

Kyle123 · Nov 16th 2012

Re: Fetching data from multiple URL

Is it the office details in the light blue box that you're after?

pandaaram · Nov 16th 2012

Re: Fetching data from multiple URL

Kyle,

Yes. I am after the entire information available on web table - 6

1. Office Address
2. Phone
3. Fax
4. Email
5. Website
6. Categories.

Kyle123 · Nov 16th 2012

Re: Fetching data from multiple URL

How's this as a starter for 10:

Code

Sub GetData()
    Dim oHtm As Object: Set oHtm = CreateObject("HTMLFile")
    Dim req As Object: Set req = CreateObject("msxml2.xmlhttp")
    Dim oRow As Object
    Dim oCell As Range
    Dim url As String
    Dim y As Long, x As Long
    
    x = 1
    For Each oCell In Sheets("sheet2").Range("b2:b10")
        req.Open "GET", oCell.Offset(, 1).Value, False
        req.send
        With oHtm
            .body.innerhtml = req.responsetext
            With .getelementsbytagname("table")(5)
                With Sheets(1)
                    .Cells(x, 1).Value = oCell.Offset(, -1).Value
                    .Cells(x, 2).Value = oCell.Value
                End With
                y = 3
                For Each oRow In .Rows
                    Sheets(1).Cells(x, y).Value = oRow.Cells(1).innertext
                    y = y + 1
                Next oRow
            End With
        End With
        x = x + 1
    Next oCell




End Sub

Display More

pandaaram · Nov 16th 2012

Re: Fetching data from multiple URL

Kyle,

I tried copying this code as a macro, retained row B2 to B10 on Sheet2 and executed the macro.. getting "Run time error -2147024891 (80004005) Access is denied" error message.

I ain't trying to access anything that is password protected. Could you kindly let me know where i am going wrong? Attaching screenprint for reference.

Also, should the detail

Code

With .getelementsbytagname("table")(5)

be replaced with

Code

With .getelementsbytagname("table")(6)

Kyle123 · Nov 16th 2012

Re: Fetching data from multiple URL

Try changing:

Code

Dim req As Object: Set req = CreateObject("msxml2.xmlhttp")

to

Code

Dim req As Object: Set req = CreateObject("WinHttp.WinHttpRequest.5.1")

And no, the table number doesn't need changing

pandaaram · Nov 16th 2012

Re: Fetching data from multiple URL

Kyle,

went past that error but receiving a "Run-time error "91" Object Variable or with block variable not set" and the debug refers to

Code

For Each oRow In .Rows

Kyle123 · Nov 16th 2012

Re: Fetching data from multiple URL

I can't replicate that error. It suggests that something's missing from the page.

Do you get the same error in my workbook?

pandaaram · Nov 16th 2012

Re: Fetching data from multiple URL

Kyle,

No - I don't get the error now. but by the look of response on the worksheet URL it doesn't seem to be alright as the data seems to be overwriting the contents.

The correct data should be as per the attachment (either one is fine)

NOTE: I am getting the same error while executing Macro GetData". If i execute macro test, it gives "Application defined or object defined error"

Kyle123 · Nov 16th 2012

Re: Fetching data from multiple URL

Well have you resized the rows and columns?

You need to make a list of all the possible bits of info that get listed in that table, you then need to check this list against the list on the actual page. Some pages have more data than others and you need some way of sorting which data goes into which columns

pandaaram · Nov 16th 2012

Re: Fetching data from multiple URL

Kyle - Agree.. If we could get info as per "Unformatted" page it would make sense as well as I can use some Sort/ filer options to re-arrange it in way specified in the "formatted" page.

Kyle123 · Nov 16th 2012

Re: Fetching data from multiple URL

Why on earth would you want the data as per the unformatted tab?

I've told you how to do it as a one stage operation, why would you want to add a second stage "sort and filter"?

pandaaram · Nov 16th 2012

Re: Fetching data from multiple URL

Kyle,

I was hoping that getting information as per unformatted tab might be simpler when compared to formatted one. Also, I wasn't sure if Formatting was doable because of following reasons:

1. Address - will be more than one line. So, merging them all and adding it to a single cell might be difficult.
2. Categories - Likewise. Sometimes, we can have one category, but many a time, we end up with more than 5-6 of them.

Even if we couldn't add it to a single cell, its OK if we could identify the Max number of rows (Across columns A-H) occupied by previous query and start from the next one.

Hope this helps and forgive me as i don't possess coding skills on VB. I do know programming, so i can understand the logic happening in the program, however not upto the mark in getting to code.

pandaaram · Nov 17th 2012

Re: Fetching data from multiple URL

Kyle - Thank you so much !! This is working now... I owe this completely to you !!

Fetching data from multiple URL

Participate now!

Share