| Author |
 Topic  |
|
|
JamesT
Yak Posting Veteran
USA
97 Posts |
 Posted - 07/18/2001 : 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. |
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 07/18/2001 : 13:11:32
|
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.
|
 |
|
| |
Topic |
|
|
|
Stored Procedure and Excel Ole Automation
