MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
12Next
Return Post new threads
View: 630|Reply: 10

extracting to excel entire web page not just restricted to

[Copy link]

11

Topics

123

Posts

295

Integral

Conqueror

Rank: 3Rank: 3

Integral
295
Post on 2-11-2017 11:37:28 | All posts |Read mode
i have excel macro to extract hyperlinks,tables and html text.But what i need is to extract entire webpage including all textlines(not html),tables,articles,i mean what ever is there in webpage to be copied and pasted in excel sheet cell by cell not in a singlecell.I can do this task manually but there will be updations everyday and also i need for lot of websites...
for tables alone i am having the code belowSub HTML_Table_To_Excel()
    Dim htm As Object
    Dim Tr As Object
    Dim Td As Object
    Dim Tab1 As Object

    'Replace the URL of the webpage that you want to download
    Web_URL = http://names.mongabay.com/most_common_surnames.htm
   
    Application.ScreenUpdating = False
   

    'Create HTMLFile Object
    Set HTML_Content = CreateObject(htmlfile)

    'Get the WebPage Content to HTMLFile Object
    With CreateObject(msxml2.xmlhttp)
        .Open GET, Web_URL, False
        .send
        HTML_Content.body.innerHTML = .responseText
    End With

    Column_Num_To_Start = 1
    iRow = 2
    iCol = Column_Num_To_Start
    iTable = 0
    Cells.Clear

    'Loop Through Each Table and Download it to Excel in Proper Format
    For Each Tab1 In HTML_Content.getElementsByTagName(table)
        With HTML_Content.getElementsByTagName(table)(iTable)
            For Each Tr In .Rows
                For Each Td In Tr.Cells
                    Sheets(1).Cells(iRow, iCol).Select
                    Sheets(1).Cells(iRow, iCol) = Td.innerText
                    iCol = iCol + 1
                Next Td
                iCol = Column_Num_To_Start
                iRow = iRow + 1
            Next Tr
        End With
        iTable = iTable + 1
        iCol = Column_Num_To_Start
        iRow = iRow + 1
    Next Tab1
     MsgBox Process Completed
     
         Application.ScreenUpdating = False
End Sub
Thanks in advance
Narene




Reply

Props Report

7

Topics

551

Posts

1283

Integral

King

Rank: 6Rank: 6

Integral
1283
Post on 2-11-2017 13:08:49 | All posts



Why would you want to copy entire pages/sites...?




Reply Support Opposition

Props Report

11

Topics

123

Posts

295

Integral

Conqueror

Rank: 3Rank: 3

Integral
295
 Author| Post on 2-11-2017 13:56:36 | All posts



in our office circulars will be released everyday from zonal office,everyday new circulars will come.From 2001 to 2016 and till jan 31 2017,i have copied all circulars to excel so that i can easily sort ,search etc that i cant done in that webpage.But if any new circulars come right from feb 1,2017 first i have to copy from that webpage,and paste in excel,then this task will be repeated and takes a long time bro.




Reply Support Opposition

Props Report

7

Topics

551

Posts

1283

Integral

King

Rank: 6Rank: 6

Integral
1283
Post on 2-11-2017 15:36:02 | All posts




From http://data.mongabay.com/copyright.htm
So you have permission to copy copyrighted content from that site and any other copyright protected sites you want to scrape?




Reply Support Opposition

Props Report

11

Topics

123

Posts

295

Integral

Conqueror

Rank: 3Rank: 3

Integral
295
 Author| Post on 2-11-2017 15:41:39 | All posts



no sir,when i googled i got a macro which contains this url  http://data.mongabay.com/copyright.htm.
not actually intented to use this copyright protected website sir.




Reply Support Opposition

Props Report

11

Topics

123

Posts

295

Integral

Conqueror

Rank: 3Rank: 3

Integral
295
 Author| Post on 2-11-2017 16:08:38 | All posts



Hai i am using only for our office website for which iam having userid and PW and thai is only intranet also..kindly request you to solve my problem sir




Reply Support Opposition

Props Report

11

Topics

123

Posts

295

Integral

Conqueror

Rank: 3Rank: 3

Integral
295
 Author| Post on 2-11-2017 16:53:37 | All posts



can any one help me with my query




Reply Support Opposition

Props Report

11

Topics

123

Posts

295

Integral

Conqueror

Rank: 3Rank: 3

Integral
295
 Author| Post on 2-11-2017 18:05:11 | All posts




Hi !
From Excel use Data, From Web … (aka QueryTable object in VBA)
And using Macro recorder you will have your own free code !





Reply Support Opposition

Props Report

11

Topics

123

Posts

295

Integral

Conqueror

Rank: 3Rank: 3

Integral
295
 Author| Post on 2-11-2017 18:39:09 | All posts



Thanks for your response dear...
Actually what I want is we open a webpage let it be any site,when it is opened,I just want Ctrl+A,Ctrl+C on the webpage then come to excel Ctrl+v...can u give me the code,I tried to record myself but it does not work....
Reply Support Opposition

Props Report

11

Topics

123

Posts

295

Integral

Conqueror

Rank: 3Rank: 3

Integral
295
 Author| Post on 2-12-2017 05:06:00 | All posts



Thanks for your response dear...
Actually what I want is we open a webpage let it be any site,when it is opened,I just want Ctrl+A,Ctrl+C on the webpage then come to excel Ctrl+v...can u give me the code,I tried to record myself but it does not work....




Reply Support Opposition

Props Report

12Next
Return Post new threads

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 11-19-2017 16:20 , Processed in 0.298569 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!