Paste Excel Table in a Word document at a specific location

[nicnad]

Hi,

I have the following code to copy paste a range from an excel workbook to a specific location in a word document :

Public Declare Function CountClipboardFormats Lib "user32" () As Long 
Dim appWd As Word.Application 
Dim wdFind As Object 
Dim ClipEmpty As New MSForms.DataObject 
Dim ClipT As String 
Function IsClipboardEmpty() As Boolean 
    IsClipboardEmpty = (CountClipboardFormats() = 0) 
End Function 
Sub CheckClipBrd() 
    If IsClipboardEmpty() = True Then ClipEmpty.PutInClipboard 
End Sub 
Sub FormatPaste() 
    wdFind.Replacement.Text = "" 
    wdFind.Forward = True 
    wdFind.Wrap = wdFindContinue 
    wdFind.Execute 
    Call CheckClipBrd 
    appWd.Selection.PasteSpecial DataType:=wdPasteBitmap 
    CutCopyMode = False 
End Sub 
Sub CopyDatatoWord() 
    Dim docWD As Word.Document 
    Dim sheet1 As Object 
    Dim sheet2 As Object 
    Dim saveCell1 As String 
    Dim saveCell2 As String 
    Dim saveCell3 As String 
    Dim dir1 As String 
    Dim dir2 As String 
    Set appWd = CreateObject("Word.Application") 
    appWd.Visible = True 
    Set docWD = appWd.Documents.Open(ThisWorkbook.Path & "\" & "webpage.htm") 
    Set sheet1 = ThisWorkbook.Sheets("Matrix") 
    Set wdFind = appWd.Selection.Find 
    ClipT = "  " 
    ClipEmpty.SetText ClipT 
    sheet1.Range("C2:F247").Copy 
    wdFind.Text = "111" 
    Call FormatPaste 
    docWD.SaveAs (ThisWorkbook.Path & "\" & "TEST.htm") 
    appWd.Quit 
    Set appWd = Nothing 
    Set docWD = Nothing 
    Set appXL = Nothing 
    Set wbXL = Nothing 
End Sub

<!-- BEGIN TEMPLATE: bbcode_code -->

This works fine, but the pasting is done and bitmap and I loose the table formatting

I would like to replace this line (in sub FormatPaste):

appWd.Selection.PasteSpecial DataType:=wdPasteBitmap

with this line :

appWd.Selection.PasteExcelTable

The thing is, when I use the second line I get the error "Argument not optional".

Any idea what is causing this and how to solve it?

Or another way to do this?

Thank you for your help!
​

 

[vaskov17]

http://msdn.microsoft.com/en-us/library/bb241755(v=office.12).aspx

 

[nicnad]

Thank you very much for the quick reply.

This link had exactly what I was looking for in it.