Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Data from Web - where the URL is the value of the active c
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/Inte...09teamoff.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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Data from Web - where the URL is the value of the active c
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/Inte...09teamoff.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/Inte...09teamoff.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Data from Web - where the URL is the value of the activec
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/ On Aug 2, 3:14 pm, aegoodrich wrote: 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/Inte...y/200200000000... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Data from Web - where the URL is the value of the active c
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row select mode to highlight active row of active cell | Excel Discussion (Misc queries) | |||
run macro although blinking cursor is active in an active cell | Excel Programming | |||
Data Connection - Active Directory | Excel Discussion (Misc queries) | |||
HOW TO COPY 480 ACTIVE E-MAIL ADDRESSES CLM "G" ON AN ACTIVE EXCE. | Excel Discussion (Misc queries) | |||
Active X and Data Grid | Excel Programming |