Solved: COPYING DATES FROM WORD TO EXCEL

[GOGGS75]

Hi,

I have set up a macro that copies all of a WORD document containing a table of data to EXCEL and have been having a problem copying dates that I was hoping someone could help with.

The routine that works correctly when carrying out the process manually is to copy all of the WORD document to the clipboard and then paste this as text using Paste Special in EXCEL.

The problem when programming VBA to do this is that the dates are not copied correctly. Examples of the problems being as follows:

01/10/08 (i.e. 1 October 2008) converted to 10/01/08
16/10/08 (i.e. 16 October 2008) coverted to string "16/10/08" not a value

I realise this is something to do with the dates copied being treated as US dates (i.e. mm/dd/yy) instead of UK dates (dd/mm/yy) but can't understand why the copy routine works manually and not in VBA ?

Is there a line that can be added to VBA to stop this problem ?

Thanks for any advice.

Goggs75

[lucas]

Goggs, I've moved this to the Excel help forum where I think you will stand a better chance of someone taking a look at it with you.

Can you format the column or cells where the date is transferred into Excel before you paste the data or is it a new Excel workbook each time?

[rbrhodes]

Hi,

Always best to post a small example or at least the code.

[GOGGS75]

Hi Lucas/Rbrhodes

I have tried formatting the cells prior to copying from the word document and this does not solve the problem.

The line of code not working is as follows:

Activesheet.PasteSpecial Format:="Text"

I have found the following code does work as a one line macro but not when building it into the intended macro:

Application.SendKeys ("%ES{END}{UP}~")

(I.E. EDIT PASTESPECIAL TEXT ENTER)

An example of the problem being as follows:

A date of 3/10/07 changes to 10/03/07 whereas a date of 15/10/07 remains as 15/10/07 but is left aligned rather than right aligned ?

Thanks for any advice.

Cheers

Goggs75

[rbrhodes]

Maybe DATESERIAL? Example please...

[GOGGS75]

Hi Rbrhodes,

As there is a full word document being copied there is not just one serial code. The example using two lines of the word document posted already has the following serial codes:

WORD "3/10/07" changes to EXCEL "10/03/07", serial code 39151

WORD "15/10/07 changes to EXCEL "15/10/07", no serial code until pressing F2 then enter. Then has a serial code of 39370.

As it works manually using the commands Edit, PasteSpecial, Text there must be something missing from the following VBA commands that can tell VBA the date it is copying is in UK format not US format.

Activesheet.PasteSpecial Format:="Text" (per macro recorder, does not work)
Activecell.PasteSpecial Paste:=xlValues (not working either)

Thanks for any advice.

Cheers

Goggs75

[Kenneth Hobs]

I guess you can fix it after the paste.

e.g.
Sub ChangeDateFormats()
Dim cell As Range
For Each cell In ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
Debug.Print cell.Address, cell.NumberFormat
If cell.NumberFormat = "m/d/yyyy" Then cell.NumberFormat = "dd/mm/yyyy"
Debug.Print cell.Address, cell.NumberFormat
Next cell
End Sub

[GOGGS75]

Hi,

I managed to fix the dates after the paste using code similar to Kenneth's, this has solved the problem.

I am still feeling there should have been an easier and smoother way to solve the problem as there was no problem carrying out the procedure manually and VBA should have been able to do this also.

Thanks for your help.

Cheers

Goggs75

[lucas]

Be sure to mark your thread solved using the thread tools at the top of the page please.

[Kenneth Hobs]

There might be some other ways to do it. I would have to know how you did it manually. Example doc and xls would help. At worst, one can use SendKeys.