Sign in to follow this  
Followers 0
PoojaKrishna

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

26 posts in this topic

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

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

#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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

$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.

Share this post


Link to post
Share on other sites

Could you start Word before you run your script?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Yes, I could open the word doc.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Getting the same error.

Share this post


Link to post
Share on other sites

Which versions of Excel and Word do you run?

Which version of AutoIt?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

AutoIt Version: 3.3.6.1

Excel and Word 2007

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

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 by PoojaKrishna

Share this post


Link to post
Share on other sites

$oExcel.Application.ActiveWorkbook.Sheets($nSheetNumber).UsedRange.Copy ;Copy the given range of cells
should copy all used cells of a range


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Yes, it worked !!!

Thank you so much for your help :-)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0