Automating Excel from SQL Server

  • :-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. 😉

  • 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

  • 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

  • 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

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

  • 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

  • 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

  • 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

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

  • 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

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

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

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

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