Query website from spreadsheet

[Scoop]

I have used web queries before but never tried to do anything like
this...

I have a spreadsheet with a column of URL's. What I would like to do
is write a macro that can loop through the "URL" column and query each
website for the contents of the meta description tag and store that in
an adjacent cell.

i.e. A site with the tag: <META NAME="Description" CONTENT="Website
Description ."> would return the text "Website Description"

I don't even know where to start...

 

[ryguy7272]

This gets a little technical, but it's not too bad. Basically, you need to
set up a loop as such:

Sub HistData()

Application.ScreenUpdating = False

Dim str1 As String
Dim str2 As String
Dim c As Range

Dim Stocks As Range


Dim bFound As Boolean
Dim ws As Worksheet


Set Stocks = Application.InputBox( _
"Type 'Symbols' in the input box below", Type:=8)

For Each c In Sheets("Sheet1").Range("Symbols")

bFound = False
For Each ws In Worksheets
If ws.Name = c.Value Then
bFound = True
Exit For
End If
Next ws

If bFound = False Then
Worksheets.Add.Name = c.Value
End If

'----------------------------------------------------------
Sheets(c.Value).Select
Cells.Select
Range("A1:IV65536").ClearContents

str1 = "URL;http://finance.yahoo.com/q/ks?s=" & _
c.Value

With ActiveSheet.QueryTables.Add(Connection:=str1 _
, Destination:=Range("A1"))

.Name = str1

.Name = "ks?s=c.Value"


.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With

Next c

End Sub

This is what I use for querying stock data from Yahoo. You can loop through
200 stock symbols. Excel could handle much, much, much more...the limitation
of 200 is on the side of Yahoo. Anyway, hope that helps. BTW, there is no
express or implied liability with that code. The code does exactly what you
would expect it to do, but it hasn't helped me to become a millionaire...


Regards,
Ryan---