Excel 2010 VBA sheet copy reformats dates (non US format: i.e. dd/mm/yyyy)

Question

I am trying to copy a either an entire worksheet or a range of data from one workbook to another. I can do this successfully manually, by selecting all the cells with data in them and pasting into a newly created worksheet (Date format is maintained). However when I try to do this via VBA any date with a day greater than 12 gets converted to text [US (m/dd/yyyy)/Australian (dd/mm/yyyy) format change]. I've tried formating the cells first to US format dates but this doesn't help. Any sugestions?

FYI - source data is from a CSV file that is opened by VBA - date values are text (eg "2/08/2013" or "31/07/2013") that are automatically converted to dates on open (IsNumber = True and IsText = False).

CSV on open looks like this (sample of 3000 odd lines): Note that "Date" and "Last update" fields are in dd/mm/yyyy hh:mm format

Id          Title     Division  Status  Date                Last update
REQ:7619    Job Details ICTIS   InProg  31/07/2013 13:03    6/08/2013 15:51
REQ:7617    Job Details ICTIS   InProg  31/07/2013 12:06    2/08/2013 11:34
REQ:6994    Job Details ICTIS   Open    31/07/2013 12:05    31/07/2013 12:05
REQ:7613    Job Details MNHD    User    31/07/2013 12:01    1/08/2013 15:59
INC:79210   Job Details ICTIS   Open    31/07/2013 12:00    31/07/2013 12:00

Simple excerpt of Code:

'Select all Data Cells
Sheets(1).Cells(1, 1).Activate
ActiveCell.SpecialCells(xlLastCell).Select
LastRow = ActiveCell.Row

'Format Date Fields to avoid automatic translation issue
'This does not help problem... so commented out
'Columns("M:M").Select
'Selection.NumberFormat = "m/d/yyyy"

'Select all data in sheet and copy
range("A2:" & LastCol & LastRow).Select
selection.Copy

'Rest of code moves to previous sheet and copies into cell A8

Same file after running the above code. Note how dates with day>12 are treated differently to those with day<=12. 31/7/2013 has "PM" added, while 6/8/2013 and 2/8/2013 have day and month swapped!

Id          Title     Division  Status  Date                Last update
REQ:7619    Job Details ICTIS   In Progress 31/07/2013 1:03:49 PM   8/06/2013 15:51
REQ:7617    Job Details ICTIS   In Progress 31/07/2013 12:06:31 PM  8/02/2013 11:34
REQ:6994    Job Details ICTIS   Open    31/07/2013 12:05:50 PM  31/07/2013 12:05:50 PM
REQ:7613    Job Details MNHD    With User   31/07/2013 12:01:46 PM  8/01/2013 15:59
INC:79210   Job Details ICTIS   Open    31/07/2013 12:00:52 PM  31/07/2013 12:00:52 PM

My quick fix has been to split the code into 2 parts - with a manual copy/paste prompted by a MsgBox. Not very elegant!! and prone to error.

Answer 1

I have had the exact same problem in the past. I can confirm that the problem occurs when the CSV is opened using a macro - when running vba excel defaults to opening using US date formats, whereas when opening a CSV manually it uses your local date formats. The easy fix not requiring a workaround of any sort is to instruct VBA to open the CSV using your local date formats as follows.

The relevant line of my broken code:

Set wbkData = Workbooks.Open(strDataDir & strDataFName)

And the fixed line:

Set wbkData = Workbooks.Open(strDataDir & strDataFName, local:=True)

Answer 2

Excel uses your default system format to determine how to interpret a date when you type it in a cell. In USA if you type in "3/2/2013" it will interpret it as Mar 2. In Australia it will interpret the same keystrokes as Feb 3. Changing the cell formatting doesn't make any difference as you discovered.

The conversion to text will occur when you copy a date that is not compatible with your system format (eg. has 20 as the month). I really think the problem is actually happening at the time that you open the CSV file.

If the problem were in the copy operation you could convert the date to text before working with it. =Text(a1, "m/d/yyyy")

Edit: It must be that one workbook is in US format and that the other is in Aussie format. I have some ideas about what is happening but I cannot say for sure. Make sure both workbooks are opened in the same instance of Excel. If both workbooks are opened by your code then it should not be a problem. Check your task manager process list to make sure that Excel.exe only appears once.

Also I'm not sure if your code is accidentally causing a recalculation and that could somehow cause a problem. This code should fix that:

'get source worksheet
Dim ws As Worksheet
Set ws = Sheets(1)
ws.Activate

'copy all but first row
Call ws.UsedRange.Offset(1, 0).Resize(rowsize:=ws.UsedRange.Rows.Count - 1).Copy

'activate second sheet
Dim wsDest As Worksheet
Set wsDest = Sheets(2)
wsDest.Activate
wsDest.Paste

'de-activate copy mode
Application.CutCopyMode = False

Also you can use the code editor window to step through the code (press F8) to see exactly which line is causing the data to change.

Answer 3

In this answer I describe two methods of achieving the error you describe. If one of those methods matches what you are doing, you may be able to amend your code to avoid the error. If that is not enough then I need to see more of your code before I can suggest a remedy. You could add the complete code to your answer or you could use the address in my profile to email it to me.

It has taken me a lot of experimentation to duplicating your error but I can now achieve the corruption of a date to middle endian format in two ways. (Middle endian seems to be the preferred academic description for m/d/y date format with d/m/y and y/m/d formats referred to as small and big endian.)

At heart the problem is that Excel always has several ways of achieving the same effect. The effect here is the conversion of a string to a date.

Approach 1 is to use the country code to determine the local date format. If you live in a country where the local date format is d/m/y then with this approach “06/08/2013” will be converted to “6 August, 2013”.

Approach 2 is to use Excel default date format which in Britain, Australia and many other countries is “*dd/mm/yyyy” but if you ask VBA for the NumberFormat corresponding to “*dd/mm/yyyy” you are given “m/d/yyyy”. Approach 2 will convert “06/08/2013” to “June 8, 2013”. This is explained in more detail in my question: .NumberFormat sometimes returns the wrong value with dates and times.

If you import a CSV file and specify the format of a date/time column as “General”, it will use Approach 1. To corrupt that date, you need to move it with code like this:

Dim CopyDate As String
CopyDate = Cell1.Value
Cell2.Value = CopyDate          ' This uses Approach 2

If you import a CSV file and specify the format of a date/time column as “Text”, it will leave the value as text. If you move that text value using a method that causes Excel to evaluate the text, it will use Approach 2 to convert it to a date.

Hope this helps.