(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:=wdNewBlankDocum ent)
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
"Chip Pearson" wrote in message
...
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" wrote in message
...
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("B2:D10").Copy
' Create a new Word Document
Set wrdDoc = WrdApp.Documents.Add(DocumentType:=wdNewBlankDocum ent)
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
____________________________________________
"Martin Krastev" wrote in
message
...
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" ??????:
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("B2:D10").Copy
' Create a new Word Document
Set wrdDoc = WrdApp.Documents.Add(DocumentType:=wdNewBlankDocum ent)
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
"Lonnie M." wrote in message
ups.com...
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/micro...cd865d50a7b808
Example of opening and pasting to the Word document:
http://groups.google.com/group/micro...c733e1036c0ba0
HTH--Lonnie M.