Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011»»

Automating Excel from SQL Server Expand / Collapse
Author
Message
Posted Friday, August 21, 2009 5:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 10:36 AM
Points: 123, Visits: 349

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.
Post #775481
Posted Thursday, October 1, 2009 12:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:13 AM
Points: 53, Visits: 231
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
Post #796549
Posted Friday, October 2, 2009 11:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 5,364, Visits: 8,951
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
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 #797093
Posted Friday, October 2, 2009 11:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:13 AM
Points: 53, Visits: 231
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
Post #797102
Posted Friday, February 19, 2010 6:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
Nicely written. Thanks Wayne.

Tom Garth
Vertical Solutions

"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
Post #869038
Posted Friday, February 19, 2010 7:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403
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.
Post #869051
Posted Friday, February 19, 2010 12:47 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 17,823, Visits: 15,750
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
Post #869358
Posted Friday, February 19, 2010 2:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 5,364, Visits: 8,951
Tom & Jason... thanks!


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 #869418
Posted Friday, April 23, 2010 4:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 3, Visits: 1,141
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
Post #909293
Posted Friday, February 18, 2011 2:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 12, 2011 1:05 AM
Points: 9, Visits: 33
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..
Post #1066209
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»

Permissions Expand / Collapse