|
Working between XL and Word
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 |
Working between XL and Word
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. "Francis Hookham" wrote: 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 |
Working between XL and Word
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. |
Working between XL and Word
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. |
Working between XL and Word
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. |
Working between XL and Word
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. |
Working between XL and Word
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. |
Working between XL and Word
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. |
Working between XL and Word
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. |
Working between XL and Word
(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. |
| All times are GMT +1. The time now is 01:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com