Export Excel-range to MS Word - Automation - Part I

[XL-Dennis]

Hi group,

This solution is lookin for a problem to be solved

However, I think this might be of general interest for the board and therefore I publish it here.

The key for the solution is to paste the range with the placement of wdInline. By that we can easily delete it and replace it with a new when necessary since it become a part of the InlineShapes-collection in the document.

To avoid any misunderstanding I explicit declare following:
- Yes, I know we can do it manually
- Yes, I don´t claim it´s the best solution

I welcome any suggestions & improvements:

<PRE>
<FONT color=blue>Sub </FONT>Excel_Range_Word()

<FONT color=#ff0000>'Here we use early binding which means that a
</FONT>
<FONT color=#ff0000>'reference must be set to MS Word Object Library x.x
</FONT>
<FONT color=#ff0000>'via Tools |Reference in the VB-editor
</FONT>
<FONT color=blue>Dim </FONT>wbBook<FONT color=blue> As</FONT><FONT color=blue> Workbook</FONT>

<FONT color=blue>Dim </FONT>wsSheet<FONT color=blue> As</FONT> Worksheet

<FONT color=blue>Dim </FONT>rnReport<FONT color=blue> As</FONT> Range

<FONT color=blue>Dim </FONT>wdApp<FONT color=blue> As</FONT> Word.Application

<FONT color=blue>Dim </FONT>wdDoc<FONT color=blue> As</FONT> Word.Document

<FONT color=blue>Dim </FONT>BMRange<FONT color=blue> As</FONT> Word.Range

<FONT color=blue>Dim </FONT>oShape<FONT color=blue> As</FONT> Word.InlineShape



<FONT color=blue>Set </FONT>wbBook = ThisWorkbook

<FONT color=blue>Set </FONT>wsSheet = wbBook.Worksheets("Sheet")



<FONT color=blue>With </FONT>wsSheet

<FONT color=blue>Set </FONT>rnReport = .Range("Rapport")

<FONT color=blue>End With</FONT>



Application.ScreenUpdating =<FONT color=blue> False</FONT>



rnReport.Copy



<FONT color=blue>Set </FONT>wdApp = CreateObject("Word.Application")



<FONT color=blue>Set </FONT>wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & "Dennis.doc")



<FONT color=#ff0000>'Here we assume that it exist only one OnlineShape and it must be
</FONT>
<FONT color=#ff0000>'deleted before the new report is inserted.
</FONT>
<FONT color=blue>With </FONT>ActiveDocument.InlineShapes(1)

.Select

.Delete

<FONT color=blue>End With</FONT>



<FONT color=blue>Set </FONT>BMRange = ActiveDocument.Bookmarks("Rapport").Range



<FONT color=blue>With </FONT>BMRange

.Select

.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _

Placement:=wdInLine, DisplayAsIcon:=False

<FONT color=blue>End With</FONT>



<FONT color=blue>With </FONT>wdApp.ActiveDocument

.Save

.Close

<FONT color=blue>End With</FONT>



wdApp.Quit



<FONT color=blue>Set </FONT>BMRange =<FONT color=blue> Nothing</FONT>

<FONT color=blue>Set </FONT>wdDoc =<FONT color=blue> Nothing</FONT>

<FONT color=blue>Set </FONT>wdApp =<FONT color=blue> Nothing</FONT>



<FONT color=blue>With </FONT>Application

.CutCopyMode =<FONT color=blue> False</FONT>

.ScreenUpdating =<FONT color=blue> True</FONT>

<FONT color=blue>End With</FONT>



MsgBox "The range has successfully been copied to Dennis.doc", vbInformation



<FONT color=blue>End Sub</FONT>


</PRE>

Kind regards,
Dennis

 

[Ivan F Moala]

Thanks Dennis

Added