Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

**maym** · 06-19-2017, 08:30 PM

Hi

I am having lots of trouble with my macro that opens a workbook in one folder and copies the contents to a workbook in another folder.

When this runs, the dates in Col A change the format from dd/mm/yyyy to mm/dd/yyyy. I am really not sure why this happens and I have tried everything, including using local:=true when opening the workbook and also forcing the format by adding: selection.numberformat= "dd/mm/yyyy" but nothing seems to work??

Can anyone help me with this problem? Attached are the two worksheets I am using and also the macro is here:

Please Login or Register  to view this content.

**maym** · 06-20-2017, 10:32 PM

can anyone please assist me with this problem

**scottiex** · 06-20-2017, 10:55 PM

cant test your code, but maybe if you make that text real dates with text to columns?

Please Login or Register  to view this content.

**maym** · 06-20-2017, 11:01 PM

thanks scottiex. How would I insert that into my code, assume it goes here:

Please Login or Register  to view this content.

**maym** · 06-20-2017, 11:16 PM

I tried the following but it isn't working??

Please Login or Register  to view this content.

**maym** · 06-20-2017, 11:16 PM

the dates still get copied across in the opposite format, i.e. they show in the initial worksheet as dd/mm/yyyy but when copied become mm/dd/yyyy?

**maym** · 06-20-2017, 11:17 PM

the dates still get copied across in the opposite format, i.e. in the initial spreadsheet they show as dd/mm/yyyy but when copied show as mm/dd/yyyy?

**scottiex** · 06-20-2017, 11:21 PM

I meant convert it to an actual date before you copy it. You could even convert it to a number as well then convert it back if it is still an issue.

**maym** · 06-20-2017, 11:25 PM

thanks scottiex.

I put the code here but still the same

Please Login or Register  to view this content.

**humdingaling** · 06-21-2017, 01:04 AM

Please Login or Register  to view this content.

does the issue still occur with just this bit of code? as in the test example it does not have this issue

**maym** · 06-21-2017, 01:07 AM

yes it does humdingaling.

Are you saying you ran the initial code above and you didn't have any issues?

**maym** · 06-21-2017, 01:19 AM

could that be because you are adding a workbook in your test code rather than opening a workbook in another folder?

**jindon** · 06-21-2017, 01:23 AM

Because your "dates" are not date, just string.

**maym** · 06-21-2017, 01:24 AM

thanks jindon,

How can you tell they are just strings? Are they all strings? How could I go about resolving my problem if they are strings?

**humdingaling** · 06-21-2017, 01:32 AM

correct
i have no issues running this with both your sample file

however i have found the issue
let me do a few tests and ill come back with workaround

**jindon** · 06-21-2017, 01:32 AM

If it is date it appears like yyyy/m/d in formula bar, but it is now 11/05/2016.

1) select column - [Date] - [TextToColumns] -[Next] - [Next]
2) select [Date] select "MDY" from the dropdown [Finish]

**humdingaling** · 06-21-2017, 01:33 AM

ok the issue is the closing of the file before pasting

Please Login or Register  to view this content.

tested using this code

if you close the workbook after the pasting it doesnt seem to have the issue anymore

**maym** · 06-21-2017, 01:54 AM

thanks a lot humdingaling.

I adjusted my initial code to the below but I get a run time error because the workbook is already open (the workbooks have the same name but in different folders so do I need to adjust further?

Please Login or Register  to view this content.

**humdingaling** · 06-21-2017, 02:01 AM

you need to declare and set Wb in order to use it

Please Login or Register  to view this content.

**maym** · 06-21-2017, 02:10 AM

oh ok, I didn't know that. How can I amend the code for that?

**humdingaling** · 06-21-2017, 02:17 AM

already did on the code provided ...i changed to be in red so it stands out

does it still not work?

**maym** · 06-21-2017, 02:20 AM

sorry didn't see it. Will try it now.

**maym** · 06-21-2017, 02:30 AM

Please Login or Register  to view this content.

Needed to make the changes in red but now I get an automation error so perhaps I haven't done it right??

**jindon** · 06-21-2017, 02:34 AM

You guys have no idea about the meaning of the code why it must close before it opens other workbook.

https://www.excelforum.com/excel-pro...n-another.html

**humdingaling** · 06-21-2017, 02:35 AM

sorry i forgot to add the with WB in my previous code

Please Login or Register  to view this content.

**humdingaling** · 06-21-2017, 02:52 AM

Jindon:
my response is to the particular problem of the date switching from Australian standard (DD/MM/YYYY) to US standard (MM/DD/YYYY)

as i have the same date settings at OP, i was able to replicate the OP issue with the code in post #17
if the workbook it copies from is open closed before it opens pastes into new workbook

the issue occurs
closing the original workbook of which the data is copied from after pasting seems to circumvent this from happening (don't know the exact reasoning this happens...chalk it up to another MS quirk with dealing with dates in VBA environment)

**jindon** · 06-21-2017, 02:55 AM

It is not my original code, but the data itself, so I have posted the solution in #16.

It will be impossible to solve the issue unless convert string date to serial date.

**maym** · 06-21-2017, 03:17 AM

Please Login or Register  to view this content.

Still get a run time error on the line highlighted above saying file is already open??

**maym** · 06-21-2017, 04:01 AM

Originally Posted by jindon

It is not my original code, but the data itself, so I have posted the solution in #16.

It will be impossible to solve the issue unless convert string date to serial date.

Thanks jindon. I am not sure how I would convert the string date to serial date and incorporate in the code you gave me.

Could you please assist me with this?

Thank you!

**humdingaling** · 06-21-2017, 04:20 AM

You guys have no idea about the meaning of the code why it must close before it opens other workbook.

i see the problem with my solution now...cannot have the two files with the same name open at once

ill give it another whirl tomorrow unless someone else thinks of something that works for you prior
the method to circumvent the problem still works....just need to get around this issue of having two files with the same name

**jindon** · 06-21-2017, 05:03 AM

Please Login or Register  to view this content.

**humdingaling** · 06-21-2017, 08:56 PM

Jindon: tested your code a few times...i still get the same issue that OP faces
the dates switch from AU standard to US standard

Please Login or Register  to view this content.

added a "dump" workbook in the middle so main wb can close in order for the sub wb to be opened

**maym** · 06-21-2017, 09:15 PM

Originally Posted by humdingaling

Jindon: tested your code a few times...i still get the same issue that OP faces
the dates switch from AU standard to US standard

Please Login or Register  to view this content.

added a "dump" workbook in the middle so main wb can close in order for the sub wb to be opened

jindon, the same thing happened to me. It still switches from AU format to US format.

humdingaling, I tried your code and although it worked, when I went to sort the data, it asked me if it wanted to sort numbers that look like numbers, so I wonder if some of the dates are still text and how to fix this?

**jindon** · 06-21-2017, 09:22 PM

Which my code are you talking about?

**maym** · 06-21-2017, 09:22 PM

I used a combination of both of these and it managed to work using this:

Please Login or Register  to view this content.

I am going to keep testing to see if it continues.

**humdingaling** · 06-21-2017, 09:56 PM

i got that text message when sorting as well

it looks like you have managed to amalgamate a solution up
its what i would have done

Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Re: Strange Problem - Copy paste dates changes format from dd/mm/yyyy to mm/dd/yyyy

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Imported data contains strings dd.mm.yyyy how can I convert to date format dd/mm/yyyy inVB

[SOLVED] opening excel file -> date format problem: DD/MM/YYYY vs MM/DD/YYYY

Bookmarks

Bookmarks

Posting Permissions