July 14, 2003 at 3:01 pm
I am trying to open Excel file in DTS package and getting message 'Not a valid account name and password'
I wanted to export data to excel file.
Is there any way to export data to excel file in the Query Analyzer.
Thanks in advance
July 15, 2003 at 2:56 am
Hi balbirsinghsodhi,
quote:
I am trying to open Excel file in DTS package and getting message 'Not a valid account name and password'I wanted to export data to excel file.
Is there any way to export data to excel file in the Query Analyzer.
Thanks in advance
what about
DECLARE @desc varchar(255) -- Fehlerbeschreibung
DECLARE @Counter int
SET @Counter=0
DECLARE @ExlValue varchar(100)
DECLARE @ObjProp varchar(100)
DECLARE @ExcelObject int, @RetCode int, @Document int , @Filename varchar(255)
EXEC @RetCode = sp_OACreate 'Excel.Application', @ExcelObject OUTPUT, 4
IF @RetCode <> 0 or @@Error <> 0 GOTO OLE_Error_Handler
EXEC @RetCode = sp_OAMethod @ExcelObject, 'Workbooks.Open', @Document OUTPUT,'C:\SUCCESS.xls'
-- in case you are creating new Excel file change the upper row with the following
--EXEC @RetCode = sp_OAMethod @ExcelObject, 'Workbooks.Add', @Document OUTPUT
IF @RetCode <> 0 or @@Error <> 0 GOTO OLE_Error_Handler
SELECT @Filename = 'C:\XLS_FILE.xls'
DECLARE authors_cursor CURSOR FOR
SELECT MsgSubject FROM mails_header ORDER BY MsgSubject ASC
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Counter=@Counter+1
SET @ObjProp='Application.ActiveWorkbook.Sheets(1).Cells(' + cast(@Counter as varchar(2)) + ', 1).value'
-- genaration OF correct property addressing
EXEC @RetCode = sp_OASetProperty @ExcelObject, @ObjProp, @ExlValue
-- SET the value OF the property
FETCH NEXT FROM authors_cursor INTO @ExlValue
--IF @RetCode <> 0 OR @@Error <> 0 GOTO OLE_Error_Handler
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
EXEC @RetCode = sp_OAMethod @ExcelObject, 'Application.ActiveWorkbook.Save',Null
-- in case you are creating new Excel file change the upper row with the following
--EXEC @RetCode = sp_OAMethod @ExcelObject,'Application.ActiveWorkbook.SaveAs',Null, 'c:\success.xls'
IF @RetCode <> 0 or @@Error <> 0 GOTO OLE_Error_Handler
EXEC @RetCode = sp_OAMethod @ExcelObject, 'Quit'
-- I've commented the following line deliberately. SEE NOTE AT THE END
--EXEC sp_OADestroy @ExcelObject
GOTO Done
OLE_Error_Handler:
CLOSE authors_cursor
DEALLOCATE authors_cursor
EXEC sp_oageterrorinfo @ExcelObject, @RetCode out, @desc out
-- the last parameter gives small textua
-- l description of the error. It was very
-- usefull!!
SELECT @retCode, @desc
EXEC @RetCode = sp_OAMethod @ExcelObject, 'Quit'
EXEC sp_OADestroy @ExcelObject
GOTO Done
Done:
--EXEC sp_OAStop
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 15, 2003 at 2:58 am
quote:
I am trying to open Excel file in DTS package and getting message 'Not a valid account name and password'I wanted to export data to excel file.
forgot to mention that there is one parameter (I think, the third or forth) in the workbooks.open method that lets you specify a password for a protected file
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 15, 2003 at 11:34 am
Thanks Frank. You are the man.
I modified your script according to my requirement.
Can you please let me know how to add more columns in excel file. your script will add only one column. I think we need to modify the following line of code.
SET @ObjProp='Application.ActiveWorkbook.Sheets(1).Cells(' + cast(@Counter as varchar(2)) + ', 1).value'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply