SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automating Excel from SQL Server


Automating Excel from SQL Server

Author
Message
WayneS
WayneS
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20902 Visits: 10652
Comments posted to this topic are about the item Automating Excel from SQL Server

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

Harinath Pottam
Harinath Pottam
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 21
A good script, I was looking for some time.
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9653 Visits: 1407
Nice article...Smile



Piers Williams
Piers Williams
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 56
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...
Qualicon
Qualicon
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 96
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
mosaic-263591
mosaic-263591
SSC-Addicted
SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)

Group: General Forum Members
Points: 487 Visits: 566
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.
Samuel Vella
Samuel Vella
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1941 Visits: 2144
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.
David Johnson-311802
David Johnson-311802
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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.
phcahill
phcahill
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 62
Will this work on 64 bit Sql Server?

Paul



paulallen7
paulallen7
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 148
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! Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search