EU Date with time conversion to US

[polarisking]

I posted this a couple of days ago in the Excel area, and got a couple of Power Pivot responses. My dilemma is that I'm bringing in the EU Date with time, and it's not converting to US. In those cases where there's no time component, the conversion works just fine. Here's what I use using the value of 4 to denote DMY

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Test for EU Date Time Conversion for Chandoo.txt" _
        , Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "Test for EU Date Time Conversion for Chandoo"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(4)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

 

[p45cal]

Try this to add a new query table Using the sample .txt file you attached):

Sub AddNew()
Set QT = ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Test for EU Date Time Conversion for Chandoo.txt", Destination:=Range("$A$1"))
With QT
  .TextFilePlatform = 3    '850
  .TextFileOtherDelimiter = False
  .TextFileTabDelimiter = True
  .TextFileSemicolonDelimiter = False
  .TextFileCommaDelimiter = False
  .TextFileSpaceDelimiter = True
  .TextFileColumnDataTypes = Array(4, 1)
  .TextFileTrailingMinusNumbers = True
  .Refresh BackgroundQuery:=False
  'bring the time back into the first column:
  For Each cll In .ResultRange.Columns(1).Cells
    cll.Value = cll.Value + cll.Offset(, 1).Value
  Next cll
  .ResultRange.Columns(2).ClearContents    'clears the second column.
End With
End Sub

and this for refreshing it:

Sub Refresh()
Set QT = ActiveSheet.QueryTables(1)    'assumes it's the only query table on the sheet.
With QT
  .Refresh BackgroundQuery:=False
  For Each cll In .ResultRange.Columns(1).Cells
    cll.Value = cll.Value + cll.Offset(, 1).Value
  Next cll
  .ResultRange.Columns(2).ClearContents 'clears the 2nd column.
End With
End Sub

I'm guessing you're not just importing a column of date/times, in which case I'd need more information.