in reply to Trying to use Win32::OLE PasteExcelTable method

First question is: what are you trying to do? At first, you're describing a copy (from Excel) and paste (into Word) while maintaining formatting. But then you're using something called 'LinkedToExcel', which sounds like something totally different. In Word, you can "link" to an Excel file, which is totally different than the copy and paste that you say that you wanted to.

When trying to write Perl code to control Office products via Win32::OLE, one of the best methods for figuring out code is to record a macro to do the desired task(s) and then work to convert the source code of that macro into Perl. You can find more details at Use The Macro Recorder, Luke (Re: Using Win32::OLE and Excel - Tips and Tricks) and other nodes if you do some more searching. Basically using this macro technique and using the Office OLE documentation is what I have used to create my scripts that automate Excel.

Out of curiosity, I just tried the micro technique on my computer that has Office 2010. The paste method used was specifying that I was pasting an OLE object and it did maintain the formatting that was used in Excel. Looking at the macro source code, there was no 'LinkedToExcel' anywhere.

I haven't written Perl code to automate Office products in quite a while so my VB to Perl conversion skills are quite rusty right now. Below is the source code of the macro that I recorded. Thought I'd share it so that you (and/or others) can convert that into Perl. (If I find time this weekend, I may try to shake off the rust to see if I can correctly convert that code into Perl.)

Sub Macro1()
'
' Macro1 Macro
'
'
    Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, Pl
+acement _
        :=wdInLine, DisplayAsIcon:=False
End Sub

[download]

One last thing. The macro source code above utilizes VB's 'selection'. I'd recommend not trying to use that in your Perl code. See Excel’s Select and Activate considered harmful for more details from someone who is far more knowledgeable than me on this topic.

Replies are listed 'Best First'.
Re^2: Trying to use Win32::OLE PasteExcelTable method
by aaronbaum2000 (Initiate) on Oct 02, 2014 at 15:02 UTC

    The 'LinkedToExcel" is a required parameter for the PasteExcelTable method. This is the macro line I'm trying to convert from VB, as shown in my Word madro editor:

    Selection.PasteExcelTable False, False, False

    [download]

    If I look up the PasteToExcel Method in Word help (I'm using Word2007) it shows the following parameters:

    Pastes and formats a Microsoft Excel table.

    Syntax

    expression.PasteExcelTable(LinkedToExcel, WordFormatting, RTF)

    expression Required. A variable that represents a Selection object.

    Parameters

    LinkedToExcel Required Boolean True links the pasted table to the original Excel file so that changes made to the Excel file are reflected in Microsoft Word.

    WordFormatting Required Boolean True formats the table using the formatting in the Word document. False formats the table according to the original Excel file.

    RTF Required Boolean True pastes the Excel table using Rich Text Format (RTF). False pastes the Excel table as HTML.

    Example

    This example pastes an Excel table into the active document. The parameters specify that the pasted table is linked to the Excel file, retains the original Excel formatting, and is pasted as RTF. This example assumes that the Clipboard contains an Excel table.

    Visual Basic for Applications 
Sub PasteExcelFormatted()
    Selection.PasteExcelTable _
        LinkedToExcel:=True, _
        WordFormatting:=False, _
        RTF:=True
End Sub

    [download]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom