InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 10:41:13 GMT20http://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspx[quote][b]paulallen7 (12/5/2008)[/b][hr]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.ThanksPaul-- EDIT: Just noticed that post above was written at the same time! :)[/quote]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. Mon, 07 May 2012 12:44:04 GMTrockbirdcthhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxI'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.Wed, 24 Aug 2011 17:06:04 GMTsleipnerhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxI 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.Mon, 21 Feb 2011 10:06:05 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxIts 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..Fri, 18 Feb 2011 02:34:43 GMTmunawargani2001http://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxVery 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 DCOMCategory (0)Event 3221235488User NT AUTHORITY\NETWORK SERVICEComputer MyComputerMessageThe 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 'NETWORKSERVICE' 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.GaryFri, 23 Apr 2010 04:23:43 GMTPlaytoehttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxTom & Jason... thanks!Fri, 19 Feb 2010 14:02:18 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxNice article Wayne.Fri, 19 Feb 2010 12:47:29 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxFWIW: 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.Fri, 19 Feb 2010 07:04:19 GMTJim Russell-390299http://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxNicely written. Thanks Wayne.Fri, 19 Feb 2010 06:54:35 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxWayneWindows Vista UltimaService Pack 1on a Dell Studio 1737Intel (R) Core(TM)2 Duo CPU P8600 @ 2.40ghz 2/40ghz4.00 gb32 bit operating systemMarkFri, 02 Oct 2009 11:13:54 GMTmfinkhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxMark,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?Fri, 02 Oct 2009 11:04:10 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxWayne 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 changeI have change the Cells.SpecialCells(11).Row to 2 andCells.SpecialCells(11).Column to 1I have set the @value to 80I 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 eitherI have change the Automation feature in surface configuration tooMark FThu, 01 Oct 2009 12:13:12 GMTmfinkhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspx:-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. ;-)Fri, 21 Aug 2009 17:19:17 GMTMile Higher Than Sea Levelhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspx[quote][b]cmcc (8/21/2009)[/b][hr]My only consolation is that there seem to be so many posts about this issue both here and on other forums that they have to do something about it, right?[/quote]Dream on... MS doesn't even have 64-bit drivers for Excel yet. The 32-bit driver can't insert into a named range if there is anything under it, even if all the data will fit into the named range. Don't see them to eager to "do something about it". :(Fri, 21 Aug 2009 15:00:09 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxRob, thanks for the reply.I started out on this journey with trying to use the Interop assemblies in an SSIS package, only to find out that there were also issues with running this on 64-bit SQL Server 2005, my production environment.It looks like one of the programmer/analysts here is going to write a utility that will poll certain folders, open workbooks, refresh and save. Not something I wanted to tackle so I'm glad this need has gotten some attention here in IT R&D. Turns out I wasn't the only one asking for this capability and the other person was able to allocate the resources to do it. Much as Microsoft integrates products, there are still some holes that we have to plug with some effort.My only consolation is that there seem to be so many posts about this issue both here and on other forums that they have to do something about it, right?Fri, 21 Aug 2009 14:11:26 GMTcmcchttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspx[quote][b]david.c.reynolds (8/21/2009)[/b][hr]Wayne, This is a very fascinating article. I have always wondered it there was a way to write data from SQL directly into an Excel Spreadsheet. However, I followed your article's instructions and was not successful in getting the process to work. Can you suggest what I might be doing wrong?[/quote]Not without more information about what kind of problems / errors you're having.Fri, 21 Aug 2009 14:04:29 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxRE: Office 2003 Redistributable Primary Interop AssembliesStarting back on page 7 and before.... Recap from MemoryThis is one web site that I was attempting to get this working myself. Sorry if I can remember it all.Basically, I heard (rumored so let's keep it at that level) that Microsoft Accounting used this as the preference.I can understand why myself why they would want to create rich Excel documents with current data AND formulas and other embedded features rather than just scraping an HTML or XML data onto a spreadsheet.Searching further and listening to other reasonable people, I believe that Microsoft failed to support or document this feature to a point we can really use it.My interest is now more concerned with the next version of SQL Serve and Windows 7.Having said that, you reminded me about this, so I will send off an e-mail to a long time friend at (Micro****) and see if they can shed some light. I use to shed cattle and sheep, now I want to shed light? ;-)Since then, I have fallen back to automation methods, one the most efficient being Pass Through T-SQL queries - also very sketchy in its documentation.If anyone has any news on how to use Interop or something like it, please, please post here!Fri, 21 Aug 2009 13:02:25 GMTMile Higher Than Sea Levelhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxWayne, This is a very fascinating article. I have always wondered it there was a way to write data from SQL directly into an Excel Spreadsheet. However, I followed your article's instructions and was not successful in getting the process to work. Can you suggest what I might be doing wrong?Fri, 21 Aug 2009 11:15:54 GMTdavid.c.reynoldshttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxhttp://www.excelforum.com/Since the last question was about Excel (getting data from SQL Server)My favorite place to post and find answers (with code and other details) for Excel - - Getting data from SQL Seerver- Adding data back into SQL ServerThe code is great and the organization is one of the better forums out there.Thu, 20 Aug 2009 08:28:02 GMTMile Higher Than Sea Levelhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxOLE Automation - Absolutely correct.SQL Server does provide another option to do what OLE Automation using (almose) Pure T-SQL. On a previous thread, we discussed how it would be a wonderful world if it was supported. However, it turns out that for this option to work, a copy of Excel must be installed... on the SQL Server. And this is what "everyone" was warning against.Thanks for bringing that up. So many options... so little posting time.Thu, 20 Aug 2009 08:21:07 GMTMile Higher Than Sea Levelhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxThank you RobI will just add one aspect."I have been repeatedly advised not to have SQL Server run Office automation"You don't need to do that.With Remote OLE DB you can have SQL Server and Office automation on separetedmachines. In fact the client can run Excel with VBA etc to fetch data from SQL Server ortransmit data to SQL Server./Gosta Thu, 20 Aug 2009 01:48:23 GMTGosta Munktellhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspx"I have been repeatedly advised not to have SQL Server run Office automation"We all hear this, probably because Office needs security patches from time to time. I would never suggest letting users have access to an office application running on the server.But, in most cases, you will loose this argument to the server group.[b]This is probably more than you ever wanted to know...[/b]Here is what I have in one support environment.1. Access MDE with a secure MDA application. I use 3rd party tools to provide modern tree views and an Outlook style look and feel. This resides on each users PC.2. Access uses linked Views - the vast majority of links are to SQL Views built with T-SQL. Access VBA also calls on Stored Procedures. I also generate Pass Throgh Queries and store the T-SQL in Access code using case statements and appends to alter the request.2. With Excel VBA and Excel Object Model, dynamically create each Excel Workbook. Start with Excel, add a worksheet, and all the gory details. My Workbooks may have a daily download of Wall Street prices for a commodity per location, basis, transport and other current data. The associated worksheets have actual formulas in the cells. Brokers want to conduct what-if situations on futures and look at the associated spreads for example.This is not something that people who just copyfromrecordset need to do. The Excel workboods can be dynamically created from the options a user checks or enters on the user interface form.3. A launcher on the users desktop checks for the last date of the Access MDE application. When I update the application, each user's PC checks for latest version and downloads it. Users are in the local office and in many remote sites.The interface on step 1 uses SQL Server to maintain each users security, views, and favorites.Basically, I have a development version. Compile it. Post it on the server and all users automatically get the latest version.Since 99% of the processing takes place on SQL Server, even a large complex 16 worksheet (full of formulas and current data) will take between 1 to 25 seconds to build from scratch. And, most of these offer the user a dozen custom choices such as: Customer name, from-to dates, overview / details, graphs, ...Just to give you an idea, the application code and forms (no data) is about 130 MB with about 150 user form interfaces and 200 very active users. Once in a while, simple reports use Access Reports. Excel has the advantage of having the business rules and formulas included so everyone can agree on where the numbers came from.Depending on the complexity and size of the data, make decisions to just copy and filter a SQL Veiw, Stored Procedure, or Pass Through Query using T-SQL. I add about two or more major features per week to update the application version that gives my traders the edge. This is a one development station and MS office on each users PC solution. The office manager clicks a button once in the morning, and it creates stand-alone Excel reports that are archived on a shared network. These can be retrieved via FTP or e-mailed. It would be easy to automat that on a timer.This solution is not the solution for everyone. I also use .NET and Reporting Services when they are the right tool.http://www.accessui.com/Home/tabid/36/Default.aspxTake a look at this web site for example (if just for the free tools).Access can work extremely efficiently with SQL Server, let SQL provide all the horsepower, and only send the results over the network.All this said:I would run a job on Excel (Excel VBA Object Model Code) on a server next to SQL server and have it create the Excel workbooks in a shared folder.We post a lot of SQL on our Excel users forum along with the Excel Object Model VBA code. (and VB and .Net... its all good)Wed, 19 Aug 2009 16:55:49 GMTMile Higher Than Sea Levelhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxUsers are able to choose for automated refreshing of the data when opening their excel-spreadsheet or by command.Frank, some of my users are clients, for whom auto-refresh won't work, as they are outside the corporate network. I need to refresh it for them, preferably on a scheduled basis. Other people don't want auto-refresh enabled, as they want the data to remain static and only be refreshed on, say, a weekly basis.Wed, 19 Aug 2009 14:15:00 GMTcmcchttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxPiers, the theory is easier than the implementation.I create many workbooks for users throughout my company. The common request is that they be refreshed on a regular basis, usually daily. The obvious option is to have the sheets refresh from the database when they are opened. However, some users are in remote offices, are on a different network, or are clients picking up files via FTP. In all of these cases the data connection fails. So, I am looking for some way to open the workbooks and refresh the data connections.HOW? You make it sound so easy!I have been repeatedly advised not to have SQL Server run Office automation. Besides, none of our SQL Servers have Office installed on them, nor will it ever be installed.I am considering setting something up on my workstation to run jobs overnight. Given that I'm a DBA, T-SQL is my programming language of choice, so Steve's SPs and examples speak to me.Do you know another way to do this?Wed, 19 Aug 2009 14:10:29 GMTcmcchttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxLinking Excel to SQL (and you can also store a query as part of the process) is one way to return a recordset. It is a popular way to provide raw data to users.However, it is sometimes necessary to allow the user to select several options that could include: date range, sort order, group-by, multiple group by (i.e. states, then cities, then product), exclusions, and so on.The interface often polls the database so that a list box will only include for example States available in the recordset for a selected date.By using T-SQL, ADO, or ODBC, the client can build a dynamic statement against a SQL view or even a stored procedure. In some cases, a pass-through query can be sent to SQL, then executed so that all the processing takes place on SQL Server. For example: Using the union of four transformation views against SQL data should be done at the server and only return the resulting records across the network.Likewise, SQL Server using T-SQL can create the Excel automation from the server side. You could have SQL create a Excel product for each client based on their role and service area.Often, these are decision support products used by a limited number of executives or decision makers.Fri, 08 May 2009 20:19:36 GMTMile Higher Than Sea Levelhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxHi I am new but I use lot's of Excel sheets to diplay data from our sql database.I create a view then use an ODBC call to link the sheet.I set the properties of the query in excel to keep the properties the user has set.The user then just has to open and right click and update Or have I missed the point of the orignal question?Fri, 08 May 2009 09:44:20 GMTHeather McPhersonhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxRob,Thanks for sharing with us. I use not only Excel but Word and MS ACCESS automation too. I will get this book.Fri, 13 Mar 2009 11:16:58 GMTYelena Varshalhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxMicrosoft Press:Programming Microsoft Office Business ApplicationsISBN-13: 978-0-7356-2536-5 2008I am reading and [u]really enjoying [/u]this book I won at the Denver (Rocky Mountain) Microsoft SQL Server Users Group. B.T.W. Thanks Microsoft for providing this for our free drawing.It provides some needed examples and observations for coders.For example: on page 96 it has code examples of - Performing Calculations Server-Side in Word Using the Excel Calculation EngineWhile many of the small clients I support (50 employees or under) don't have some of the applications (Share Point, Visual Studio...) there is a movement to SQL Server and moving to MS Office applications. For $35.00 retail - code available - I will give it a thumbs up.It offers multiple options depending on your clients resources, or maybe even a way to justify obtaining the resources.Fri, 13 Mar 2009 11:10:56 GMTMile Higher Than Sea Levelhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxI think would be better to handle excel automation from VB.net, C# or a CLR stored procedure rather than use OA procedures.Thu, 12 Mar 2009 13:29:10 GMTJose A Contrerashttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxWayne,Please read about data driven subscriptions for Reporting Services. It may help you.Here is a link to the 3 -page 30 min tutorial:http://technet.microsoft.com/en-us/library/ms169673.aspxTutorial: Creating a Data-Driven SubscriptionIn the tutorial it shows you how to create a database with the list of the report subscribers and then create a number of your business reports one per subscriber and output them on the file share..I guess that if you then have to move the reports to the different folders you probably can have a script for that.Mon, 09 Mar 2009 14:31:48 GMTYelena Varshalhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxI just opened my e-mail and grinned!While my schedul won't allow me to respond for a couple of days...This looks like some fun!I will plan on getting back to this.Mon, 09 Mar 2009 14:10:46 GMTMile Higher Than Sea Levelhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxThe feedback on this article has been very interesting. And good enough that I've been looking at other ways to accomplish what I'm using the Excel Automation for.So, let me lay out what's going on, and hopefully I'll get suggestions for how to handle this.This is happening for a small business. They deal with the reselling of international magazines in the US (and the handling of subscribers for the magazine). Their SQL Server is 2008 (recently upgraded from 2000). The excel spreadsheets are reports for each publisher, generated when each issue of their magazine is released. The spreadsheets are uploaded to the company web site for the publishers to log in and download.When most of these spreadsheets are generated, new data is appended to the existing spreadsheet.Since the company deals with numerous publishers, obtaining a separate license for each publisher to access SQL would be cost-prohibitive. (As would be upgrading to unlimited licenses.) This is the primary reason for exporting the reports as spreadsheets.I've looked into inserting into the spreadsheet thru the OpenRowset function. This requires two things:1. You can't use variables for the parameters, and each publisher has a different path to the file.2. You must know the name of the worksheet, and some are not the default (or even the same between publishers).The Excel spreadsheets are not being used for their functions. In fact, the most complicated thing going on is to do some simple math (add, get percentages) on some columns (which can easily be done at the sql level to avoid this), and to format some of the cells (ie. the percentage columns, date columns, etc.).So, I need to be able to:1. append data to an existing spreadsheet.2. must be able to work on spreadsheets based on a variable to the path where they are located, and to the name of the file itself.3. must be able to work on the first worksheet of that spreadsheet, regardless of it's name.4. some of the spreadsheets export just one new row. Others do multiple. On at least one, the first row is raw numbers, and the second row is percentages dealing with that number. On another, the first row contains a variable title (which changes for each issue release), and the second contains data for that title.5. some of the spreadsheets need to have a separate worksheet for some of the data. This is typically done be year, with the most recent year being the first worksheet. (Currently, new worksheets are manually created and arranged manually by the staff as necessary.)I don't think SSIS or SSRS will be able to handle all of the above requirements. Please correct me if I'm wrong.I could load all of the data from each type of spreadsheet into a history table, and then just add data to these tables, and from there completely rewrite the spreadsheets. This would take care of the need to append to existing spreadsheets, however the other 4 issues are still present.So, what other options are there?Looking forward to reading these...WayneSun, 08 Mar 2009 14:00:39 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxThank you all for interesting comments in this thread.We do have different experiences and what works for oneof us may be doesn't work for another of us.I have been working with Excel and SQL-server for many yearsand never really have had problems with automation. I have to goback to Windows 95 and memory leakage problems. But I have onebasic rule. A common user can never open a file on the server or run a (Excel)process on the server. An Excelfile is produced on the serverand delivered to the use (eg coppied to another server) or the usercan use a makro in his template Excelfile (pW protected) and withremote OLE DB get data from the server.:)I use VBA or still old VB for this. I have tryed to use VB.net and officetoolkits but I am not happy with that and is now getting to old tolearn the stuff. Also I have problems to orient myselt in the new Office2007 user interface.IfB.T.W. I read somewhere on a blog that Microsoft Accountants internally use custom Excel reports generated with VBA (0r SQL T-SQL) at the server level.is true I am in good company! //GostaSun, 08 Mar 2009 12:57:44 GMTGosta Munktellhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxThanks for your comments. Any solution besides installing Excel on my SQL server would be great.In this specific situation, the daily subscriptions that I bring in from multiple web sites and upload to my SQL Server databaes using around 500 lines of code save the little company more each month than my monthly contracting price alone. They could get it in XML instead of Excel, but at a much bigger subscription cost.Users also have access to automated multi-tab Excel sheets that include protection, network user security, formulas, pull-down list and other features. They evaluate marketing contracts, approve them, then activate the positions to be brought back into SQL Server.From a business perspective, it has given a small company a competitive edge against much larger companies. It allows me to change the business rules as the economy changes sometimes on a weekly basis.Is this the solution for all my past, current, and future clients? No.Is it a solution for everyone else's customers? No.Is it something I do for the entire business support? No.I don't use Access as the actual database, SQL Server is my database. Access is the programming interface. It is highly versioned with automated distribution to clients. Pass-Through queries are used so SQL Server manages the processing. It is not perfect. It appears to be very cost effective for this situation. I am very interested in using other options than prevent installing Excel on my SQL server. All comments and ideas are welcome.The good, the bad, and the ugly suggestions will help in planning maintenance and support.Or, help consider other cost-effective solutions.Sun, 08 Mar 2009 12:49:14 GMTMile Higher Than Sea Levelhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxI'm with the naysayers. This comes from several years of experience dealing with Excel automation. Not to say that this technique doesn't work for certain situations, but here are the flaws as I see them.- Installing Excel on a server that has SQL Server- Quirks dealing with Excel COM objects and programming.- Assuming that the original spreadsheet will stay consistentI think the best technique is to deal with Excel on the client or with a product/service that is designed to do this task. As a side note: I have clients and coworkers that commonly submit data to me in Excel format. Excel is not a database or a desirable data source! Neither is Access! :crazy:Sun, 08 Mar 2009 11:59:50 GMTchrisn-585491http://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxThanks Sam!It is great to have all the options!This will probably work for some of my data only Excel creations.And, I can get somewhat the same resusts in MS Reporting ServicesTypically, I create Excel Workbooks that evaluate the data. The data determines the formula that I want to include in my Excel Workbook cell on an individual basis.The number of Excel worksheets and the custom data in each are sorted out and organized at the server. Those choices may for example create a custom pull-down list box with values for a financial futures analysist based on today's prices and the prices of owned futures contracts that affect the enterprise risk. These are somewhat large reports that allow "what - if" from a Excel user interface.Using Passthrough Queries and Stored procedures, these individual Excel reports were taking about 5 to 8 seconds on an unloaded network. That was ok until all the other users saw the wonderful applications and how much time savings it created.You have given me another valuable option to explore. :) B.T.W. I read somewhere on a blog that Microsoft Accountants internally use custom Excel reports generated with VBA (0r SQL T-SQL) at the server level.I would love to be a fly on the wall behind one of those programmers!TAgain Thanks!Sun, 15 Feb 2009 11:03:15 GMTMile Higher Than Sea Levelhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxFor this type of problem, I like using excel web queries, and .iqy files.An excel web query works by linking a section of the spreadsheet to the contents of a web page (NOTE: this does not have to be an html file). And the data can be updated whenever the user wants to. I won't go in to detail here, but search on web for how to create iqy files. Parameters can be added as well, which can come from either user interaction/ fixed values/ cell references.Excel can be used to do the style / layout/ data type formatting, and sql can do the bit which it is best at, returning/sorting data.The person who asked you for the report never has to again, as they can update it themselves (from excel), removing the need for scheduling and file storage. And they can add any pretty colors/etc themselves.The implementation of such a web service is simple. Receive a web request, call a stored proc with any necessary arguments, and then return the resulting dataset in to a tab separated format (Excel will take this response, and place it in to the spreadsheet). The parameters and name of the stored proc could in theory, be inferred by the parameters of the web query, such that you only ever need to create the web method once.http://reportingserver/tabsepdata.asmx/get?report=[b]XYZ[/b]&arg[b]FOO[/b]=[b]BAR[/b]&arg[b]JOHN[/b]=[b]DOE[/b]SamSat, 14 Feb 2009 10:41:49 GMTsam.walkerhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxAny ideas about installing Office Object Libraries with out installing Office 2003?I had a dream that the Office Interop would just add the Excel .DLL onto my Windows 2003 server registry to provide the object librarys for my T-SQL automation to reference.Not haveing to install Excel 2003 on my server sounded like a great solution.The ReadMe files are corrrect, when trying to install an error message asked me to install Office 2003 first. [font="Arial Black"]Previous Post in this thread: AFAIK there were also the Office PIA's, that made it possible to do this kind of stuff without (fully) installing Excel on the server.Here they are: Office 2003 Redistributable Primary Interop Assemblies.[/font][b]Read Me for Office 2003 Redistributable Primary Interop Assemblies [/b] Minimum system requirements to install the Office 2003 Primary Interop Assemblies are:[b]A Microsoft Office System 2003 product[/b].NET Framework 1.1 or higherB.T.W Installing Office 2003 Service Pack 1 after installing the Office 2003 Primary Interop Assemblies may require a repair of the Office 2003 Primary Interop AssembliesMSDN Referencehttp://msdn.microsoft.com/en-us/library/15s06t57(VS.80).aspxThu, 15 Jan 2009 10:52:33 GMTMile Higher Than Sea Levelhttp://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxYou can find the equivalent of EXCEL functions which can be called in T-SQL at www.xleratordb.com.Tue, 13 Jan 2009 12:03:13 GMTcharles.flock-906611http://www.sqlservercentral.com/Forums/Topic614268-1273-1.aspxYou can call Excel remotely from ASP and ASP.NET pagesAlso we have good experience with MS ACCESS / VBA and linked tables to SQL Server. This is really good for disconnected clients. You can have local Access tables and when the client is on the network these local tables can be synchronized with SQL Server linked tables from MS ACCESS.For you to be able to use remote Excel or Access procedures or functions you have to install Excel or Access on the remote server. There is a lot of articles on the topic but the main idea is: Dim exapp As Excel.Application Dim workbook1 As Excel.Workbook Dim worksheet1 As Excel.Worksheet...... exapp = New Excel.Application workbook1 = exapp.Workbooks.Open(FilePath) then use properties and methods of the workbooks and worksheetsTue, 13 Jan 2009 10:53:43 GMTYelena Varshal