Change M(M)/D(D)/YYYY format = General ...

[adambc]

... to D(D)/M(M)/YYYY format = Date (so that I can sort old to new) ...

I am working with two extract files from another system that I cannot influence!

I am reading them into a WS in my WB using Power Query / Append ...

But in what should be date columns, the values (in the extracts, PQ is not changing them) are eg 7/1/2023 & 10/13/2022 and format = General (PQ is seeing them as Text) - whereas I need them to read 1/7/2023 & 13/10/2022 respectively AND format = Date ...

Can someone help me please/

Thanks ...

 

[adambc]

Which is what I did and created this module ...

Sub RefreshAllAndTextToDate()

ActiveWorkbook.RefreshAll

Columns("E:E").Select
    Selection.TextToColumns Destination:=Range( _
        "TicketsCombined[[#Headers],[Date Created]]"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True

Columns("F:F").Select
    Selection.TextToColumns Destination:=Range( _
        "TicketsCombined[[#Headers],[Date Created]]"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True

End Sub

But ... if I run it as is, the only piece that runs is the RefreshAll ie the TextToColumns part of the code doesn't run- if I comment out the RefreshAll after running it, only the Columns("E:E").Select runs ie it doesn't then run the Columns("F:F").Select piece of the VBA - what am I missing?!!!

Ignore ...

if I comment out the RefreshAll after running it, only the Columns("E:E").Select runs ie it doesn't then run the Columns("F:F").Select piece of the VBA

... my mistake ... but with full sub ONLY RefreshAll runs?!!!

 

[adambc]

Which is what I did and created this module ...

Sub RefreshAllAndTextToDate()

ActiveWorkbook.RefreshAll

Columns("E:E").Select
    Selection.TextToColumns Destination:=Range( _
        "TicketsCombined[[#Headers],[Date Created]]"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True

Columns("F:F").Select
    Selection.TextToColumns Destination:=Range( _
        "TicketsCombined[[#Headers],[Date Created]]"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True

End Sub

But ... if I run it as is, the only piece that runs is the RefreshAll ie the TextToColumns part of the code doesn't run- if I comment out the RefreshAll after running it, only the Columns("E:E").Select runs ie it doesn't then run the Columns("F:F").Select piece of the VBA - what am I missing?!!!

Ignore all, sorted with ...

Sub RefreshAllAndTextToColumnsColumnsEAndF()

    For Each objConnection In ThisWorkbook.Connections
        'Get current background-refresh value
        bBackground = objConnection.OLEDBConnection.BackgroundQuery
        
        'Temporarily disable background-refresh
        objConnection.OLEDBConnection.BackgroundQuery = False
        
        'Refresh this connection
        objConnection.Refresh
        
        'Set background-refresh value back to original value
        objConnection.OLEDBConnection.BackgroundQuery = bBackground
    Next
    
    Columns("E:E").Select
    Selection.TextToColumns Destination:=Range( _
        "TicketsCombined[[#Headers],[Date Created]]"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
        
    Columns("F:F").Select
    Selection.TextToColumns Destination:=Range( _
        "TicketsCombined[[#Headers],[Date Last Updated]]"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
End Sub