-1

I wrote excel macro to fetch data from multiple pages ( here around 25-40 pages ) . I have managed to change pages and scrape all pages from every page .

Sub Fetch_Data() 

Dim IE As Object

Dim httpReq As Object
Dim HTMLdoc As Object
Dim resultsTable As Object
Dim tRow As Object, tCell As Object
Dim destCell As Range
    

Set IE = CreateObject("InternetExplorer.Application")

IE.Visible = False

'Application.ScreenUpdating = False
Application.StatusBar = "Data Fetching in progress, please wait..."

IE.Navigate "https://www.bseindia.com/markets/debt/TradenSettlement.aspx" 'load the Backshop Loan Locator page

Do
DoEvents
Loop Until IE.ReadyState = 4

Set HTMLdoc = IE.Document

'LR = Cells(Rows.Count, 1).End(xlUp).Row

   With ActiveSheet
        '.Cells.ClearContents
        Set destCell = .Range("A1")
    End With
    
Set resultsTable = HTMLdoc.getElementById("ContentPlaceHolder1_GridViewrcdsFC")
    For Each tRow In resultsTable.Rows
        For Each tCell In tRow.Cells
            destCell.Offset(tRow.RowIndex, tCell.cellIndex).Value = tCell.innerText
        Next
Next

'________________________________________________________________________________________________________________________
    
'Go to Next page

'IE.Navigate "javascript:__doPostBack('ctl00$ContentPlaceHolder1$GridViewrcdsFC','Page$2')"
i = 2


For i = 2 To 50

If i = 2 Then
Url = "javascript:__doPostBack('ctl00$ContentPlaceHolder1$GridViewrcdsFC','Page$2')"
On Error GoTo ErrorHandler
ElseIf i = 3 Then
Url = "javascript:__doPostBack('ctl00$ContentPlaceHolder1$GridViewrcdsFC','Page$3')"
On Error GoTo ErrorHandler
ElseIf i = 4 Then
Url = "javascript:__doPostBack('ctl00$ContentPlaceHolder1$GridViewrcdsFC','Page$4')"
On Error GoTo ErrorHandler
ElseIf i = 5 Then
Url = "javascript:__doPostBack('ctl00$ContentPlaceHolder1$GridViewrcdsFC','Page$5')"
On Error GoTo ErrorHandler

ErrorHandler:
GoTo XYZ

End If

IE.Navigate Url
Do
DoEvents
Loop Until IE.ReadyState = 4
Url = ""


LR = Cells(Rows.Count, 1).End(xlUp).Row - 1

   With ActiveSheet
        '.Cells.ClearContents
        Set destCell = .Range("A" & LR)
    End With
    
Set resultsTable = HTMLdoc.getElementById("ContentPlaceHolder1_GridViewrcdsFC")
    For Each tRow In resultsTable.Rows
        For Each tCell In tRow.Cells
            destCell.Offset(tRow.RowIndex, tCell.cellIndex).Value = tCell.innerText
        Next
Next

Next i



'________________________________________________________________________________________________________________________

XYZ: IE.Quit

Application.StatusBar = "Data Fetching Completed"

MsgBox ("Data Successfully Fetched")

Application.StatusBar = ""

 Dim lrow As Long
    Dim index As Long
    Dim header As String

    header = Range("A1").Value
    lrow = Range("A" & Rows.Count).End(xlUp).Row

    For index = 2 To lrow
        If Range("A" & index).Value = header Then Rows(index).Delete
    Next
    
End Sub

I want to change pages automatically without writing every page , I tried something like below , but pages are not getting changed , how to loop through pages :

For i = 2 To 4

x = "Page$" + CStr(i)

Url = "javascript:__doPostBack('ctl00$ContentPlaceHolder1$GridViewrcdsFC'," & x & ")"

On Error GoTo ErrorHandler

ErrorHandler:
GoTo XYZ
New_Coder
  • 13
  • 3

1 Answers1

0

You have to look if there are url links to the other pages on the current page, find a tag and loop all the webpages. You can also look voor the url of each page and hardcode it.

Example with urls beneath tag "a":

Set AElements = HTMLDoc.getElementsByTagName("a")
    For Each AElement In AElements
        If AElement.id = "xxxxxxxxx" Then
            Cells(Cell, 27) = AElement.src     'I write URL in the 27th column
           'AElement.href
        End If
    Next AElement
Daniel Sanders
  • 24
  • 2
  • Actually I am new to this, so I don't understand much about tagging... Can you please tell me in above program about how I can fetch data from multiple pages in single website? I tried to write many programs but I am able to fetch only first page – New_Coder Nov 22 '22 at 09:01
  • I have tried in many ways but still no success..... – New_Coder Nov 22 '22 at 10:29
  • I can't enter the webpage, i get a message that the site is moved and somekind of security warning.I need te see the webpage to look for certain tags. – Daniel Sanders Nov 24 '22 at 08:54
  • I wrote program and its working as expected , little bit slow.... but I need to know how i can loop into pages , please check i have added in my program above ..i cant loop pages – New_Coder Nov 25 '22 at 06:11