VBA: Convert UK to US date format (dd/mm/yyyy to mm/dd/yyyy) when pasting data

Programming
, ,
1

I am running a macro to paste data from a 3rd party application into a spreadsheet, but the date format is being changed to American (mm/dd/yyyy). The regional settings on my PC are for the UK format (dd/mm/yyyy) and the cells are also formatted as dd/mm/yyyy.

Range("A1").PasteSpecial xlPasteAll

I have tried using Range("A1").NumberFormat = "mm/dd/yyyy" before and after the paste line of code, but it does not work.

I have also tried a solution which cycles through all the cells in a range and formats them, but this is too slow (over 10 seconds):

Dim DateRange As Range

Range("A1").PasteSpecial xlPasteAll Set DateRange = Range("A2:A3000") For Each Cell in DateRange Cell.Value = Format(Cell.Value, "dd/mm/yyyy") Next Cell

Manually using Text to Columns works quickly, but I haven’t been able to integrate it into the VBA code.

Is there any other way to prevent VBA from converting the dates to American format?

2

You can try using the TextToColumns method in VBA to prevent the conversion of dates to the American format. Here’s an example code snippet that you can use:

Range("A1").PasteSpecial xlPasteAll

Dim rng As Range
Set rng = Range("A1:A3000")

rng.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, xlDMYFormat)

This code will paste the data into the range A1:A3000 and then use the TextToColumns method to convert the data to the correct date format based on the regional settings of your PC.

Make sure to adjust the range (A1:A3000) to match the actual range where you want to convert the dates.

Hope this helps!