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 1 of 11

1 2 3 4 5 »»»

Automating Excel from SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

WayneS

Posted Thursday, December 04, 2008 10:47 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 6,370, Visits: 8,235

Comments posted to this topic are about the item Automating Excel from SQL Server

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 #614268

Harinath Pottam

Posted Thursday, December 04, 2008 10:49 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, March 31, 2013 8:18 PM
Points: 17, Visits: 18

A good script, I was looking for some time.

Post #614270

Anipaul

Posted Friday, December 05, 2008 12:20 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:35 AM
Points: 4,789, Visits: 1,336

Nice article...:)

Post #614283

Piers Williams

Posted Friday, December 05, 2008 1:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 07, 2012 11:07 PM
Points: 6, Visits: 54

Yeah, but in reality:

- You'd at very least set the Excel worksheet up with data ranges, then all you have to do programatically is ask the worksheet to refresh itself from the database. Easier to maintain.

- You'd think very seriously about using Excel Services (or a 3rd party product) rather than kill your SQL Server (and your sanity) with COM calls. Not to mention the concurrency impact.

Just because we can, doesn't mean we should...

Post #614299

Qualicon

Posted Friday, December 05, 2008 2:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 14, 2012 2:17 AM
Points: 4, Visits: 46

Hi,

Maybe it's a marvelous idea, but......
Why do we need this ?

An Excel-user can create a Microsoft Query in Excel (OLEDB) and update the spreadsheet he created with all the formatting he wants.
The DBA creates a view for this purpose and give permission to the user who needs it.
Users can than select a subset of the data (if needed) by using parameters in the query.

Users are able to choose for automated refreshing of the data when openening their excel-spreadsheet or by command.

My experience is that when you build something specific for a user (formatting he wants, data he wants now), you will get a lot of requests for enhancements and little changements, which will keep you busy.

Regards,
Frank van de Ven

Post #614314

mosaic-263591

Posted Friday, December 05, 2008 2:21 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 7:37 AM
Points: 419, Visits: 556

Automating Excel is a very useful way to solve the kind of problem you describe, but I too would be wary of doing it from within SQL Server. For example if something goes wrong, how can you be sure never to leave an instance of Excel running in the background?

I have found it useful to automate Excel from scripts - in my case mainly VBS. You will find that you can use much the same Excel code as you are doing already, and you could access the database using ADODB connection, recordset and command objects and many more.

If you are into Powershell then you could use the .NET objects instead to access the database and benefit from much better error handling as well.

Post #614320

Samuel Vella

Posted Friday, December 05, 2008 2:31 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 328, Visits: 1,851

I'd have to agree with Piers,

This looks like it could be useful under certain circumstances and for that reason the article is worthwhile.

In most cases though, I would not want to ask the database to perform those operations. SSIS, VBA or a .NET application would be more appropriate.

Post #614323

David Johnson-311802

Posted Friday, December 05, 2008 2:41 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 05, 2008 2:34 AM
Points: 1, Visits: 6

Good article - very clear. It is good to know what is possible, even if there are drawbacks.

Please note that there is a comma missing, from before 'SaveAs'. Also, it would be worth mentioning that @value contains the value you want to save.

Post #614325

phcahill

Posted Friday, December 05, 2008 4:27 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, August 25, 2012 10:01 PM
Points: 11, Visits: 60

Will this work on 64 bit Sql Server?

Paul

Post #614368

paulallen7

Posted Friday, December 05, 2008 4:28 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, January 07, 2011 9:08 AM
Points: 23, Visits: 144

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! :)

Post #614369

« Prev Topic | Next Topic »

103 posts, Page 1 of 11

1 2 3 4 5 »»»

Permissions