[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

date order changes when opening csv file via macro

Posted on 2006-05-25
4
Medium Priority
?
216 Views
Last Modified: 2010-04-30
Hi,
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??

Peter
0
Comment
Question by:ptp2
  • 2
4 Comments
 
LVL 6

Expert Comment

by:junglerover77
ID: 16766652
Please go to "Control Panel" -> "Regional and Language Options" and select "English (Ireland)".

Regards,
Jungle
0
 
LVL 1

Author Comment

by:ptp2
ID: 16767680
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.

Regards
Peter

0
 
LVL 2

Accepted Solution

by:
dcp002 earned 2000 total points
ID: 16776402
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"
    Columns("A:A").EntireColumn.AutoFit

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:= _
        Range("A1"))
        .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.
etc.
0
 
LVL 1

Author Comment

by:ptp2
ID: 16791113
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
Peter
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses

873 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