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.
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:=TrueEnd 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, 4If @RetCode <> 0 or @@Error <> 0 Goto OLE_Error_HandlerSELECT @WordObjectExec @RetCode = sp_OAMethod @WordObject, 'Documents.Add', @Document OUTPUTIf @RetCode <> 0 or @@Error <> 0 Goto OLE_Error_HandlerExec @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 , @FilenameIf @RetCode <> 0 or @@Error <> 0 Goto OLE_Error_HandlerExec @RetCode = sp_OAMethod @WordObject, 'Quit'Exec sp_OADestroy @WordObjectGoto DoneOLE_Error_Handler:Exec sp_displayoaerrorinfo @WordObject, @RetCodeExec @RetCode = sp_OAMethod @WordObject, 'Quit'Exec sp_OADestroy @WordObjectGoto DoneDone:Exec sp_OAStop*******************************I need a reference source for the excel commands so I can manipulate the excel document. Any help is appreciated. |
|
|
|
|
|
|