Tom’s Tutorials For Excel: Export Word To Excel
For importing part of a Word document into your Excel workbook, here is a macro that imports the second paragraph from a Word doc.
Sub ExportWordToExcel()
'Declare object variables.
Dim WdApp As Object, wddoc As Object
'Open Word
Set WdApp = CreateObject("Word.Application")
'Open the Word document
Set wddoc = WdApp.Documents.Open(Filename:="C:\Your\File\Path\myWordDoc.docx")
'Copy paragraph 2
wddoc.Paragraphs(2).Range.Copy
'Activate your workbook and go to the paste destination of Sheet1 cell A1.
Application.Goto ThisWorkbook.Worksheets("Sheet1").Range("A1")
'Paste paragraph 2 from the Word document.
ActiveSheet.Paste
'Close the Word document, no need to save changes.
wddoc.Close savechanges:=False
'Quit the Word application.
WdApp.Quit
'Set the Object variables to Nothing to release system memory.
Set wddoc = Nothing
Set WdApp = Nothing
End Sub
Hi Tom,
I have recently bought your book Excel vba which is very interesting. I found the code to export a range from excel to word. But I am wondering how to specify in the word document where to paste the ranges from excels. Like, to specify the paragraphs. Since i will have to export ranges from various spreadsheets to the same word document.
Thank you for your help
Hi, and thank you for buying my book.
Using the example assumptions in the code on page 403, this will copy range A1:H25 and make it be the third paragraph in the destination Word document.
Sub ExportFromExcelToWord()
Application.ScreenUpdating = False
Range("A1:H25").Copy
Dim WdApp As Object, wddoc As Object
Set WdApp = CreateObject("Word.Application")
Set wddoc = WdApp.Documents.Open(Filename:="C:\Your\File\Path\myWordDoc.docx")
wddoc.Paragraphs(3).Range.Paste
wddoc.Close Savechanges:=True
WdApp.Quit
Set wddoc = Nothing
Set WdApp = Nothing
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub