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

[PoojaKrishna]

Hi Friends,

I want to copy a table on an Excel worksheet to a MS Word document. A copy and paste method worked when I tried it manually.

Can we directly pass a word document object to the excel copy method? Please suggest me some way to copy an excel worksheet table to a word document.

Func _TableCopy($sExcelFile, $sWordDocument)
 
 $oExcel = ObjCreate("Excel.Application") ;create Excel object
 $oWordApp = _WordCreate ($sWordDocument, 0, 0)
 $oDocuments = _WordDocGetCollection ($oWordApp,1)
 $oExcel.WorkBooks.Open($sExcelFile) ;Open input file
 $oExcel.Application.ActiveWorkbook.Sheets($aSheetNames[1]).Copy()
 
 
 $oExcel.Quit ;Quit Excel
 
EndFunc ;=>_TableCopy

[water]

You can't use the copy method. It creates a copy of a sheet in the workbook.

You could either use the clipboard or have a look at the DDE like DDEPoke Method

[PoojaKrishna]

Hi Water,

Thank you so much for your reply. I have tried with both clipboard and DDE methods but I couldn't succeed.

I couldn't find any forum posting on using ClipBoard to get table from an excel worksheet. Do I have to use any particular format with _ClipBoard_SetData?

I have tried with DDE and the code is attached below. Word returns an error 'Word has stopped working' and then recovers the page.

Func _TableCopy($sExcelFile, $sWordDocument)

If Not FileExists($sExcelFile) Then ;If Excel file path does not exist
SetError(1);Set error
Return (0);Return
EndIf
If Not FileExists($sWordDocument) Then ;If Word file path does not exist
SetError(2);Set error
Return (0);Return
EndIf

$oExcel = ObjCreate("Excel.Application") ;create Excel object
If @error Then
SetError(3);Set error
Return (0);Return
Endif
$oExcel.WorkBooks.Open($sExcelFile) ;Open input file

$channelNumber = $oExcel.DDEInitiate( "WinWord", $sWordDocument)
$rangeToPoke = $oExcel.Application.ActiveWorkbook.Sheets(1).Range("A1")
$oExcel.DDEPoke ($channelNumber, "\StartOfDoc", $rangeToPoke)
$oExcel.DDETerminate($channelNumber)
$oExcel.Quit ;Quit Excel
EndFunc ;=>_TableCopy

Can you please help to fix the bug?

[water]

You need to add some error checking to your code. A COM error handler as described in ObjEvent would give us a lot of information if there is an error.

[PoojaKrishna]

Yes, I added an error handler but it returns nothing and still the problem is there.

expandcollapse popup

#include <File.au3>
#include <Excel.au3>
#include <Word.au3>
#include <Array.Au3>
#include <Clipboard.au3>
#RequireAdmin
;Variables
Global $g_eventerror = 0 ; Variable set on COM error. Must be reset after handling.
Global $oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ; Custom error handler
Local $sExcelFile = "original 2.xlsx" ;Input dir
Local $sWordDocument = "WordDoc.docx" ;Result file name
Local $nCopy = _TableCopy($sExcelFile, $sWordDocument)
If @error Then
MsgBox(0,@ScriptName & " - Error", "@error = " & @error)
Else
Msgbox(0, @ScriptName, "Number of moves done: " & $nCopy)
EndIf

Func _TableCopy($sExcelFile, $sWordDocument)

