scraping webpage data modification

Hi,


As i need some expert advise to help me get this code working properly.
1) This code(in attachment notepad) is working fine to scrape data from web page and successfully pasting it to Excel, but ii requires little modification in code so that it will paste data in only one cell, rather than pasting it to whole workbook.

2) Addition modification requires to make a loop through code so that it access the input from Excel(in attachment-Input sheet) and make changes to URL(i noticed in URL that only last word needs to be changed which will be taken from excel file column 1and so on till its find blank)


Any help be much appreciated.
Thanks.

Automation has been disabled on this site says Wiki. Try to use HTTP request(There are many threads on this site)

The code

Sub Test()
Dim IE As Object, cel As Range
     
    Set IE = CreateObject("InternetExplorer.Application")
 For Each cel In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    With IE
        Visible = 0
        .Navigate "https://en.wikipedia.org/wiki/" & cel ' should work for any URL
        Do Until .ReadyState = 4: DoEvents: Loop

        x = .Document.body.innerText
        x = Replace(x, Chr(10), Chr(13))
        x = Split(x, Chr(13))
        Range("C1").Resize(UBound(x)) = Application.Transpose(x)
        
        .Quit
    End With
Next
End Sub

Range("A1").Resize(UBound(x)) = Application.Transpose(x)
You have an array with 456 rows. You are dumping in to a cell. If you want to scrape the data, you need to break down the page by locating the element where the data is stored.

Hi,

As pasting all the wen data into single is getting done by this code, but i still need help to loop it for all the website access.
2nd additional requirement of my original post.

2) Addition modification requires to make a loop through code so that it access the input from Excel(in attachment-Input sheet) and make changes to URL(i noticed in URL that only last word needs to be changed which will be taken from excel file column 1and so on till its find blank)

Sub Trial()
    Dim IE As Object

    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "https://en.wikipedia.org/wiki/maya" ' should work for any URL
        Do Until .ReadyState = 4: DoEvents: Loop

            Range("B1").Value = .document.body.innertext

            .Quit
        End With

End Sub

I doubt if you read what I wrote in post 2.

First, the site does not allow multi page requests. The code stopped at the second loop. There are restrictions. There are some people who claim you can get around this problem, but I doubt it if the owner does not allow multi request.

Second, if you overcome the limitation issue, you need to find the elements where the data is stored to scrape it.

Hi AB33,


Much appreciated for your help and concern.
As i am not trying to access this(WIKI) site for any use, its just a sample web page. My company web page have lots of restriction, that why i cannot post that, and this example was very much similar to what i was trying to achieve with macro.