SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

JamesT
Yak Posting Veteran

USA
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")
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.

robvolk
Most Valuable Yak

USA
15676 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000