Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Stored Procedure and Excel Ole Automation

Author  Topic 

JamesT
Yak Posting Veteran

97 Posts

Posted - 2001-07-18 : 12:19:01
Are there any resources, print or electronic, that reference the commands that can be used for OLE calls in stored procedures to Excel? There have been several samples for Word but I am needing to use Excel. The problem I am having is that most of the sample for coding use early binding and that method cannot be done using stored procedures. A sample of the early binding method is as follows:

Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "SERVER=SQLSRV;UID=user;PWD=password;DRIVER={SQL Server};DATABASE=PUBS"
.
.
.
With objExcel
.Visible = True
.Application.DisplayAlerts = False
.Workbooks.Open FileName:="\\server\directory\templates\template.xls", Editable:=True
End With ' excel application

******************************

In the stored procedure, the code would be more like this (granted this example is for word):

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

*******************************

I need a reference source for the excel commands so I can manipulate the excel document.

Any help is appreciated.
   

- Advertisement -