word to excel format change

  • Thread starter Thread starter rumkus
  • Start date Start date
R

rumkus

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







- Show quoted text -
Click to expand...

Worked charmingly !

Thank you very much Peter.
 
Back
Top