Convert Date to Text in VBA Excel

[Blackfirexray]

Hi!

As a new member I've search thru the forum in the hunt for an answer to question, but no luck so far. There are som similar threads but none with a specific anser to my issue. I've created a workbook with a lot of soccerscores. At this point I'm forced to input the scores manually, but I'm aiming to have this automated. Tried to import tables from web with the function "From web" in the data menu. Worked quite ok until a certain game scores 1-1, and my Excelsheet turns it into "01-jan", and other dates as well, like "2-1" turns into "01-feb". Well, VBA seemed like a possible way, so I did that and it works fine importing the tables until the above mentioned issue arises, the outcome is the same.

Any advise on what to do? Preferrably I'd like to have it all fixed in VBA, but I'm thankful for any advise.

 

[steve the fish]

If the cell is text formatted it shouldnt happen. It depends on how the cell is filled. Other than that a single quote placed in front such as:

'2-1

 

[Blackfirexray]

Thank you for your answer. I'm familiar with " '2-1 ", but unfortunately it aint helping me. I think that I need to convert "2-1" to text before importing it to Excel. As shown below inside the parenthesis is what I get from my VBA-code (in separate columns), but it should be as shown far right (2-3).

( 2 Chelsea2 - Burnley0 03-feb FT 2 87 - 10 - 3 ) 2-3

 

[steve the fish]

It may be beter to share the VBA and if it isnt apparent the place where it gets its data.

 

[Blackfirexray]

Okej, I use two Subroutines:

Sub BrowseToStryktipsetXML()


Dim XMLPage As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument

XMLPage.Open "GET", "http://tipsrader.se//spel/stryktips", False
XMLPage.send

HTMLDoc.body.innerHTML = XMLPage.responseText


ProcessHTMLPage HTMLDoc

End Sub


And:

Sub ProcessHTMLPage(HTMLPage As MSHTML.HTMLDocument)


Dim HTMLTable As MSHTML.IHTMLElement
Dim HTMLTables As MSHTML.IHTMLElementCollection
Dim HTMLRow As MSHTML.IHTMLElement
Dim HTMLCell As MSHTML.IHTMLElement
Dim RowNum As Long, colnum As Integer
Set HTMLTables = HTMLPage.getElementsByTagName("table")

For Each HTMLTable In HTMLTables

Worksheets.Add
Range("A1").Value = HTMLTable.className
Range("B1").Value = Now

RowNum = 2
For Each HTMLRow In HTMLTable.getElementsByTagName("tr")
'Debug.Print vbTab & HTMLRow.innerText

colnum = 1

For Each HTMLCell In HTMLRow.Children
Cells(RowNum, colnum) = HTMLCell.innerText
colnum = colnum + 1

Next HTMLCell
RowNum = RowNum + 1
Next HTMLRow

Next HTMLTable

End Sub

[TABLE="width: 536"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD]The data comes from http://tipsrader.se/spel/stryktips[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][TABLE="width: 536"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

This should work. Bear in mind all cells with be formatted now as text. After the line Worksheets.Add in ProcessHTMLPage put this:

Cells.NumberFormat = "@"

 

[Blackfirexray]

Many thanks, works perfectly fine!!!