PDA

View Full Version : Paste Format



peterwmartin
01-03-2007, 03:42 AM
Hi all I need some help with formating. I have a word document which I select then paste into excel. The original word doc has been justified centre. The code I have copies and pastes. But is there a way to justify it centre of sheet I still need to be able to change values so I can't copy as picture.
Private Sub cmdOK_Click()
Dim fd As FileDialog
Dim myPath As String

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
'in the background, create an instance of XL
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

Selection.WholeStory
Selection.Copy
xlSheet.Range("e1").Select
xlSheet.Paste
xlApp.Visible = True
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

End Sub


Any ideas would be appreciated.
Thanks all

Charlize
01-03-2007, 06:10 AM
Hi all I need some help with formating. I have a word document which I select then paste into excel. The original word doc has been justified centre. The code I have copies and pastes. But is there a way to justify it centre of sheet I still need to be able to change values so I can't copy as picture.
Private Sub cmdOK_Click()
Dim fd As FileDialog
Dim myPath As String

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
'in the background, create an instance of XL
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

Selection.WholeStory
Selection.Copy
xlSheet.Range("e1").Select
'xlSheet.Paste
xlSheet.PasteSpecial format:="Microsoft Word 8.0 Document Object"
xlApp.Visible = True
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

End Sub


Any ideas would be appreciated.
Thanks all

peterwmartin
01-03-2007, 07:44 AM
Thanks Charlize,
This pastes as a picture any tables I take over I need to edit.
The document has tables and text.

JonPeltier
01-03-2007, 08:33 AM
You're pasting the document starting at cell E1. This gives you one paragraph per cell in column E, except that tables take up as many rows and columns as they used in Word, starting with the next cell in column E. You could apply each paragraph's alignment in Word to each cell's alignment in Excel, with partial success. You will not get line wrapping and other paragraph formatting the way a word processor would provide, because Excel isn't a word processor. There's no neat way to accomplish this, although I can think of slow, messy, buggy ways that involve merging cells and stepping through the Word paragraphs one by one.

peterwmartin
01-03-2007, 08:13 PM
Thanks Jon.
I will work through it . I did see some code for if text if table etc however I can't find it again.