• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

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.
0
Canders_12
Asked:
Canders_12
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
Canders_12Author Commented:
Sorry for slow reply
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now