Working between XL and Word

[Francis Hookham]

I need to paste a selection from an XL sheet into a Word.rtf document - the
Word document can be a new from opening Word or an already open document



My attempts to record the operation has not worked - I need to know how to
make make Word active and then go back to XL



If this be done with an XL macro please show me



Francis Hookham

 

[Martin Krastev]

Your VBA code may run in only one application. You can "manipulate" the other
through automation. Use the following steps:

1.Import the word type library (tools/references)
2. declare the variable for the word app
dim wApp as word.application
3. get reference to a running word app
set wApp = getObject(,"Word.Application")
4. use wApp as a word application object to do what you want to do

Hint: You may record a new macro in word to see how to paste data. Then copy
the vba code to excel and modify it if necessary.

 

[Lonnie M.]

Here are a couple of examples that might help you get started.

Example of pasting to a new Word document:
http://groups.google.com/group/micr...nto+new+word+document&rnum=3#bccd865d50a7b808


Example of opening and pasting to the Word document:
http://groups.google.com/group/micr...into+a+word+document&rnum=15#02c733e1036c0ba0

HTH--Lonnie M.

 

[Francis Hookham]

Many thanks Lonnie and Martin

Ok as far opening the Word doc but I am having no luck with pasting in the
copied table

1 In this particular case I want the table pasted in but, in the future,
I might want to paste as text so can you help with code for both please

2 Is the reference to 'Microsoft Word Object Library' carried by the
macro/worksheet of shall I have to set the reference again in another
computer - I shall want to send the resulting worksheet to a friend for whom
I am writing this

Thanks

Francis Hookham

Sub PasteAsPic()
Dim WrdApp As New Word.Application
Dim wrdDoc As New Word.Document

'copy the required data from the Excel File
ThisWorkbook.Sheets(1).Range("B210").Copy

' Create a new Word Document
Set wrdDoc = WrdApp.Documents.Add(DocumentType:=wdNewBlankDocument)
WrdApp.Visible = True

'Paste the copied data as Picture
WrdApp.Selection.PasteSpecial Placement:=wdInLine,
DataType:=wdPasteBitmap

'save the new Word Document
wrdDoc.SaveAs "c:\Filename.doc", wdWord

'clean up code
wrdDoc.Close
Set wrdDoc = Nothing
Set WrdApp = Nothing

End Sub

 

[Martin Krastev]

1. To paste as plain text use Selection.PasteAndFormat (wdFormatPlainText)
and to paste as table use Selection.PasteAndFormat (wdPasteDefault)
2. Yes, the type library reference is kept with the excel workbook

"Francis Hookham" напиÑа:

 

[Francis Hookham]

Martin - thank you for your quick response but something is wrong in the
following macro which stops at 'Selection.PasteAndFormat (wdPasteDefault)'
It also stops with 'Selection.PasteAndFormat (wdFormatPlainText)'
Please help!

When it is does run properly I should like to change to using an existing
Word document(Calendar.rtf) which already exists in the same folder as the
XL sheet.
Please also help with the code for that

This is the macro which will not run

Sub DataFromXLToWord()
Dim WrdApp As New Word.Application
Dim wrdDoc As New Word.Document

'copy the required data from the Excel File
ThisWorkbook.Sheets(1).Range("B210").Copy

' Create a new Word Document
Set wrdDoc = WrdApp.Documents.Add(DocumentType:=wdNewBlankDocument)
WrdApp.Visible = True

'Paste the copied data as table
Selection.PasteAndFormat (wdPasteDefault)
'Selection.PasteAndFormat (wdFormatPlainText)

'save the new Word Document
wrdDoc.SaveAs "c:\Filename.doc", wdWord

'clean up code
wrdDoc.Close
Set wrdDoc = Nothing
Set WrdApp = Nothing

End Sub

Francis Hookham
____________________________________________

 

[Francis Hookham]

I thought this went off yesterday but I cannot see it exept in my Out box:

Martin - thank you for your quick response but something is wrong in the
following macro which stops at 'Selection.PasteAndFormat (wdPasteDefault)'
It also stops with 'Selection.PasteAndFormat (wdFormatPlainText)'
Please help!

When it is does run properly I should like to change to using an existing
Word document(Calendar.rtf) which already exists in the same folder as the
XL sheet.
Please also help with the code for that

This is the macro which will not run

Sub DataFromXLToWord()
Dim WrdApp As New Word.Application
Dim wrdDoc As New Word.Document

'copy the required data from the Excel File
ThisWorkbook.Sheets(1).Range("B210").Copy

' Create a new Word Document
Set wrdDoc = WrdApp.Documents.Add(DocumentType:=wdNewBlankDocument)
WrdApp.Visible = True

