Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011

Automating Excel from SQL Server Expand / Collapse
Author
Message
Posted Monday, February 21, 2011 10:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:04 PM
Points: 6,594, Visits: 8,882
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
Posted Wednesday, August 24, 2011 5:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 15, 2014 3:30 PM
Points: 30, Visits: 146
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
Posted Monday, May 7, 2012 12:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase «««7891011

Permissions Expand / Collapse