Holding the formatting on a table from a website

[jproffer]

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.

[mdmackillop]

Date formats are annoyingly intrusive and persistent!
Try this
[VBA]
Sub Pull_NCAA_Football_Standings()
Dim rng As Range
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
With ActiveSheet
Set rng = Intersect(.Columns("B:C"), .UsedRange)
For Each cel In rng
If IsDate(cel) Then
txt = cel
cel.ClearContents
cel.NumberFormat = "General"
cel.Value = "'" & Month(txt) & " - " & Day(txt)

End If
Next
End With
End Sub

[/VBA]

[jproffer]

Thank you so much for the help...

Date formats are annoying indeed.

OK, tried that and it converts MOST of them just right. A few of the teams have a record of 0 - something...0 - 8 is one of them I think. When it changes that one it makes it 8 - 1, which is confusing to me because if I remember correctly, before when it pulled the table the winless teams didnt change to dates...it stayed as 0 - 8 which is not a date format (that I know of at least) but yet the further code you gave (brilliant by the way) still decides that it IS a date, and changes it to 8 - 1.

So....where the H%#L does it get the 1?? I can see it flipping it around...but changing the 0 to a 1 really leaves me wondering.