Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Location
    Wollongong, New South Wales, Australia
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forwards compatability of Excel (xl2000)

    I have written code to extract data from text files dumped from an oracle database in which date are in short format dd/mm/yyyy.
    Originally the code was written in xl97, as there are over 2000 PCs on site, may of which still use xl95, I re-wrote the code for xl95.
    I am presently using XL2000, and have found what appears to be a compatibility problem with date times extracted from the text files.
    Being in Australia date - time stamps are commonly dd/mm/yyyy hh:mm:ss, The code I am using, even though the PC is set to Australian english,
    and the FieldInfo is set to option 4 (xlDMYFormat DMY date) the code will only read in dates in american format (mm/dd/yyyy), consequently when it encounters a date greater than the 12th day of the month it converts this into a string.
    Is there any way of overcoming this issue?
    I am currently trying to get the dates in long format (dd/mmmm/yyyy)
    PS when the data is opened manually using the text import wizard it extracts fine.
    The code I am using is:

    Workbooks.OpenText Dirname & fileName, Origin:=xlWindows, _
    StartRow:=1, DataType:=xlDelimited, TextQualifier _
    :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=True, Comma:=False, Space:=False, Other:=False, _
    FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 4), Array _
    5, 4), Array(6, 1), Array(7, 1))
    Thanks,
    Harold Kaul

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forwards compatability of Excel (xl2000)

    Having similar regional settings, I sympathise with you. VBA speaks American and that can lead to terrible results when one recorded a macro .

    To overcome the problem you described I used this macro (this processes column A as far as there are filled cells in column A):

    <pre>Sub ProcessDates()
    Dim MyCell As Range

    With ThisWorkbook.Worksheets("ImportSheet")
    For Each MyCell In .Range(.Cells(2, 1).Address, Cells(Application.CountA(.Columns(1)), 1).Address)
    sString = MyCell.Value
    If InStr(sString, "-") = 0 Then
    sMonth = Left(sString, InStr(sString, "/") - 1)
    sTemp = Right(sString, Len(sString) - InStr(sString, "/"))
    sDay = Left(sTemp, InStr(sTemp, "/") - 1)
    sTemp = Right(sTemp, Len(sTemp) - InStr(sTemp, "/"))
    MyCell.Value = sMonth & "-" & sDay & "-" & sTemp
    Else
    sDay = Left(sString, InStr(sString, "-") - 1)
    sTemp = Right(sString, Len(sString) - InStr(sString, "-"))
    sMonth = Left(sTemp, InStr(sTemp, "-") - 1)
    sTemp = Right(sTemp, Len(sTemp) - InStr(sTemp, "-"))
    MyCell.Value = sMonth & "-" & sDay & "-" & sTemp
    End If
    Next
    End With
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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