Copy a worksheet or table from an Excel workbook to Word Doc

[water]

Glad to be of service

[PoojaKrishna]

Is there any way to paste the copied table directly to a word bookmark? I could paste the table to the beginning of the doc.

$oWord.Activedocument.Select
$oWord.Activedocument.Range(0,0)
$oWord.Activedocument.Range.paste

And the following code returns true.

msgbox(0,"",$o_doc.Bookmarks.Exists("Table"))

Please help.

Edited March 1, 2013 by PoojaKrishna

[kylomas]

@water,

I've been goofing around with this since the thread started and came up with this. 1ST time automating office products so it's probably not right. I read your thread on "wordex.au3" and was it seemed inconclusive as to whther we should use it or not, perhaps I'm missing the point.

Regardless, this creates a word table from an excel spread sheet.

expandcollapse popup

#include <Constants.au3>
#include <Excel.au3>
#include <Array.au3>
#include <WordEX.au3>

#AutoIt3Wrapper_Add_Constants=n

;---------------------------------------------------------------------------------------------------
; create an Excel test file
;---------------------------------------------------------------------------------------------------

Local $ExcelFile = @DesktopDir & '\testfile.xls', $ret

If FileExists($ExcelFile) Then
    $ret = FileDelete($ExcelFile)
    If $ret = 0 Then
        MsgBox($mb_systemmodal, '*** ERROR ***', 'File not deleted...file = ' & $ExcelFile)
        Exit
    EndIf
EndIf

Local $oExcel = _ExcelBookNew()

If @error = 1 Then
    MsgBox($mb_ok, 'Excel Error', 'Excel Object does not exist' & @LF & 'Object = ' & $oExcel)
    Exit
EndIf

_ExcelSheetNameSet($oExcel, 'Test Sheet #1')
_ExcelWriteCell($oExcel, 'Writing a Bunch of Numeric Test Values', 1, 5)
_ExcelFontSetProperties($oExcel, 1, 5, 1, 5, True)

For $1 = 3 To 10
    For $2 = 1 To 15
        _ExcelWriteCell($oExcel, Random(1, 100, 1), $1, $2)
    Next
Next

$ret = _ExcelBookSaveAs($oExcel, $ExcelFile, "xls", 0, 1)

If $ret = 0 Then
    Switch @error
        Case 1
            ConsoleWrite('Object does not exist' & @LF)
        Case 2
            ConsoleWrite('Invalid file type string' & @LF)
        Case 3
            ConsoleWrite('File exists and overwrite flag not set' & @LF)
    EndSwitch
EndIf

_ExcelBookClose($oExcel, 1)

;---------------------------------------------------------------------------------------------------
; now read list of sheets looking for sheet #1
;---------------------------------------------------------------------------------------------------

$oExcel = _ExcelBookOpen($ExcelFile, 0)

If $oExcel = 0 Then
    Switch @error
        Case 1
            ConsoleWrite('Object create failed' & @LF)
        Case 2
            ConsoleWrite('File Open Failed...File = ' & $ExcelFile & @LF)
    EndSwitch
EndIf

Local $aMySheets = _ExcelSheetList($oExcel)

For $1 = 0 To UBound($aMySheets) - 1
    If $aMySheets[$1] = 'Test Sheet #1' Then
        _my_sheet_routine($aMySheets[$1])
    EndIf
Next
_ExcelBookClose($oExcel, 1)

;---------------------------------------------------------------------------------------------------
; now process the selcted sheet
;---------------------------------------------------------------------------------------------------

Func _my_sheet_routine($sheet)

    _ExcelSheetActivate($oExcel, $sheet)
    Local $aSheet = _ExcelReadSheetToArray($oExcel)

    ;_arraydisplay($aSheet)

    Local $oWord = _Word_Create()
    If @error <> 0 Then Exit MsgBox(16, "Word UDF: _Word_DocTableWrite Example", "Error creating a new Word application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    Global $oDoc = _Word_DocAdd($oWord)
    If @error <> 0 Then Exit MsgBox(16, "Word UDF: _Word_DocAdd Example", "Error creating a new Word application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    Local $oRange = _Word_DocRangeSet($oDoc, -2)
    _Word_DocTableWrite($oRange, $aSheet)
    If @error <> 0 Then Return MsgBox(16, "Word UDF: _Word_DocTableWrite Example 1", "Error creating the table." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    MsgBox(0, 'Word Close', 'Pres <ENTER> to close Word Document')

    _Word_DocClose($oDoc)
    _word_quit($oWord)

EndFunc   ;==>_my_sheet_routine

kylomas

[PoojaKrishna]

Yes, I got the way to paste the table on Clipboard to a bookmark on Word Doc:

$oWord.Activedocument.Bookmarks("BookMarkName").range.paste

Thanks for the help friends.

[water]

I read your thread on "wordex.au3" and was it seemed inconclusive as to whther we should use it or not, perhaps I'm missing the point.

Kylomas,

the WordEX UDF can be used like any other UDF I have written (AD, Outlook ...). What I try to do is to get the WordEX UDF replace the Word UDF that comes with AutoIt.

The Devs haven't made up their mind yet because there are so many changes that all scripts using the "old" version will crash and need to be modified.

If the new Word UDF makes it into AutoIt I will brush up the Excel UDF (I've already started a thread). This will have script breaking changes as well but will give a lot of new functions.

[PoojaKrishna]

Range.PasteandFormat method can be used to keep the original table format while pasting.

Please see the following pages for more information:

http://msdn.microsoft.com/en-us/library/office/ff193749.aspx

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.word.wdrecoverytype(v=office.11).aspx

http://www.access-im-unternehmen.de/fileadmin/download/WordEnumerationen.txt