If Not StringInStr($sExcelFile,"\") Then $sExcelFile = @ScriptDir & "\" & $sExcelFile ;If no dir given
If Not StringInStr($sWordDocument,"\") Then $sWordDocument = @ScriptDir & "\" & $sWordDocument ;If no dir given

If Not FileExists($sExcelFile) Then ;If Excel file path does not exist
SetError(1);Set error
Return (0);Return
EndIf
If Not FileExists($sWordDocument) Then ;If Word file path does not exist
SetError(2);Set error
Return (0);Return
EndIf

$oExcel = ObjCreate("Excel.Application") ;create Excel object
If @error Then
SetError(3);Set error
Return (0);Return
Endif

$oExcel.WorkBooks.Open($sExcelFile) ;Open input file
$channelNumber = $oExcel.DDEInitiate( "WinWord", $sWordDocument)
$rangeToPoke = $oExcel.Application.ActiveWorkbook.Sheets(1).Range("A1")
$oExcel.DDEPoke ($channelNumber, "\StartOfDoc", $rangeToPoke)

$oExcel.DDETerminate($channelNumber)
$oExcel.Quit ;Quit Excel
EndFunc ;=>_TableCopy

; This is my custom defined error handler
Func MyErrFunc()
Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"     & @CRLF & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "       & @TAB & hex($oMyError.number,8) & @CRLF & _
             "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "       & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "     & @TAB & $oMyError.helpfile     & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
         )
        
Local $err = $oMyError.number
If $err = 0 Then $err = -1

$g_eventerror = $err ; to check for after this function returns
Endfunc

[water]

Can you insert a MsgBox after each DDE statement so we can see where the problem occurres?

[PoojaKrishna]

$oExcel.WorkBooks.Open($sExcelFile) ;Open input file
Msgbox(0,"",1)
$channelNumber = $oExcel.DDEInitiate( "WinWord", $sWordDocument)
Msgbox(0,"",2)
$rangeToPoke = $oExcel.Application.ActiveWorkbook.Sheets(1).Range("A1")
Msgbox(0,"",3)
$oExcel.DDEPoke ($channelNumber, "\StartOfDoc", $rangeToPoke)
Msgbox(0,"",4)
$oExcel.DDETerminate($channelNumber)
Msgbox(0,"",5)

On DDEInitiate Excel shows the following message:

---------------------------

Microsoft Office Excel

---------------------------

Remote data not accessible.

To access this data Excel needs to start another application. Some legitimate applications on your computer could be used maliciously to spread viruses or damage your computer. Only click Yes if you trust the source of this workbook and you want to let the workbook start the application.

Start application 'WINWORD.EXE'?

---------------------------

Yes No

---------------------------

And on DDEPoke Word stops working and reopens the page.

[water]

Could you start Word before you run your script?

[PoojaKrishna]

Yes, I could open the word doc.

[water]

Just to see if we get the same or a different error message.

[PoojaKrishna]

Getting the same error.

[water]

Which versions of Excel and Word do you run?

Which version of AutoIt?

[PoojaKrishna]

AutoIt Version: 3.3.6.1

Excel and Word 2007

[water]

Unfortunately I'm not at my Windows PC right now. I will test with Office 2010 32 bit on Windows 7 64 bit and AutoIt 3.3.8.1 tomorrow.

[PoojaKrishna]

Thank you so much for your time. If I get any solution before that, I will post it here.

[PoojaKrishna]

Hi,

I succeeded in getting data from Excel sheet to Clipboard. I will work on pasting the data into word document, tomorrow and will update here.

I want to paste an entire worksheet to a word document bookmark.

$oExcel = ObjCreate("Excel.Application") ;create Excel object
If @error Then
   SetError(3);Set error
   Return (0);Return
Endif
 
$oExcel.WorkBooks.Open($sExcelFile) ;Open input file
$oExcel.Application.ActiveWorkbook.Sheets(1).Range("A1").Copy

$oExcel.Quit ;Quit Excel

[water]

A Range is copied to the Clipboard. If you copy a Sheet then it would create a new Sheet in the Workbook.

To insert the data into Word use Range.Paste.

[PoojaKrishna]

Thank you Water.

Still working on the script to get the actual range of an Excel worksheet. The code below is working fine, If we specify the start and end cell of the worksheet to copy.

$sWordDocument = "MyWordDoc.Docx"
$sExcelFile = "MyExcelFile.xlsx"
$nSheetNumber = 2
$sFirstCell = "B2"
$sLastCell = "I6"
$oExcel = ObjCreate("Excel.Application") ;create Excel object
If @error Then
SetError(3);Set error
Return (0);Return
Endif
$oWord = ObjCreate("Word.Application") ;Create Word object
If @error Then
SetError(4);Set error
Return (0);Return
Endif
$o_doc = $oWord.Documents.Open ($sWordDocument) ;Open word document
If $o_doc = 0 Then
SetError(5);Set error
Return (0);Return
Endif
$oExcel.WorkBooks.Open($sExcelFile) ;Open Excel workbook
$nSheetCount = $oExcel.Application.ActiveWorkbook.Sheets.Count ;get number of sheets of the work book
If $nSheetCount < $nSheetNumber Then ;If given sheet number out of range
SetError(6);Set error
Return (0);Return
Endif

$sRange = $sFirstCell & ":" & $sLastCell
$oExcel.Application.ActiveWorkbook.Sheets($nSheetNumber).Range($sRange).Copy ;Copy the given range of cells
$oWord.Activedocument.Select ;Past the copied cells into word document
$oWord.Activedocument.Range(0,0)
$oWord.Activedocument.Range.paste
$o_doc.save
$oWord.Quit
$oExcel.Quit ;Quit Excel

Edited March 1, 2013 by PoojaKrishna

[water]

$oExcel.Application.ActiveWorkbook.Sheets($nSheetNumber).UsedRange.Copy ;Copy the given range of cells

should copy all used cells of a range

[PoojaKrishna]

Yes, it worked !!!

Thank you so much for your help :-)