Automating Excel from SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Wayne Sheffield » Automating Excel from SQL Server

103 posts, Page 11 of 11

«««7 8 9 10 11

Automating Excel from SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

WayneS

Posted Monday, February 21, 2011 10:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:38 PM
Points: 6,368, Visits: 8,230

I haven't tried performing Excel Automation on Windows Server 2008, so I don't know what would be required to make it work there. Sorry.

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #1067188

sleipner

Posted Wednesday, August 24, 2011 5:06 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 7:01 PM
Points: 17, Visits: 116

I'm guessing that to use automation you also have to have Excel installed on the SQL Server which in any organization with a controlled software environment may not be the case. I just checked on ours and it is not installed.

Another option rather than Excel automation from the server is to use an SSRS report linked to an Excel spreadsheet, or simply use the Workbook_Open event to have the spreadsheet update itself.

Post #1165049

rockbirdcth

Posted Monday, May 07, 2012 12:44 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 17, 2012 1:41 PM
Points: 1, Visits: 6

paulallen7 (12/5/2008)

Nice article, even if there are drawbacks to this approach it's info worth knowing.

We've been looking at a bit of SQL automation of excel. (not similar to the problem provided but some ideas could be adapted).

Would anybody know if this approach would work on a 64 bit SQL server? (no 64 bit implementation of jet). I've got a stand alone app running on our SQL server that talks to ACE and JET databases (using WOW64) but couldn't get my CLR proc to do the same as it's running under the control of 64 bit SQL.

Any thoughts would be much appreciated.

Thanks
Paul

-- EDIT: Just noticed that post above was written at the same time! :)

I use the automation cmd in sql to open and save excel file.

it looks like it doesn't work in x64 bit sql server. Could anyone provide some advice?

==============================================================
declare @xlApp integer, @rs integer
execute @rs = dbo.sp_OACreate 'Excel.Application', @xlApp OUTPUT
execute @rs = master.dbo.sp_OASetProperty @xlApp, 'ScreenUpdating', 'False'
execute @rs = master.dbo.sp_OASetProperty @xlApp, 'DisplayAlerts', 'False'

declare @xlWorkbooks integer
execute @rs = master.dbo.sp_OAMethod @xlApp, 'Workbooks', @xlWorkbooks OUTPUT

declare @xlWorkbook integer
execute @rs = master.dbo.sp_OAMethod @xlWorkbooks, 'Open', @xlWorkbook OUTPUT, 'C:\test.xls'

Declare @FileName varchar(200)
Set @FileName = 'C:\test_format.xls'
execute @rs = master.dbo.sp_OAMethod @xlWorkbook, 'SaveAs', null, @FileName, -4143

execute @rs = master.dbo.sp_OAMethod @xlWorkbook, 'Close'
execute @rs = master.dbo.sp_OAMethod @xlApp, 'Quit'

==================
Result: Command(s) completed successfully.

but these is no test_format.xls created.

Post #1296041

« Prev Topic | Next Topic »

103 posts, Page 11 of 11

«««7 8 9 10 11

Permissions