Solved

Excel VBA Date Paste Woes

Posted on 2012-04-09
13
219 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
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 how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

756 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