Getting Data from Web - where the URL is the value of the active c

[aegoodrich]

I have a list of about 200 URLs in a column in excel.

I want to write a macro that will:

Active Cell is A1. A1's value it the text
http://web1.ncaa.org/d1mfb/2002/Internet/ranking_summary/2002000000009teamoff.html

I need the macro to go to the webpage, get the data from Tables 3 and 4 on
the website, and paste them in a new sheet

 

[aegoodrich]

I have a list of about 200 URLs in a column in excel.

I want to write a macro that will:

Active Cell is A1. A1's value it the text
http://web1.ncaa.org/d1mfb/2002/Internet/ranking_summary/2002000000009teamoff.html

I need the macro to go to the webpage, get the data from Tables 3 and 4 on
the website, and paste them in a new sheet

Here is the code that gets recorded in excel 2007:

ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://web1.ncaa.org/d1mfb/2002/Internet/ranking_summary/2002000000009teamoff.html" _
, Destination:=Range("$A$1"))
.Name = "2002000000009teamoff"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3,4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

This works for me, except that I want the macro to go to the URL which is
the cell value of the current cell (before running the macro). So in the
above example, the value of the actuve cell is the text:
http://web1.ncaa.org/d1mfb/2002/Internet/ranking_summary/2002000000009teamoff.html

 

[Randy Harmelink]

I have a free open-source add-in that can grab the data for you using
user-defined function. I wrote the add-in functions because of
frustrations I had with collecting financial data from the web using
EXCEL Web Queries. For example, one of the user-defined functions can
grab individual table cells:

=RCHGetTableCell(URL,2,">Opponent",,,,3,"</table",,)

....would grab the data from the named cell "URL" (which I had
populated with your value below), then get the data from the 2nd
column of the 3rd row after finding the string ">Opponent" within the
source code of the web page. It returns this value to EXCEL:

Pittsburgh(20-26)

The add-in, documentation on its functions, and examples and
templates, can be found in the files area of this Yahoo Group:

http://finance.groups.yahoo.com/group/smf_addin/

 

[aegoodrich]

Well, I am so very smart... I figured it out for myself.

In case you need it ,here is the vba code that does the following:
1. Creates a new worksheet named "Working Data"
2. Then extracts data tables 3 and 4 from the webpage and pastes it on the
sheet "Working Data".
3. The difference here is that the URL for the webpage is the text of the
active cell prior to running the macro.
4. That is, say before running the macro, the active cell is A1 on Sheet1 -
and this cell has the text "http://www.WEBSITE.com".

Sub GetDataFromActiveCellWebSite()

Dim WEBNAME As String
WEBNAME = ActiveCell.Value


ActiveWorkbook.Worksheets.Add().NAME = "Working Data"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & WEBNAME, Destination:=Range("$A$1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3,4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


Hop that helps you.