/********************************************************************************/
/*      Created By :    Umachandar Jayachandran                                 */
/*      Created On :    23 July 1999                                            */
/*      Description:    Demonstrates OLE automation by using WORD.              */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
Declare @WordObject int, @RetCode int, @Document int , @Filename varchar(255)
Exec @RetCode = sp_OACreate 'Word.Application', @WordObject OUTPUT, 4
If @RetCode <> 0 or @@Error <> 0 Goto OLE_Error_Handler

SELECT @WordObject

Exec @RetCode = sp_OAMethod @WordObject, 'Documents.Add', @Document OUTPUT
If @RetCode <> 0 or @@Error <> 0 Goto OLE_Error_Handler

Exec @RetCode = sp_OAMethod @WordObject,
                'Selection.TypeText("Created from within SQL Server SP using OLE Automation.")'
If @RetCode <> 0 or @@Error <> 0 Goto OLE_Error_Handler

-- There are several different methods to pass parameters to OLE
-- automation methods. These are discussed in BOL of SQL65/70.
/* -- This will also work fine.
DECLARE @Method varchar(255)
SELECT @Method = 'ActiveDocument.SaveAs("C:\Temp\Doc Created From SQLServer.doc")' 
Exec @RetCode = sp_OAMethod @WordObject, @Method                
*/
SELECT @Filename = 'C:\Temp\Doc Created From SQLServer.doc'
Exec @RetCode = sp_OAMethod @WordObject,
                'ActiveDocument.SaveAs' , NULL , @Filename
If @RetCode <> 0 or @@Error <> 0 Goto OLE_Error_Handler

Exec @RetCode = sp_OAMethod @WordObject, 'Quit'
Exec sp_OADestroy @WordObject
Goto Done

OLE_Error_Handler:
    Exec sp_displayoaerrorinfo @WordObject, @RetCode
    Exec @RetCode = sp_OAMethod @WordObject, 'Quit'
    Exec sp_OADestroy @WordObject
    Goto Done

Done:
Exec sp_OAStop

This page was last updated on May 01, 2006 04:28 PM.