August 21, 2009 at 5:19 pm
:-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. 😉
October 1, 2009 at 12:13 pm
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
October 2, 2009 at 11:04 am
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
October 2, 2009 at 11:13 am
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
February 19, 2010 at 6:54 am
February 19, 2010 at 7:04 am
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.
February 19, 2010 at 12:47 pm
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
February 19, 2010 at 2:02 pm
Tom & Jason... thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 23, 2010 at 4:23 am
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:
Source DCOM
Category (0)
Event 3221235488
User NT AUTHORITY\NETWORK SERVICE
Computer MyComputer
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
February 18, 2011 at 2:34 am
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..
February 21, 2011 at 10:06 am
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
August 24, 2011 at 5:06 pm
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.
May 7, 2012 at 12:44 pm
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 102 (of 102 total)
You must be logged in to reply to this topic. Login to reply