CODEWITHSUNDEEP
vbaexcel
ramesh bethapudi
ramesh bethapudi

Reputation: 5

How to call URL from cells in sheet1 and scrape webpage data in sheet2..!

I am trying to scrape the tabular data into excel using website url, but I want URL should be called from Cell(A1) in sheet1 and Cell(A2) in sheet1 and so on... The scrapped data should be imported in sheet2 in the same workbook. Please help me out. I am using the below code but i am able to work with once one url.

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection _
    :="URL;http://publicrecords.netronline.com/state/AL/county/macon", Destination _
        :=Range("$A$1"))
        .Name = "autauga"
        .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 = "5"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.SmallScroll Down:=-15
    Range("C1").Select
    ActiveWindow.SmallScroll Down:=-12
End Sub

Upvotes: 0

Views: 177

Answers (1)

Weasemunk
Weasemunk

Reputation: 455

I'm not completely sure what you're asking for, but I think you want to use a For loop.

For row = 1 To n
    ...
    Range("$A$" & row)
    ...
Next

This will allow you to use cells A1, A2, ..., An sequentially.

Upvotes: 1

Related Questions