Importing Web Page with Hyperlinks into Excel

[feltra]

Hi all,

I need to read a page from the web and import it in Excel, programatically (ie. thru a macro). This page contains some text that have hyperlinks... A typical example of the page is:


Sample Page with Links

Text line 1....
Text line 2...
.....
....
Link to Data Set 1
Link to Data Set 2
.....


The lines "Link to Data Set 1" and "Link to Data Set 2" are links to another page(s). I need to get THESE LINKS into the Excel sheet, along with the text. Is this possible at all?

I am open to experiementing with WebQuery or any other method, including editing the .iqv file manually, if that will work.


Alternative 1:
If it is simply not possible from within Excel, the first workaround is to try to get the source page of the web page. Is it possible to get the Source of the web page using WebQuery or any other methods in Excel VBA?

Alternative 2:
Any other roundabout method of using other tools such as PERL etc, and finally getting them into Excel?


Please note:
The option of copying manually by doing a select all and pasting into Excel or from IE 6 using Right-mouse-button option of Exporting to Excel is not a solution in this case, because, I need to access a whole lot of such pages (as given in the sample above) - about thousands of them... (over a period of time). So it has to be done programatically.

Advance thanks for any & all feedback.

Thanks & Best Regards,
-feltra

PS: I am just a beginner in Excel VBA and learn mostly by using Recorded Macros to understand whats happening, so please bear with me if i take time to understand... thanks.

[qff]

Hi

the following code will create a web query using this thread as the data source. The hyperlinks will appear in column H

Sub mywebquery()
Dim myURL As String
myURL = "URL;http://www.vbaexpress.com/forum/showthread.php?t=14167"
With ActiveSheet.QueryTables.Add(Connection:=myURL, Destination:= _
Range("A1"))
.Name = "showthread.php?t=14167"
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = """post109253"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = True
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With

'get hyperlinks to column H
Dim myRows As String
myRows = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To myRows
If Cells(i, 1).Hyperlinks.Count > 0 Then
Cells(i, 8).Value = Cells(i, 1).Hyperlinks(1).Address
End If
Next i

End Sub


Hope this helps

regards
qff

[feltra]

Hi Qff,

Thanks a lot for the feedback... I got the solution from another forum yesterday.. It turned out that not giving

.WebFormatting = xlWebFormattingAll


was the culprit. Once i gave that (instead of xlAll which i was using earlier), it worked like a charm.

Thanks for the detailed example... As I am still learning VBA and WebQuery, this will certainly help. I will try it out and let you know if I get stuck somewhere.


Thanks & Best Regards,
-feltra