'Paste the copied data as table
Selection.PasteAndFormat (wdPasteDefault)
'Selection.PasteAndFormat (wdFormatPlainText)

'save the new Word Document
wrdDoc.SaveAs "c:\Filename.doc", wdWord

'clean up code
wrdDoc.Close
Set wrdDoc = Nothing
Set WrdApp = Nothing

End Sub

Francis Hookham
____________________________________________

 

[Chip Pearson]

Francis,

Both Excel and Word have objects named Selection (and Range), and
it isn't clear in your code

Selection.PasteAndFormat (wdPasteDefault)

which Selection object (Excel or Word) is being used. The one the
compiler will choose depends on the order of the References in
the VBA Editor.

Assuming you're after the Word Selection object, you need to
prefix it with WrdApp

WrdApp.Selection.PasteAndFormat (wdPasteDefault)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Francis Hookham]

Many thanks Chip - all working according to plan except saving is to .txt
I need Save As... .rtf

Not sure how to make the XL sheet active in order to
Application.CutCopyMode = False

Code so far is pasted in below

I am most grateful

Francis Hookham

Dim WrdApp As New Word.Application
Dim WrdDoc As New Word.Document
Dim FirstRow As Integer 'number of first row in data area
Dim FirstCol As Integer 'number of first cloumn in data area
Dim LastRow As Integer 'number of last row in data area
Dim LastCol As Integer 'number of last cloumn in data area
Dim Count As Integer 'Count is a variable used for counting

Sub PasteAsPic()
FirstRow = 2 'first row defined
FirstCol = 1 'first col defined
LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row 'find last row
LastCol = Cells(FirstRow, Columns.Count).End(xlToLeft).Column 'find last
col
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy 'copy
data from XL sheet
' Create a new Word Document and make visible
Set WrdDoc = WrdApp.Documents.Add(DocumentType:=wdNewBlankDocument)
WrdApp.Visible = True
'Paste the copied data as Picture
WrdApp.Selection.PasteAndFormat (wdPasteDefault)
'run Sub to save this new Word Document on Desktop
SaveFileAsDiaryDate
'clean up code
'(next three lines don't seem to do anything)
WrdDoc.Close
Set WrdDoc = Nothing
Set WrdApp = Nothing
End Sub

Sub SaveFileAsDiaryDate()
DiaryName = Format(Date, "yymmdd") & " BatemanHallDiary" 'name for
today's file
'TEMPORARY while testing - allows Save As... using same name
Application.DisplayAlerts = False
WrdDoc.SaveAs DiaryName, wdWord 'file saved under that name
'TEMPORARY while testing - allows Save As... using same name
Application.DisplayAlerts = True
End Sub

 

[Francis Hookham]

(Not quite sure what is going on - I thought I posted this 2-3 days ago but
do not see it in the postings!)

Many thanks Chip - all working according to plan except saving is to .txt
I need Save As... .rtf

Not sure how to make the XL sheet active in order to
Application.CutCopyMode = False

Code so far is pasted in below

I am most grateful

Francis Hookham

Dim WrdApp As New Word.Application
Dim WrdDoc As New Word.Document
Dim FirstRow As Integer 'number of first row in data area
Dim FirstCol As Integer 'number of first cloumn in data area
Dim LastRow As Integer 'number of last row in data area
Dim LastCol As Integer 'number of last cloumn in data area
Dim Count As Integer 'Count is a variable used for counting

Sub PasteAsPic()
FirstRow = 2 'first row defined
FirstCol = 1 'first col defined
LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row 'find last row
LastCol = Cells(FirstRow, Columns.Count).End(xlToLeft).Column 'find last
col
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Copy 'copy
data from XL sheet
' Create a new Word Document and make visible
Set WrdDoc = WrdApp.Documents.Add(DocumentType:=wdNewBlankDocument)
WrdApp.Visible = True
'Paste the copied data as Picture
WrdApp.Selection.PasteAndFormat (wdPasteDefault)
'run Sub to save this new Word Document on Desktop
SaveFileAsDiaryDate
'clean up code
'(next three lines don't seem to do anything)
WrdDoc.Close
Set WrdDoc = Nothing
Set WrdApp = Nothing
End Sub

Sub SaveFileAsDiaryDate()
DiaryName = Format(Date, "yymmdd") & " BatemanHallDiary" 'name for
today's file
'TEMPORARY while testing - allows Save As... using same name
Application.DisplayAlerts = False
WrdDoc.SaveAs DiaryName, wdWord 'file saved under that name
'TEMPORARY while testing - allows Save As... using same name
Application.DisplayAlerts = True
End Sub