Posts by pandaaram

Re: Fetching data from multiple URL

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

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.

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.

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"

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

For Each oRow In .Rows

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

With .getelementsbytagname("table")(5)

be replaced with

With .getelementsbytagname("table")(6)

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.

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..

Re: Extract data from Web/pull data from web through excel

Kyle,

I have similar query and hope you may help.

I have an Excel that contains the following:

Sheet1 - Col B with name & Col C with URL

Requirement: Extract webtable 6 for each of these URL's and save it on Sheet-2. Also, the # of rows per URL may vary. Hence, it should find the last populated value in Col B and start from next row.

Sample Macro recorded:

Sub Test()
'
' Test Macro








'
With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://url name.html", _ ' I need to fetch the URL name from Sheet1 Col C2 thru C100 (for eg)
        Destination:=Range("$A$1")) ' Need to dynamically choose the Destination based on where previous cell ends
        .Name = "1st Executive" ' I need to fetch Name from Sheet1 Col B2 thru B100 (for eg)
        .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

Dear Friends,

I have recorded a Macro and its something like this:

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

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.