Excel vba 6.0 copy date cells date format

[sirving]

:sad: I have written a number of macros which work successfully, but 1 is giving me some trouble. At the start of the macro, I open another excel worksheet and copy data from sheet using [Range("A1", "G30").Copy], then paste using [Range("A2").Select], [ActiveCell.PasteSpecial] on current workbook.

This pastes the data correct except for 1 cell, date and time cell.
This is in format dd/mm/yyyy hh:mm. But when pasted to sheet, the date is converted to American date. mm/dd/yyy hh:mm.

It doesn't mater what the cell format is when pasteSpecial is performed (within macro).

If the same action are done manually, the date is not changed !!!!

Any help would be much appreciated.

[Comatose]

Are you sure there is no macro, formula, or function that is forcing the cell to behave this way in the template? Another solution, is to force the change by using "format" on the cell AFTER the paste operation...

[sirving]

Are you sure there is no macro, formula, or function that is forcing the cell to behave this way in the template? Another solution, is to force the change by using "format" on the cell AFTER the paste operation...

This is extract of code used:

[If FileExists("C:\EM AXH in Period issue.csv") Then
Workbooks.Open Filename:="C:\EM AXH in Period issue.csv"
Cells.Select
Selection.Copy

Windows("template automatic EM last 24hrs iss14.xls").Activate
Sheets("access hub").Select
Range("A1").Select
ActiveCell.PasteSpecial]

Text being copied from EM AXH in PEriod issue.csv and pasted to template automatic EM last 24hrs iss14.xls, sheet access hub.

[sirving]

This is extract of code used: [If FileExists("C:\EM AXH in Period issue.csv") Then Workbooks.Open Filename:="C:\EM AXH in Period issue.csv" Cells.Select Selection.Copy Windows("template automatic EM last 24hrs iss14.xls").Activate Sheets("access hub").Select Range("A1").Select ActiveCell.PasteSpecial] Text being copied from EM AXH in PEriod issue.csv and pasted to template automatic EM last 24hrs iss14.xls, sheet access hub.

After furtherr investigation, I have found that if i open workbook C:\EM AXH in Period issue.csv manually, the the date format is correct (cell format is set to custom dd/mm/yyyy hh:mm), but if i open the same workbook using [Workbooks.Open Filename:="C:\EM AXH in Period issue.csv"] then the date in the cells is shown as mm/dd/yyyy hh:mm although the format of cell is still set to dd/mm/yyyy. Why is the cells being converted to american date format when the file is opened using a vba macro ???

[mrdarcy]

After furtherr investigation, I have found that if i open workbook C:\EM AXH in Period issue.csv manually, the the date format is correct (cell format is set to custom dd/mm/yyyy hh:mm), but if i open the same workbook using [Workbooks.Open Filename:="C:\EM AXH in Period issue.csv"] then the date in the cells is shown as mm/dd/yyyy hh:mm although the format of cell is still set to dd/mm/yyyy. Why is the cells being converted to american date format when the file is opened using a vba macro ???

I am having the exact same problem! Did you figure it out, or does anybody know how to get around this problem?

[SirWren]

I am having the exact same problem! Did you figure it out, or does anybody know how to get around this problem?

Ok... here's the solution, add the parameter to your VBA macro, e.g.

Workbooks.OpenText Filename:="C:\Test1.csv", DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierNone, FieldInfo:=Array(1, 4), Local:=True

[MHamlyn]

Thankyou so much for this it has helped solve the same problem I have had for a few months.

Now it works perfectly!

[sirving]

Thanks for the info.

The parameter option doesn't appear as an option
for me using Microsoft excel200 vba version 6.

I am still having the same problem

[DenisOxon]

What dates are being changed ?

Is is dates at beginning of month e.g 7th May or does this happen with dates later in month e.g 17th May also ?

I have had a similar problem in VB5 where some dates change and others don't for which I don't yet have a solution.

[sirving]

First two dates are correct (25/4/07) 2nd 2 dates are incorrect (5/2/07, 5/5/07 & 5/11/07)

Think the 5th is just a coincidence on this example.

[sirving]

The dates do appear to be less that 12 when they are copied incorrectly. the 3 dates above should are in the wrong format. i.e. mm/dd/yy
but are dd/mm/yy in the original file

[DenisOxon]

I think there is two possible solutions from the testing I have done in VB5

1) change date format to American style before copying, copy across then reformat to English style.

2) format date cells to text before copying. Then copy. Hopefully (because I have not tested this) the cells will stay as text and therefore displayed correctly.

Please let us know how you get on.

[VOLTAN]

I am having the exact same problem! Did you figure it out, or does anybody know how to get around this problem?

No. But I could do with a fix. I hve a similar problem. when using code to open a CSV file in Excell a field containing dates is fouled up. some are imported as dd/mm/yy and others as mm/dd/yy. the original format is dd/mm/yy. it apears that dates with leading zeros "01/11/07" for example are trated differently. it is impractiac to change the format of the CSV files I need to find a fix in how excell imports the data. any suggestions?

[sirving]

If the cells containing the date are changed to text first, then copied and changed to dd/mm/yy hh:mm it is then in the correct format.

I wrote vba code which (after dates had been copied) checked if the day was <12 then change to mm/dd/yy hh:mm else leave.

This seemed to work

[chiewtin]

Ok... here's the solution, add the parameter to your VBA macro, e.g. Workbooks.OpenText Filename:="C:\Test1.csv", DataType:=xlDelimited, _ TextQualifier:=xlTextQualifierNone, FieldInfo:=Array(1, 4), Local:=True

==================
I am also facing the same problem with date format using macro and I tried to apply your solution... but the command that I am using is Application.GetOpenFilename
So anyone can suggestion how to insert or setup Local := True into this command?

[Jishnu]

Another 'fossil' thread bumped right through :D

[geraldscm]

I tried a whole range of different approaches to resolve this problem. Excel still insisted on writing the date field from my VBA code as mm/dd/yyyy instead of the source string format of dd/mm/yyyy when dd was less than 12. In the end I overcame it by writing out = "*" & datefieldstring and dealing with that on any subsequent inputs. Not very elegant, but at least it works consistently.