+ Reply to Thread
Results 1 to 3 of 3

Copy-paste from csv to xls macro > wrong date format

Hybrid View

Gero Copy-paste from csv to xls... 04-27-2009, 02:55 PM
Gero Re: Copy-paste from csv to... 04-27-2009, 04:16 PM
Gero Re: Copy-paste from csv to... 04-30-2009, 06:12 AM
  1. #1
    Registered User
    Join Date
    04-27-2009
    Location
    Bahrain
    MS-Off Ver
    Excel 2003
    Posts
    7

    Copy-paste from csv to xls macro > wrong date format

    Hello and thanks for your time reading this.
    I'm exporting a csv file from a membership database with the attendance of members. With a macro i open the file and copy the contend to excel.
    With the macro the column for the date has *randomly* wrong formats.
    Example the date in column B:
    Aasia Tallah 19-03-2009 19:33
    Aasia Tallah 17/03/09 7:49 pm
    Aasia Tallah 15/03/09 7:53 pm
    Aasia Tallah 14/03/09 6:34 pm
    Aasia Tallah 03-12-2009 20:05
    Aasia Tallah 03-11-2009 19:56
    Aasia Tallah 03-10-2009 19:54
    I try to change the format of the column, but that does not work.
    Rows 2,3 and 4 seem to be text but they are not. If i access the edit bar for example the 17th of march and store without changing anything, it changes to the format of the first row. That is how i want it.
    If i copy the column by hand, every thing is fine.
    All of the following i tryed:
    - format date column of the csv before copying... failed
    - copy special with only pasting values... failed
    - preformat the column i paste the date to... failed
    - delete the sheet i paste to and create a new one... failed
    - copy the macro to a new workbook... failed

    Any suggestions?
    I could run a macro on all of the over 2500 cells with dates, copy and paste in the same cell... that would work i guess, but i'd rather like to know why this happens.

    Thanks for any input on this subject, Google search did not help :-)
    Last edited by Gero; 05-12-2009 at 01:30 PM. Reason: no response, had to use a long workaround :-(

  2. #2
    Registered User
    Join Date
    04-27-2009
    Location
    Bahrain
    MS-Off Ver
    Excel 2003
    Posts
    7

    Angry Re: Copy-paste from csv to xls macro > wrong date format

    my hope for the workaround failed as well:

    Sub FormatEditEachCell()
    Sheets("DBC Active Members test").Select
        lastRow = Range("A1").End(xlDown).Row
        Set cell1 = Range("C1")
        For n = 1 To lastRow
        x1 = cell1.Value
        x2 = Format(x1, "dd-mm-yy H:mm")
        cell1.Value = x2
        Set cell1 = cell1.Offset(1, 0)
        'Stop
        Next
    End Sub
    If i only doubleclick in a cell with the wrong format and then move to the next cell, the format changes to the way i want... i'm stuck
    Last edited by Gero; 04-28-2009 at 04:04 AM. Reason: Added code tags as per forum rules

  3. #3
    Registered User
    Join Date
    04-27-2009
    Location
    Bahrain
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Copy-paste from csv to xls macro > wrong date format

    could not find a solution... started new approach, changing the csv to txt.
    Opening the file manually with comma as dilimiter, everything is fine, record the macro, the dates are messed up again. 10th of March is now 3rd of October and all values with a day higher as 12 are not recognized as dates at all.

    Importing the dates as text seems to work fine at the beginning, but the sorting will only work manually, if recorded 30/03/09 is higher then 14/04/09.

    I changed the system date settings, but that also did not work.
    Isn't there a way to swich off the "artificial intellegence" of Excel?

    Please help, i spent days now searching for a solution.
    Thanks
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1