jproffer
12-08-2009, 09:11 AM
I have a code that pulls a table from a website with many of the cells in the table formatted as:
1 - 3
1 - 9
9 - 4
etc.
When the table is pulled and added to the worksheet, excel insists on formatting those entries as dates.
Question is, is there a way to stop that. I've tried to put "Format" in front of the querry table add line and " @ " after to format as text but that errors out. Also tried to just put " Cells.Numberformat= "@" " just before the table is pulled (and if I stop there and go to the WS and type in, say "9 - 4", it stays that way), but when it pulls the table from the website it overwrites the formatting...I guess...???
Am I just putting it in the wrong place, or is there another way?
Sub Pull_NCAA_Football_Standings()
Sheets.Add before:=Sheets(1)
Sheets(1).Name = "NCAAF Standings"
Sheets("NCAAF Standings").Activate
With ActiveSheet.QueryTables.Add("URL;http://espn.go.com/college-football/standings", Range("A1"))
.Refresh False
.PreserveFormatting = True ' <---I've tried true and false here with no change
End With
End Sub
BTW, if I put the cells formatting after the table is pulled, it changes each entry to the 5 digit "basic date" format (or whatever you call it, lol :) ).
Any help is greatly appreciated.
1 - 3
1 - 9
9 - 4
etc.
When the table is pulled and added to the worksheet, excel insists on formatting those entries as dates.
Question is, is there a way to stop that. I've tried to put "Format" in front of the querry table add line and " @ " after to format as text but that errors out. Also tried to just put " Cells.Numberformat= "@" " just before the table is pulled (and if I stop there and go to the WS and type in, say "9 - 4", it stays that way), but when it pulls the table from the website it overwrites the formatting...I guess...???
Am I just putting it in the wrong place, or is there another way?
Sub Pull_NCAA_Football_Standings()
Sheets.Add before:=Sheets(1)
Sheets(1).Name = "NCAAF Standings"
Sheets("NCAAF Standings").Activate
With ActiveSheet.QueryTables.Add("URL;http://espn.go.com/college-football/standings", Range("A1"))
.Refresh False
.PreserveFormatting = True ' <---I've tried true and false here with no change
End With
End Sub
BTW, if I put the cells formatting after the table is pulled, it changes each entry to the 5 digit "basic date" format (or whatever you call it, lol :) ).
Any help is greatly appreciated.