word to excel format change
 

When copied my word table into excel sheet
2-0-0 becomes 2-0-0 which is correct but
2-4-0 becomes 02.04.2000 on excel cell. This is rather irritating.
I have to keep this "-" intact as i am doing parsing afterwards.
Any help or right direction ? Thank you very much in advance.

Part of my code to copy:
.........
wdDoc.Tables(1).Select
wdApp.Selection.Copy: Sh.Paste
wdApp.Quit
.............

word to excel format change
 

This worked for me

Sub test()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim wdTble As Word.Table
Dim ws As Worksheet

Set ws = ActiveSheet
Dim rng As Excel.Range

Set ws = ActiveSheet
Set rng = ws.Range("A1")

Set wdApp = GetObject(, "word.application")
Set wdDoc = wdApp.ActiveDocument

Set wdTble = wdDoc.Tables(1)
With wdTble
Set rng = rng.Resize(.Rows.Count, .Columns.Count)
End With

' must be a way to avoid Select ?
wdTble.Select
wdApp.Selection.Copy

rng.NumberFormat = "@" ' as text

rng.PasteSpecial xlPasteValues

' maybe apply some other formats
With rng.Borders
.LineStyle = xlContinuous
.Weight = xlThin ' xlMedium
End With
rng.HorizontalAlignment = xlRight

End Sub

Regards,
Peter T

wrote in message
...
When copied my word table into excel sheet
2-0-0 becomes 2-0-0 which is correct but
2-4-0 becomes 02.04.2000 on excel cell. This is rather irritating.
I have to keep this "-" intact as i am doing parsing afterwards.
Any help or right direction ? Thank you very much in advance.

Part of my code to copy:
........
wdDoc.Tables(1).Select
wdApp.Selection.Copy: Sh.Paste
wdApp.Quit
............

word to excel format change
 

On May 3, 12:18*pm, "Peter T" <peter_t@discussions wrote:
This worked for me

Sub test()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim wdTble As Word.Table
Dim ws As Worksheet

* * Set ws = ActiveSheet
* * Dim rng As Excel.Range

* * Set ws = ActiveSheet
* * Set rng = ws.Range("A1")

* * Set wdApp = GetObject(, "word.application")
* * Set wdDoc = wdApp.ActiveDocument

* * Set wdTble = wdDoc.Tables(1)
* * With wdTble
* * * * Set rng = rng.Resize(.Rows.Count, .Columns.Count)
* * End With

' must be a way to avoid Select ?
* * wdTble.Select
* * wdApp.Selection.Copy

* * rng.NumberFormat = "@" ' as text

* * rng.PasteSpecial xlPasteValues

* * ' maybe apply some other formats
* * With rng.Borders
* * * * .LineStyle = xlContinuous
* * * * .Weight = xlThin ' xlMedium
* * End With
* * rng.HorizontalAlignment = xlRight

End Sub

Regards,
Peter T

wrote in message

...



When copied my word table into excel sheet
2-0-0 becomes 2-0-0 which is correct but
2-4-0 becomes 02.04.2000 on excel cell. This is rather irritating.
I have to keep this "-" intact as i am doing parsing afterwards.
Any help or right direction ? Thank you very much in advance.

Part of my code to copy:
........
wdDoc.Tables(1).Select
wdApp.Selection.Copy: Sh.Paste
wdApp.Quit
............- Hide quoted text -

- Show quoted text -

Worked charmingly !

Thank you very much Peter.