Url as text

klatlap

Well-known Member
Joined
Sep 1, 2004
Messages
597
This is part of a code i use to download data from a webpage, what i would like to add is the ability to download urls as text so i can then use the urls separately.

Code:
Sub ProcessHTMLPage(HTMLPage As MSHTML.HTMLDocument)

    Dim HTMLTable As MSHTML.IHTMLElement
    Dim HTMLTables As IHTMLElementCollection
    Dim HTMLRow As MSHTML.IHTMLElement
    Dim HTMLCell As MSHTML.IHTMLElement
    Dim RowNum As Long, ColNum As Integer
    Dim sht As Worksheet
    Set HTMLTables = HTMLPage.getElementsByTagName("table")
    
    For Each HTMLTable In HTMLTables
        
    Set sht = Sheets.Add(After:=Sheets(Sheets.Count))
        Range("A1").Value = HTMLTable.className
    
        RowNum = 2
        For Each HTMLRow In HTMLTable.getElementsByTagName("tr")
            
            ColNum = 1
            For Each HTMLCell In HTMLRow.Children
                Cells(RowNum, ColNum) = vbTab & HTMLCell.innerText
                ColNum = ColNum + 1
            Next HTMLCell
            
            RowNum = RowNum + 1
            
        Next HTMLRow
        
   Next HTMLTable
    
  
End Sub
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
Hi,

Maybe something like that : (change range as needed)

Sub urlastext()
Dim hp As Hyperlink
Dim rng As Range
Dim ws As Worksheet
Set rng = Range("A1:A30")


For Each hp In rng.Hyperlinks
hp.Delete
Next


End Sub
 

klatlap

Well-known Member
Joined
Sep 1, 2004
Messages
597
This solution works ok.

Code:
Sub ProcessHTMLPage(HTMLPage As MSHTML.HTMLDocument)

    Dim HTMLTable As MSHTML.IHTMLElement
    Dim HTMLTables As IHTMLElementCollection
    Dim HTMLRow As MSHTML.IHTMLElement
    
    
    
    Set HTMLTables = HTMLPage.getElementsByTagName("table")
        
        For Each HTMLTable In HTMLTables
            Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = HTMLTable.className
             
            
                For Each HTMLRow In HTMLTable.getElementsByTagName("a")
                    Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = HTMLRow.getAttribute("href")
                
                Next HTMLRow
       
                  
        Next HTMLTable
    
   
End Sub
 

klatlap

Well-known Member
Joined
Sep 1, 2004
Messages
597
Here is both codes for downloading URL's or can be changed to download other elements, make sure you go in to VBA editor Tools and References and reference the Microsoft XML Library, i am using Microsoft XML 6.0 as noted by the first Dim statement.

Code:
Option Explicit
Sub XML()
    Dim XMLPage As New MSXML2.XMLHTTP60
    Dim HTMLDoc As New MSHTML.HTMLDocument
    
    
    XMLPage.Open "GET", "https://en.wikipedia.org/wiki/Main_Page", False
    XMLPage.send

    HTMLDoc.body.innerHTML = XMLPage.responseText
    
    ProcessHTMLPageURLlink HTMLDoc
End Sub

Code:
Sub ProcessHTMLPageURLlink(HTMLPage As MSHTML.HTMLDocument)

    Dim HTMLTable As MSHTML.IHTMLElement
    Dim HTMLTables As IHTMLElementCollection
    Dim HTMLRow As MSHTML.IHTMLElement
    
    
    
    Set HTMLTables = HTMLPage.getElementsByTagName("table")
        
        For Each HTMLTable In HTMLTables
            Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = HTMLTable.className
             
            
                For Each HTMLRow In HTMLTable.getElementsByTagName("a")
                    Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = HTMLRow.getAttribute("href")
                
                Next HTMLRow
       
                  
        Next HTMLTable
    
   
End Sub

This video was a great resource https://www.youtube.com/watch?v=dShR33CdlY8
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,343
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top