Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query website from spreadsheet
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query website from spreadsheet
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--- -- RyGuy "Scoop" wrote: 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Web Query for Java Applet driven data website | Excel Discussion (Misc queries) | |||
website prevents excel import web query | Excel Discussion (Misc queries) | |||
Excel spreadsheet on website | Excel Discussion (Misc queries) | |||
nse website query | Excel Programming | |||
How do I query a website | Excel Programming |