| 0 |
Subject: Excel 97 Web Queries
Posted by: ukula
- [18618128] Mon, Jul 12, 2004, 10:09
I'm trying to set up a web query in Excel 97 for the first time. Basically I'm trying to grab the roster data off of a few select teams. I set up the query as:
WEB
1
http://fantasygames.sportingnews.com/baseball/fullseason/ultimate/game/frozen_roster.html?user_id=xxxxx&table_id=101
The problem I have is that it returns the data from the TSN opening screen as if I had never signed in. I have my browser opened in another window and I'm signed in to my TSN Ultimate League. Is there a line I need to add to get past the user id/password screen so that it recognizes me as being signed in?
Also, how can I just grab just the roster data without all of the other garbage that comes along with it, the TSN menus, ads, etc.....
Any help would be appreciated - again, this is my first time trying this.
ukula |
| 1 | Guru
ID: 330592710
Mon, Jul 12, 2004, 10:55
|
To effectively log yourself in to TSN, you need to load this URL first:
http://fantasygames.sportingnews.com/crs/home_check_reg.html?username=LOGIN&password=PASS
where LOGIN is your TSN login name and PASS is your password. Run this once at the beginning of your excel session and you'll remain logged in throughout that session.
|
| 2 | ukula
ID: 49651211
Mon, Jul 12, 2004, 12:10
|
Thanks Guru. I have two more questions:
1. How do I get just the roster data and not all of the other HTML code/tables on the TSN web page?
2. Is there a simple way to run through a number of different user_id/table_id combinations without Excel prompting me for the new parameters (which is where I am right now)?
ukula
|
| 3 | RecycledSpinalFluid
Dude
ID: 204401122
Mon, Jul 12, 2004, 12:11
|
You can macro script the process, so you don't need that extra file.
Let make some macros:
Sub loadSampleTeam()
Call login
curTableID = 1
curTableID = InputBox("Enter Table ID to use (Default is current)", "Table ID", curTableID)
sampleTeamID = InputBox("Enter Sample Team ID", "Load Sample Team", 2130)
frozenRosterURL = "http://fantasygames.sportingnews.com/hoops/fullseason/ultimate/game/frozen_roster.html" '(HOOPS EXAMPLE)
frozenRosterTables = "21,22,23" '(This is where you limit what is displayed)
Call loadWebQuery2(frozenRosterURL & "?user_id=" & sampleTeamID & "&table_id=" & curTableID, xlWebFormattingAll, frozenRosterTables)
End Sub
Sub loadWebQuery2(URLtoLoad, formatStyle, Optional webTablesToShow)
URLtoLoad = "URL;" & URLtoLoad
Set curTeamSht = Workbooks(1).Worksheets("CurrentTeam") 'The sheet you want to display it on
Set objOpenSheet = curTeamSht.QueryTables.Add(Connection:=URLtoLoad, _
Destination:=curTeamSht.Range("B1"))
With objOpenSheet
.WebFormatting = formatStyle
If formatStyle = xlWebFormattingAll Then
.WebSelectionType = xlSpecifiedTables
.WebTables = webTablesToShow
Else
.WebSelectionType = xlSpecifiedTables
.WebTables = webTablesToShow
End If
.BackgroundQuery = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
Sub login()
'
'
'
Worksheets("Config").Select 'Pluck Username and Password from the "Config" Worksheet (user defined data)
SWUserName = Range(findString("TSN_UserName")).Offset(1, 0).Value
SWPassword = Range(findString("TSN_Password")).Offset(1, 0).Value
loginURL = "http://fantasygames.sportingnews.com/crs/home_check_reg.html?username=" & SWUserName & "&password=" & SWPassword
Call loadWebQuery2(loginURL, xlNone, "21")
Call removeWebQuery
'
End Sub
Sub removeWebQuery()
Worksheets("CurrentTeam").Select
Cells.Select
Selection.Clear 'Contents
Selection.QueryTable.Delete
Selection.ColumnWidth = 3.12
Selection.RowHeight = 13.5
Range("A1").Select
End Sub
Now, I don't recall whether I coded this stuff for XL97, XL2000, XLXP or all of the above. I think XL97 was different.
|
| 4 | ukula
ID: 596221212
Mon, Jul 12, 2004, 13:29
|
Wow - Thanks rsf, I'll see if I can dig through the code and make something work. Brings me back to my old computer programming classes.
ukula
|
|
|
|
Post a reply to this message: (But first, how about checking out this sponsor?)
|
|