Date formats being switched between UK / US formats when possible...

[JazzSP8]

Hey All

Weird one here, I've had this before but I've always been able to bodge my way around it somehow, but this time I can't and it's finally time to try and solve the problem once and for all.

I'm from the UK - When I'm using VBA to copy and paste a worksheet from a workbook on our company network drive to a workbook on my local desktop that has dates in it, the dates that can be changed to US format are being.

So, for example;

[TABLE="width: 81"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]26/06/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]26/06/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]11/06/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]11/06/2018[/TD]
[/TR]
[TR]
[TD="align: right"]25/06/2018[/TD]
[/TR]
[TR]
[TD="align: right"]25/06/2018

[/TD]
[/TR]
</tbody>[/TABLE]
Becomes...

[TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD]26/06/2018
[/TD]
[/TR]
[TR]
[TD]26/06/2018[/TD]
[/TR]
[TR]
[TD="align: right"]06/11/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]06/11/2018[/TD]
[/TR]
[TR]
[TD]25/06/2018[/TD]
[/TR]
[TR]
[TD]25/06/2018[/TD]
[/TR]
</tbody>[/TABLE]

What was the 11th June 2018 becomes 6th November 2018, the other dates that can't be swapped around aren't.

It's not a formatting issue I don't think as the value is being changed from 43262 (11/06) to 43410 (06/11)

I've checked the "Locale" information in both Excel and Control Panel, both are set correctly to English (United Kingdom)

If I just open the Workbook on the network drive and copy and paste the sheet into a new workbook manually, the dates remain correct.

It's not just these dates that are being swapped around, any time the day / month can be swapped and made into a US format date it is being doing 11/12 becomes 12/11 and 06/07 becomes 07/06 etc.

There is nothing fancy about the code I'm using to copy and paste the worksheet;

    Workbooks.Open Filename:="Z:\Pricing Sheets\Pricing Analyser - " & FName & ".xlsm", ReadOnly:=True, UpdateLinks:=xlUpdateLinksAlways
    ActiveSheet.AutoFilterMode = False
    Cells.EntireColumn.Hidden = False
    Cells.Copy
    ActiveWorkbook.Close savechanges:=False
    ActiveSheet.Paste

(Fname is just the variable part of the filename defined by a Cell earlier in the code)

This makes no sense to me

Anyone come across anything like this before?

 

[JackDanIce]

Ah I was confused between mentioning pastespecial but using the single line to copy and paste (as well as bypass clipboard), thanks!

Another suggestion to try may be

Workbooks.Open FileName:="Z:\Pricing Sheets\Pricing Analyser - " & Fname & ".xlsm", ReadOnly:=True, UpdateLinks:=xlUpdateLinksAlways, Local:=True

Taken from:https://stackoverflow.com/questions...-reformats-dates-non-us-format-i-e-dd-mm-yyyy

 

[JazzSP8]

Nope - Changing the code doesn't work either, just does the same thing

In the interests of elimination I've just tried this on another PC and it does the same thing so I know it's not just "my PC" at least.

Thanks to all who've tried so far by the way - Just realised I'd forgot my manners

 

[JazzSP8]

Ah I was confused between mentioning pastespecial but using the single line to copy and paste (as well as bypass clipboard), thanks!

Another suggestion to try may be

Workbooks.Open FileName:="Z:\Pricing Sheets\Pricing Analyser - "  & Fname & ".xlsm", ReadOnly:=True,  UpdateLinks:=xlUpdateLinksAlways, Local:=True

Taken from:https://stackoverflow.com/questions...-reformats-dates-non-us-format-i-e-dd-mm-yyyy

Good shout, and something I didn't know - But, no - Didn't work LOL

I hadn't seen Rorry's code when I posted & yes they are the same.
The code Rory & I posted is using Copy Destination, which bypasses the clipboard. The Paste special would be like

   Cells.Copy
   ws.Range("A1").PasteSpecial [COLOR=#0000ff]xlPasteValues[/COLOR]

With the part in blue it will paste values only, without it will paste formulae & formats

Just tried that as well - That pasted the Serial Numbers as you'd expect, but they where still wrong :-/

 

[RoryA]

I assume you haven't previously done a Text to Columns operation, either manually or in code, in that session of Excel?

Also, does it happen if you open Excel in safe mode and then run the code?

 

[Legacy 421546]

Seen this?
https://kickthatcomputer.wordpress.com/2014/08/05/converting-us-dates-to-uk-format-in-excel/

https://www.linkedin.com/pulse/easy-excel-hack-convert-us-dates-uk-format-jim-haysom

 

[JazzSP8]

I assume you haven't previously done a Text to Columns operation, either manually or in code, in that session of Excel?

No - Don't use it in the code, and definitely not with this session - Excel has been opened and closed many times since the last time I will have done that

The only thing that happens before I open the workbooks is I manually create some CSV files

Also, does it happen if you open Excel in safe mode and then run the code?

Good call - Hadn't thought of that either, but - Yes - It still does it

 

[JazzSP8]

Seen this?
https://kickthatcomputer.wordpress.com/2014/08/05/converting-us-dates-to-uk-format-in-excel/

https://www.linkedin.com/pulse/easy-excel-hack-convert-us-dates-uk-format-jim-haysom

I haven't seen that, no - And on a quick test it converted the dates back to how I need them and left the already UK dates alone...

That sounds like a way round this if there is no solution

 

[RoryA]

Does this produce the correct values?

Dim data
 Workbooks.Open Filename:="Z:\Pricing Sheets\Pricing Analyser - " & FName & ".xlsm", ReadOnly:=True, UpdateLinks:=xlUpdateLinksAlways
    data = activesheet.usedrange.value
    ActiveWorkbook.Close savechanges:=False
    activesheet.range("A1").Resize(ubound(data), ubound(data, 2)).value = data

 

[JazzSP8]

Does this produce the correct values?

Dim data
 Workbooks.Open Filename:="Z:\Pricing Sheets\Pricing Analyser - " & FName & ".xlsm", ReadOnly:=True, UpdateLinks:=xlUpdateLinksAlways
    data = activesheet.usedrange.value
    ActiveWorkbook.Close savechanges:=False
    activesheet.range("A1").Resize(ubound(data), ubound(data, 2)).value = data

Ohhhhhhhhhhhhhhhhhhhhhhh!!!!! IT DOES!!!

THANKS!

I'd be lying if I understood though?

 

[RoryA]

It avoids copy/paste completely and assigns the values from the source workbook directly to an array, then assigns that array directly to the values of the relevant range in the destination workbook.