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.
-- 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.