Solved

Excel VBA Date Paste Woes

Posted on 2012-04-09
13
215 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:teylyn
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:teylyn
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:teylyn
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 50

Expert Comment

by:teylyn
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

760 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

26 Experts available now in Live!

Get 1:1 Help Now