Excel VBA Date Paste Woes

Hello all,

I have a program that allows me to "copy data to clip board".  When I paste the data into excel it works a treat and has the correct date values.  However, when I use the simple macro below:

        Range("A1").Select
        ActiveSheet.Paste

It transposes the day and the month around.  Sadly, it is not a formatting headache as I thought I could just go in and amend it in formats but it actuall has moved it around and thinks the month is the day and vice versa.  Any ideas on why and any cunning ideas on a workaround?

Many thanks in advance, Alan.
Canders_12Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

VBA always treats dates as the US standard format, i.e. MM/DD/YYYY, at least if you don't specify things otherwise.

Can you provide more detail about the actual data that sits in the Clipboard, and about your regional settings?

cheers, teylyn
0
Canders_12Author Commented:
teylyn:

Please find attached a copy of excel to see what is happening.  What regional settings are you after and where would i find them?

Really appreciate your help.

Many thanks, Alan.
formats.xls
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
The Excel file does not really help much, since on my system, the date would display with my regional settings.

The regional settings are determined by the operating system. In Windows XP, for example, go Start > Control Panel > Regional and  Language settings. Here you can specify how numbers and dates/times are displayed in any application on your computer.

If these differ from the US standard, then you will need to take steps in Excel VBA.

Since I cannot see from the attached file what your regional settings are, you'll need to provide them.

What is the order or day month, and year you want to show?

cheers, teylyn
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
It's not quite clear from your example if the desired date is 4-Aug-2012 or 8-Apr-2012.

Can you elaborate? Also, what application are you copying from? What is your complete VBA code for the copy and paste operation?
0
Canders_12Author Commented:
The program is called "Protean"

That is the complete code :/  That is why it is soooooooooooooo frustrating that when I past it works but then use code that should do the same it messes with the dates

Please see regional settings as attached.  

Soooo hope you can help :(
regional-settings.bmp
0
Canders_12Author Commented:
PS

Date should be 08/04/2012.  As in 8th of April 2012.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Errrhmm. If that's the complete code, how do you trigger it?

Why not just use Ctrl-V to paste with the correct date format?
0
Canders_12Author Commented:
Interestingly if I have the date of 13/03/2012 it will work.  When I try 12/03/2012 it resorts to switching the dates :/
0
Canders_12Author Commented:
Sorry, there is code that happens after this but seems little point until this first part is resolved.
0
AnuroopsunddCommented:
use below code and try

Range("A1").Select
        ActiveSheet.PasteSpecial
0
Canders_12Author Commented:
Anuroopsundd:

Sadly no joy :(
0
ScriptAddictCommented:
Teylyn is likely right.  You are likely in Canada or somewhere else that uses a different date format.  

Failing that:
It could be something with how the progam you are using stores the data.  

I would just assume that it can't/shouldn't be changed and edit my code so that it handles it properly.  Something that edits the date to the correct format after the paste hits:

Perhaps something like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Source1 As Range
Dim Target1 As Range
Dim ConvertText As String

Set Source1 = ActiveSheet.Range("A1")
Set Target1 = ActiveSheet.Range("B1")

Application.EnableEvents = False

ConvertText = Excel.WorksheetFunction.Text(Source1, "dd/mm/yyyy")
Target1.Value = CDate(ConvertText)

Application.EnableEvents = True

End Sub

Open in new window


If that doesn't switch it correctly move around the dd and the mm and you should be good.

Alternatively, and likely better, you could take the paste and assign it to a variable in the existing code and do the converstions before pasting it to the worksheet.

Perhaps something based on this code from www.cpearson.com:

 
Dim DataObj As New MSForms.DataObject

    Dim S1 As String

    DataObj.GetFromClipboard
        S1 = DataObj.GetData("FormatId1")

    Debug.Print S1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Canders_12Author Commented:
Sorry for slow reply
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.