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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Stored Procedure and Excel Ole Automation
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

97 Posts

Posted - 07/18/2001 :  12:19:01  Show Profile  Reply with Quote
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")
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
Exec sp_displayoaerrorinfo @WordObject, @RetCode
Exec @RetCode = sp_OAMethod @WordObject, 'Quit'
Exec sp_OADestroy @WordObject
Goto Done
Exec sp_OAStop


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

Any help is appreciated.

Most Valuable Yak

15732 Posts

Posted - 07/18/2001 :  13:11:32  Show Profile  Visit robvolk's Homepage  Reply with Quote
Instead of:

Exec @RetCode = sp_OACreate 'Word.Application', @WordObject OUTPUT, 4

Use this:

Exec @RetCode = sp_OACreate 'Excel.Application', @ExcelObject OUTPUT, 4 --need to declare @ExcelObject

For a reference on Excel objects, open the Excel help file, go to the Contents tab, and look for an Excel Visual Basic Reference heading. Clicking on it will direct you to the Excel VB help file, and everything you could possibly want is there. Excel 97 had very good examples of code as well, I don't know about 2000 or higher but they're probably the same.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000