Automating Excel from SQL Server

Question

Automating Excel from SQL Server

Viewing 13 posts - 91 through 102 (of 102 total)

You must be logged in to reply to this topic. Login to reply

Mile Higher Than Sea Level SSCarpal Tunnel Points: 4828 More actions · Answer 1

:-D:-P:-D:-P:w00t::w00t::laugh:

OHH that was funny! Sorry to take so long to get back, had to get up off the floor!

Microsoft office products are extremely poorly supported for office automation. The business model is that every ma and pa shop with 2 to 500 employees, will buy web based priducts.

I invested into Microsoft stock until two years ago, so you can blame me... the stock holder.

In October, I will be really testing the new Office version with the new SQL server version. I honestly don't expet Microsoft to do anymore with the current versions. But, then again I don't make the decisions, because I am no longer a stock owner. 😉

mfink SSCommitted Points: 1632 More actions · Answer 2

Wayne

Thank you so much. I was attracted to thei article because a vendor is using SP_OACreate and SP_OAMethod. And I wanted to learn more about those commands.

I have SQL2005 installed on my laptop (dev environment) Windows vista and MS office are installed there too.

You code was very well put I keyed it line for line clean parse and it runs without errors and @rs is 0

However the excel workbook the I placed on my D:\Production\Test\SQL_Object.xls does not change

I have change the Cells.SpecialCells(11).Row to 2 and

Cells.SpecialCells(11).Column to 1

I have set the @value to 80

I am guessing the value of cell a:2 will become a bolded 80%

But nothing anywhere.

the worksheet name change or title name change doesn't work either

I have change the Automation feature in surface configuration too

Mark F

WayneS SSC Guru Points: 95460 More actions · Answer 3

Mark,

I've been running into that problem also since I got my new laptop w/ Vista. I haven't had time to check out why.

Are you running 64-bit?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mfink SSCommitted Points: 1632 More actions · Answer 4

Wayne

Windows Vista Ultima

Service Pack 1

on a Dell Studio 1737

Intel (R) Core(TM)2 Duo CPU P8600 @ 2.40ghz 2/40ghz

4.00 gb

32 bit operating system

Mark

Tom Garth SSCertifiable Points: 6193 More actions · Answer 5

Nicely written. Thanks Wayne.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 6

FWIW: One more vote for using a pull approach instead of a push. Simply create views/procs to select the data required, populate the workbook with those external data sources, and let the user chose "Data/Refresh All". Much easier, simpler, and faster.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Nice article Wayne.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95460 More actions · Answer 8

Tom & Jason... thanks!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Playtoe Grasshopper Points: 21 More actions · Answer 9

Very useful article.

I had some problems getting it to run on my PC.

I managed to work out what it was.

In case anyone else has the same experience, here was my solution.

I am running SQL Server 2005 on my local machine which is running Windows Vista.

When I tried to run sp_OACreate I was getting access denied.

A look in the log showed:

SourceDCOM

Category(0)

Event3221235488

UserNT AUTHORITY\NETWORK SERVICE

ComputerMyComputer

Message

The description for Event ID '-1073731808' in Source 'DCOM' cannot be found. The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them. The following information is part of the event:'machine-default', 'Local', 'Activation', '{00024500-0000-0000-C000-000000000046}', 'NT AUTHORITY', 'NETWORK SERVICE', 'S-1-5-20', 'LocalHost (Using LRPC)'

Solution:

From Windows Start Menu type DCOMCNFG to run Component Services.

In Component Services click on Computers | My Computer and then click on 'DCOM Config'.

From the list of objects on the right panel find the 'Microsoft Excel Application' and right click,

select 'Properties' and click on the 'Security' tab.

Under 'Launch and Activation permission' click on 'Edit', add the 'NETWORK

SERVICE' and click on 'Local Activation' for the account.

That did it for me!

Easy when you know how, but it did take me a good few hours to work it out.

Hope I can save someone else some time with this.

Gary

munawargani2001 SSC Journeyman Points: 95 More actions · Answer 10

Its a great article.

It works fine with Windows Server 2003, but not with Windows Server 2008 and SQL Server 2008. I have Office 2007 installed.

Can you suggest any work around..

Thanks in advance..

WayneS SSC Guru Points: 95460 More actions · Answer 11

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
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sleipner SSCommitted Points: 1664 More actions · Answer 12

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.

rockbirdcth Newbie Points: 3 More actions · Answer 13

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.