Solved

Excel VBA Date Paste Woes

Posted on 2012-04-09
13
216 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:Canders_12
13 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37822868
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
 

Author Comment

by:Canders_12
ID: 37822886
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37822893
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37822899
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
 

Author Comment

by:Canders_12
ID: 37822910
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
 

Author Comment

by:Canders_12
ID: 37822914
PS

Date should be 08/04/2012.  As in 8th of April 2012.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37822917
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
 

Author Comment

by:Canders_12
ID: 37822920
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
 

Author Comment

by:Canders_12
ID: 37822921
Sorry, there is code that happens after this but seems little point until this first part is resolved.
0
 
LVL 17

Expert Comment

by:Anuroopsundd
ID: 37822978
use below code and try

Range("A1").Select
        ActiveSheet.PasteSpecial
0
 

Author Comment

by:Canders_12
ID: 37822991
Anuroopsundd:

Sadly no joy :(
0
 
LVL 11

Accepted Solution

by:
ScriptAddict earned 500 total points
ID: 37824137
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
 

Author Closing Comment

by:Canders_12
ID: 37907665
Sorry for slow reply
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My experience with Windows 10 over a one year period and suggestions for smooth operation
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now