copy paste from html to excel

[rakic]

Hi !

Usually results of Football (Soccer) matches are given like 3:1, 2:2, 0:1. See picture“ORIGINAL”
ORIGINAL
12. kolo GERMANY 1
Dan Cas R.b. 45' 90'
Sub 15:30 5201 Bayern M. Cottbus 1:1 3:1
Sub 15:30 5202 S Hannover Bochum 1:1 2:2
Sub 15:30 5203 S Hansa Dortmund 0:1 0:1
Sub 15:30 5204 S Hertha Hamburger 0:0 2:0
Sub 15:30 5205 S K'lautern München 1860 0:0 0:0



….When I want to copy these results and after that to paste them into EXCEL, they are changed. After the paste they become 3:01, 2:02, 0:01……… and so on.
See picture “RESULT OF PASTING IN EXCEL”
RESULT OF PASTING IN EXCEL
12. kolo GERMANY 1
Dan Cas R.b. 45' 90'
Sub 15:30 5201 Bayern M. Cottbus 1:01 3:01
Sub 15:30 5202 S Hannover Bochum 1:01 2:02
Sub 15:30 5203 S Hansa Dortmund 0:01 0:01
Sub 15:30 5204 S Hertha Hamburger 0:00 2:00
Sub 15:30 5205 S K'lautern München 1860 0:00 0:00


Is there a way to prevent this change of format to TIME.
The best thing would be to place result in two columns like :

Bayern M. Cottbus 3 1
Hannover Bochum 2 2
Hansa Dortmund 0 1
If it is possible.

Thank you in advance

DORDE

 

[ChrisUK]

I tried recording a macro and got this answer back - well, I've modified it slightly to suit your post.

Hope this helps

Chris

URL = "URL;" & Range("A1").Value
SiteNameStart = InStr(1, URL, "www.")
SiteNameEnd = InStr(SiteNameStart, URL, ".co")
SiteName = Mid(URL, SiteNameStart, SiteNameEnd - SiteNameStart)

With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("A1"))
.Name = SiteName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

End Sub

 

[rakic]

Answers are very interesant, but for last two from ChrisUK I feel a lack of knowledge. Finally I’ found a solution that satisfied me, and may be interest for reged. After opening a file in Internet Explorer, I am going to File – Edit with MICROSOFT FRONT PAGE, then make a replace with from “:” to “---“. With copy-paste transfer new version of file to EXCEL farther. By this way I loose any “Time formatting” and it may be, for other occasions bad thing of this method.

Dorde