Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    We use a browser-based application that exports query result sets to Excel. Actually, it creates a tab-delimited text file, and then launches it in Excel. (I'm using Office 2003; some users have Office 2007.)

    The text file contains dates formatted as dd/mm/yyyy 00:00:00. The problem arises when Excel tries to convert this column and gets it terribly wrong. Where the first value is in the range of 1-12, it reads the date as an mm/dd/yyyy string and creates an incorrect date value. For all other values, it simply leaves the string as it is.

    Opening the file using File > Open, the Text Import Wizard seems to promise that it can interpret the column as DMY, but actually, it does not work any differently than Excel's automatic detection.

    If I go to Start > Settings > Control Panel > Regional and Language Options > Customize... > Date tab and change the short date format to dd/MM/yyyy, then Excel converts all of the dates correctly. But this is not an ideal solution because the user cannot print in the expected format without creating custom date formats or reverting the control panel setting and restarting Excel.

    (We cannot change the format of the export file generated by the application; it will be several months before the vendor can implement a solution.)

    Any thoughts on an easier way to work around this? Or will a macro or other intermediate processing be required?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    When I do this interactively, Excel ignores the DMY setting, but if I record a macro of this action, it works correctly when run.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    [quote name='HansV' post='778537' date='05-Jun-2009 14:12']When I do this interactively, Excel ignores the DMY setting, but if I record a macro of this action, it works correctly when run.[/quote]
    I was not able to record the Open Text Wizard in Excel 2003 (the macro is empty), but I think I have created an equivalent procedure. Unfortunately, it doesn't work any better.

    [codebox]Sub ImportTextFileDMY7()
    ' Get file name
    Dim fd As FileDialog, strFile As String
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Export File", "*.txt"
    .InitialView = msoFileDialogViewDetails
    .Title = "Pick File for Date Reformatting"
    .ButtonName = "Reformat"
    If .Show = -1 Then
    strFile = .SelectedItems(1)
    Set fd = Nothing
    Else
    Set fd = Nothing
    Exit Sub
    End If
    End With
    ' Get date column
    Dim strColumn As String
    strColumn = Trim(InputBox("Which column has the data you need to switch?", "Enter Column Number", "7"))
    ' Open and parse
    If IsNumeric(strColumn) Then
    Workbooks.OpenText Filename:=strFile, DataType:=xlDelimited, Tab:=True, FieldInfo:=Array(Array(CInt(strColumn), xlDMYFormat))
    Else
    Workbooks.OpenText Filename:=strFile, DataType:=xlDelimited, Tab:=True
    End If
    End Sub[/codebox]

    Edit: I tried adding "Local:=False" but it didn't make the parser any more obedient...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I suspect users will have to change their control panel settings temporarily and import interactively. VBA is too US-centric.

    The alternative would be to use the old BASIC text file manipulation instructions, read the file line by line and parse the dates yourself but that's not very attractive either.

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    [quote name='HansV' post='778551' date='05-Jun-2009 15:22']The alternative would be to use the old BASIC text file manipulation instructions, read the file line by line and parse the dates yourself but that's not very attractive either.[/quote]
    It's not too bad, except that it takes longer to browse to the file than to process it.

    The following code is a bit fragile in that it assumes consistent data and requires / delimiters; it's not the most efficient string handling, but it passed the first round of testing and it's Friday afternoon.

    For future reference:
    [codebox]Sub GetExportAndSwapDate()
    ' Get file name
    Dim fd As FileDialog, strFile As String
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Export File", "*.txt"
    .InitialView = msoFileDialogViewDetails
    .Title = "Pick File for Date Reformatting"
    .ButtonName = "Reformat"
    If .Show = -1 Then
    strFile = .SelectedItems(1)
    Set fd = Nothing
    Else
    Set fd = Nothing
    Exit Sub
    End If
    End With

    ' Open File for Reading
    Dim intFile1 As Integer, strLineIn As String, intRowCount As Integer, _
    strFields() As String, intLoopCount As Integer, strTemp As String
    intFile1 = FreeFile
    Open strFile For Input As #intFile1

    ' Loop Lines, fix dates, add to workbook
    Do While Not EOF(intFile1)
    Line Input #intFile1, strLineIn
    strFields = Split(strLineIn, vbTab)
    intRowCount = intRowCount + 1
    For intLoopCount = 0 To UBound(strFields)
    If InStr(1, strFields(intLoopCount), "0:00") Then
    strTemp = Trim(Left(strFields(intLoopCount), InStr(1, strFields(intLoopCount), " ")))
    strTemp = Split(strTemp, "/")(1) & "/" & Split(strTemp, "/")(0) & "/" & Split(strTemp, "/")(2)
    strFields(intLoopCount) = strTemp
    End If
    ActiveSheet.Cells(intRowCount, intLoopCount + 1).Value = strFields(intLoopCount)
    Next
    Loop
    Close #intFile1
    End Sub[/codebox]

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='778551' date='05-Jun-2009 18:22']I suspect users will have to change their control panel settings temporarily and import interactively. VBA is too US-centric.

    The alternative would be to use the old BASIC text file manipulation instructions, read the file line by line and parse the dates yourself but that's not very attractive either.[/quote]

    Is there any VBA method of:
    1. Capturing the Control Panel > Regional Settings;
    2. Setting the Regional Settings to "English (United Kingdom)";
    3. Importing; parsing; and formatting the data; then
    4. Resetting the Regional Settings to their original value?
    Regards
    Don

  7. #7
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='wdwells' post='778567' date='06-Jun-2009 01:15']Is there any VBA method of:
    1. Capturing the Control Panel > Regional Settings;
    2. Setting the Regional Settings to "English (United Kingdom)";
    3. Importing; parsing; and formatting the data; then
    4. Resetting the Regional Settings to their original value?
    [/quote]
    You can capture the current value of most Regional settings using Application.International(...) but these are read only, so only give you point 1.

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jscher2000' post='778528' date='05-Jun-2009 16:34']We use a browser-based application that exports query result sets to Excel. Actually, it creates a tab-delimited text file, and then launches it in Excel. (I'm using Office 2003; some users have Office 2007.)

    The text file contains dates formatted as dd/mm/yyyy 00:00:00. The problem arises when Excel tries to convert this column and gets it terribly wrong. Where the first value is in the range of 1-12, it reads the date as an mm/dd/yyyy string and creates an incorrect date value. For all other values, it simply leaves the string as it is.

    Opening the file using File > Open, the Text Import Wizard seems to promise that it can interpret the column as DMY, but actually, it does not work any differently than Excel's automatic detection.

    If I go to Start > Settings > Control Panel > Regional and Language Options > Customize... > Date tab and change the short date format to dd/MM/yyyy, then Excel converts all of the dates correctly. But this is not an ideal solution because the user cannot print in the expected format without creating custom date formats or reverting the control panel setting and restarting Excel.

    (We cannot change the format of the export file generated by the application; it will be several months before the vendor can implement a solution.)

    Any thoughts on an easier way to work around this? Or will a macro or other intermediate processing be required?[/quote]Hi Jefferson

    I was able to achieve the required results on the attached text file with this code
    Code:
    Sub DMY()
    	
    	'
    		Workbooks.OpenText Filename:= _
    			"C:\Documents and Settings\W. Donald Wells\My Documents\Trash\Copy of Date.txt" _
    			, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    			xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
    			, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
    			Array(2, 4)), TrailingMinusNumbers:=True
    	End Sub
    The date column being Col B is controlled by:
    FieldInfo:=Array(Array(1, 1), Array(2, 4))

    The first argument identifies the field;
    The second argument identifies the way the data field is to be interpreted, as follows:
    1 - General
    2 - Text
    3 - Date MDY
    4 - Date DMY
    5 - Date YMD
    6 - Date MYD
    7 - Date DYM
    8 - Date YDM
    9 - Do not import this field
    p.s. Check the OpenText Method in VBA Help
    Attached Files Attached Files
    Regards
    Don

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='jscher2000' post='778528' date='06-Jun-2009 06:34']We use a browser-based application that exports query result sets to Excel. Actually, it creates a tab-delimited text file, and then launches it in Excel. (I'm using Office 2003; some users have Office 2007.)

    ...

    Any thoughts on an easier way to work around this? Or will a macro or other intermediate processing be required?[/quote]

    Hi Jefferson,

    The following macro converts date strings and values in the selected range between mm/dd/yyyy and dd/mm/yyyy. The output is in the form of date values that you can format as dates via Format|Cells|Number|Date.
    Code:
    Sub ConvertDateFormat()
    Dim DtRange As Range
    Dim oCell As Range
    Dim oTxt As String
    If Selection.Cells.Count = 1 Then
      Set DtRange = ActiveCell
    Else
      Set DtRange = Selection
    End If
    With Application
      On Error Resume Next ' In case there are no xlConstants or convertible dates in DtRange
      For Each oCell In DtRange.SpecialCells(xlConstants)
    	oTxt = oCell.Text
    	If UBound(Split(oTxt, "/")) = 2 Then _
    	  oCell.Value = CDate(Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/" & Split(oTxt, "/")(2))
      Next oCell
    End With
    End Sub
    Note that no conversion is performed if the date is embedded in a formula or the output would be an invalid date.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    [quote name='macropod' post='778584' date='05-Jun-2009 23:52']The following macro converts date strings and values in the selected range between mm/dd/yyyy and dd/mm/yyyy.[/quote]
    Thanks, macropod. The property oCell.Text looks to be very handy. The reason I say that is DMY dates such as 4/06/2009 0:00 are being converted to MDY date values (5-digit numbers) while dates such as 14/06/2009 0:00 are being left as strings. Using the .Text property appears to work around the problem of this underlying difference. (I just need to get rid of the time value from the strings that aren't converted.) Will test in the next day or so.

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    [quote name='wdwells' post='778573' date='05-Jun-2009 18:24']The date column being Col B is controlled by:
    FieldInfo:=Array(Array(1, 1), Array(2, 4))[/quote]
    Thanks, Don. I wonder whether the help file was incorrect that I could just list the conversion for the one column ("G") I wanted to specify. I could try creating an array that covers all 11 columns and see whether Excel pays attention. (I was hoping that wouldn't be necessary because the column sequence might change...)

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jscher2000' post='778708' date='07-Jun-2009 14:23']Thanks, Don. I wonder whether the help file was incorrect that I could just list the conversion for the one column ("G") I wanted to specify. I could try creating an array that covers all 11 columns and see whether Excel pays attention. (I was hoping that wouldn't be necessary because the column sequence might change...)[/quote]
    As I understand it, you can specify only the date field and all other fields will be considered "General" where Excel guesses how to treat each field depending on what it finds. If it finds something that looks like a date, it will have its way with it.

    Added later:
    I believe that if you specify the Date DMY conversion for all fields, unless the field looks like a date, it will be treated as General.
    Regards
    Don

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    I ended up changing part of this, because when I had just A1 active, the macro would process the entire sheet. Maybe something about "SpecialCells" that I don't fully understand.
    [quote name='macropod' post='778584' date='05-Jun-2009 23:52'][codebox]Sub ConvertDateFormat()
    ' Flips dd/mm/yyyy dates to mm/dd/yyyy dates
    Dim DtRange As Range, oCell As Range, strText As String
    If Selection.Cells.Count < 2 Then
    MsgBox "Please reselect the date range and try again.", vbOKOnly
    Exit Sub
    Else
    Set DtRange = Selection
    End If
    On Error Resume Next ' In case there are no xlConstants or convertible dates in DtRange
    For Each oCell In DtRange.SpecialCells(xlConstants)
    strText = oCell.Text
    If InStrRev(strText, " ") > 8 Then ' Trim any time value
    strText = Left(strText, InStrRev(strText, " ") - 1)
    End If
    If UBound(Split(strText, "/")) = 2 Then ' Rebuild DMY to MDY
    oCell.NumberFormat = "m/dd/yyyy" ' pre-apply custom format
    oCell.Value = CDate(Split(strText, "/")(1) & "/" & Split(strText, "/")(0) & "/" & Split(strText, "/")(2))
    End If
    Next oCell
    On Error GoTo 0
    Set oCell = Nothing
    Set DtRange = Nothing
    End Sub[/codebox]

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I've run into this too. SpecialCells in VBA works (more or less) the same way as Edit | Go to... > Special... does interactively: when applied to a multi-cell range, it's restricted to that range, but when applied to a single cell, it looks at the entire sheet.

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='jscher2000' post='779166' date='10-Jun-2009 11:26'][/quote]
    How about:

    Set DtRange = ActiveCell
    Else
    Set DtRange = Selection.SpecialCells(xlConstants)
    End If
    For Each oCell In DtRange
    ...
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •