• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

EU Date with time conversion to US

polarisking

Member
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

Code:
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
 

Attachments

p45cal

Well-Known Member
Try this to add a new query table Using the sample .txt file you attached):
Code:
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:
Code:
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.
 
Last edited:
Top