Excel cell format change

[doris_day]

This may be obvious to those who know Excel better than me but I can't seem to change a cell format to the way I need.
Say I'm importing data from a web query and the data reads 2-4 in HTML, it appears in Excel as 02-Apr as it assumes it should be a date. I want it to read as 2-4 but can't find a format change that will do that.
As I said, this may be simple............but then so am I

[osknows]

Hi doris,

Try formatting the cell as text. However, sometimes even this won't work and you have to force excel to recognize it as text by prefixing an apostrophe when writing to the sheet. Usually, text with characters - or / causes problems.

Eg say you have variable Var = "2-4"

.range("A1").value = "'" & Var

[Ian]

There's an option to disable date recognition when you first set the web query up. You click the button "options" top right on my old version 2003 of Excel and tick "disable date recognition" - not sure where it is on later versions. You might have to start again as the cells will now be formatted as dates.

[doris_day]

Thanks for the replies. The easiest way to do this was using the disable date recognition, so thanks again. Yet another hidden option......

[osknows]

We'll speak again in a few months when none of your dates work

[doris_day]

Thanks os...btw, I've found a guy in India (arent they all there ?) that is willing to do some VS tutoring on Skype....he seems quite good but then again, you never know...I'll keep you updated on my progress

[Shaun]

osknows, maybe you could help me out with a similar problem as i am not sure what you are explaining.

I am importing this webpage http://formguide.cyberhorse.com.au/inde ... d&raceno=8

Now where it has stats like Track 8:1-2-0 somtimes the formating is just numbers even if i change it ti text when i import a bew page they get changed again.

It dose not change them all just some mainly if the first set of numbers is above 10.

Can you give me an example of how to fix this, i will provide my macro for the import.

Code: Select all

Sub Form()
Sheets("Form").Select
    Sheets("Form").Range("B1:J1000").Select
    Selection.ClearContents
    Columns("B:J").Select
    Selection.NumberFormat = "@"
    With Sheets("Form").QueryTables.Add(Connection:= _
        "URL;" & Sheets("Data").Range("A1"), Destination:=Sheets("Form").Range("$B$1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = True
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
Columns("B:J").Select
    Selection.Copy
    Application.DisplayAlerts = False
    Sheets("Data").Select
    Range("AA1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AA:AA").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("AA1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1)), _
        TrailingMinusNumbers:=True
        Columns("AC:AC").Select
    Selection.TextToColumns Destination:=Range("AC1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
        Application.DisplayAlerts = True
        Range("A1").Select
       
End Sub


The macro does a few other things but i need to get this fixed as it is part of an automated ratings program i use.

[osknows]

Hi Shaun,

Do you have an example of the text that comes out incorrect, both what it should be and what it changes to?

I've tried your code and the link and everything appears to extract correctly.

[Shaun]

I will run a few tests today and then if i could e-mail you the sheet may give you a better idea, it is part of a large workbook but i will just send the sheet.

[Shaun]

I am having trouble finding an example today as all the races seam fine, as i said it is a specific event that creates the problem and i guess today is fine.

I will post back when this happens again, thanks for your help.

[Shaun]

Found an issue with this page from today.

http://formguide.cyberhorse.com.au/inde ... s&raceno=6

If you import to cell A1 the problem is in H79 it should be displayed as 27:9-5-1 but instead is displayed as 40549.13125 in my script i have .WebDisableDateRecognition = True this solves any date issue with some of the other formats but won't solve this.

[osknows]

I find Webqueries a nightmare as they tend to do there own thing with very little control over the results. The example below show a quick and 'dirty' way around it by replacing all instances of ":" with "::" thereby ensuring Excel won't recognise these strings as a date

Code: Select all

Sub form()
Sheets("Form").Select
    Sheets("Form").Range("B1:J1000").Select
    Selection.ClearContents
    Columns("B:J").Select
    Selection.NumberFormat = "@"
    formhtml = ExecuteWebRequest(ThisWorkbook.Sheets("Data").Range("A1").Value)
    formhtml = Replace(formhtml, ":", "::")
    outputtext (formhtml)
   
    Set temp_qt = ThisWorkbook.Sheets("Form").QueryTables.Add(Connection:= _
            "URL;" & ThisWorkbook.Path & "\temp.txt" _
            , Destination:=ThisWorkbook.Sheets("Form").Range("A1"))
    With temp_qt
        .Name = "test"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = True
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Set temp_qt = Nothing
    Kill ThisWorkbook.Path & "\temp.txt"
    If ThisWorkbook.Connections.Count > 0 Then ThisWorkbook.Connections.Item(ThisWorkbook.Connections.Count).Delete

Columns("B:J").Select
    Selection.Copy
    Application.DisplayAlerts = False
    Sheets("Data").Select
    Range("AA1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AA:AA").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("AA1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1)), _
        TrailingMinusNumbers:=True
        Columns("AC:AC").Select
    Selection.TextToColumns Destination:=Range("AC1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
        Application.DisplayAlerts = True
        Range("A1").Select
       
End Sub

Public Function ExecuteWebRequest(url As String) As String
    Dim oXHTTP As Object
    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
    oXHTTP.Open "GET", url, False
    oXHTTP.send
    ExecuteWebRequest = oXHTTP.responseText
    Set oXHTTP = Nothing
End Function

Public Function outputtext(text As String)
Dim MyFile As String, fnum As String
        MyFile = ThisWorkbook.Path & "\temp.txt"
        fnum = FreeFile()
        Open MyFile For Output As fnum
        Print #fnum, text
        Close #fnum
End Function


[Shaun]

I will give it a try and let you know.

i downloaded another sheet some time back that get data from the web but they didn't use web queries not sure what they did i will have to dig it out and try to understand it.

[Shaun]

Looks to be working, i had to make a few changes as the data import changed a little and i had to compensate for :: but all is good.

Maybe you can answer a question for me, the script has increased in speed, what did i have in mine that would be slowing it down?

[osknows]

I expect it will be because MSXML2 is much faster than a webquery at downloading data.