date order changes when opening csv file via macro

Posted on 2006-05-25
Last Modified: 2010-04-30
I have a file that is used everyday. I wrote a macro that deletes the contents of a worksheet and then opens a csv file, copies the data into the worksheet and then closes the csv file.
I work with dates in the dd/mm/yyyy format (Ireland). When the csv file is opened the days 01 to 09 reverse with the month
example 2nd May 2006 02/05/2006 becomes 05/02/2006. - dates 10 - 31 are fine example - 24th May 2006 stays 24/05/2006

If I open the csv file directly with excel the dates are fine. It seems that using the macro upsets it!!! I need to import the csv file via a macro etc.
Anyone got any ideas??

Question by:ptp2
    LVL 6

    Expert Comment

    Please go to "Control Panel" -> "Regional and Language Options" and select "English (Ireland)".

    LVL 1

    Author Comment

    Hi Jungle,

    Thanks for your input. My settings are set to English (Ireland) already. The problem only occurs when opening the csv file via a macro.


    LVL 2

    Accepted Solution

    Don't OPEN the .CSV file - IMPORT the data instead.

    Using Excel 2002 I reproduced your problem with this macro:
        Workbooks.Open Filename:= _
            "C:\Documents and Settings\david\My Documents\book1.csv"

    The following macro worked with dates in English / Irish format:

        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;C:\Documents and Settings\david\My Documents\Book1.csv", Destination:= _
            .Name = "Book1"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With

    Or if you want to record the macro and change a few options:

    Data - Import External Data - Import data.
    {select filename}
    Delimited - NEXT
    comma - NEXT
    DMY for date format      - NEXT      <<<<------------------------------------------------ this is what sorts it.
    LVL 1

    Author Comment

    Hi dcp002,

    Thanks for help. Sorry about the delay in replying. I took your advise and used the macro route and it worked.

    Thanks again

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now