06 February 2007

SQL Server Excel Workbench

The need to produce Excel reports from SQL Server is very common. Here, Robyn Page and Phil Factor present practical techniques for creating and manipulating Excel spreadsheets from SQL Server, using linked servers and T-SQL. The pièce de résistance is a stored procedure that uses OLE Automation to allow you full control over the formatting of your Excel report, and the ability to include sums, ranges, pivot tables and so on.

The C.R.U.D. of Excel

Phil and I have teamed up on this workbench, which demonstrates how to create, read, update and delete information in Excel using T-SQL, from SQL Server. As always, the workbench is structured so that it can be pasted into Query Analyser and SSMS, and the individual examples executed – you can download the .sql from the “Code Download” link at the bottom of the article, load it up and start experimenting!

Contents

We start by showing you how to create an Excel Spreadsheet from SQL Server in TSQL(Transact SQL), create a worksheet, attach to it as a linked server, write to it, read from it, update it as if it was an ordinary SQL Server Database table, and then synchronise the data in the worksheet with SQL Server. We also illustrate the use of OPENQUERY, OPENDATASOURCE and OPENROWSET.

To create the Excel spreadsheet, we show how to attach to an ADODB source from SQL Server and execute SQL against that source. We then show you an alternative ‘quick cheat’ way (using sp_makewebtask) to create and populate an Excel spreadsheet from Transact SQL.

If you need more control over the Excel Spreadsheet that you are creating, we then show you how to do it via OLE automation. This will enable you to do anything you can do via keystrokes, and allow you to generate full Excel reports with pivot tables and Graphs.

Using this technique, you should be able to populate the data, or place data in particular calls or ranges. You can even do ‘macro substitutions’.

A word of caution before you start. If you have your security wide open, it is not just you who would be able to write out data as a spreadsheet. An intruder would be able to do it with that list of passwords or credit-card numbers. In a production system, this sort of operation needs to be properly ring-fenced. We tend to create a job queue and have a special user, with the appropriate permissions, on the Task Scheduler, to do anything that involves OLE automation or xp_CMDShell. Security precautions can get quite complex, but they are outside the scope of the article.

Some of what we illustrate can be done using DTS or SSIS. Unfortunately, these are outside the scope of this article. In fact, transferring data between Excel and SQL Server can be done in a surprising variety of ways and it would be fun one day to try to list them all.

First we need some simple test data:

And so on. (The full import file is in the ZIP, as is the Excel file!).

Create the table and then execute the contents of CambridgePubs.SQL.

Creating Excel spreadsheets via ADODB

First, we need to create the spreadsheet with the correct headings (PubName, Address, PostCode).

There are two possible ways one might do this. The most obvious way is using the CREATE statement to create the worksheet and define the columns, but there seems to be no way of doing this by linking the Excel file, unless the Excel file already exists. We need a utility stored procedure to get at ADODB in order to create databases and execute DDL and SQL against it.

Now we have it, it is easy.

The Excel file will have been created on the Database server of the database you currently have a connection to.

We could now insert data into the spreadsheet, if we wanted:

Manipulating Excel data via a linked server

We can now link to the created Excel file as follows.

To drop the link, we do this!

So now we can insert our data into the Excel spreadsheet:

Synchronizing the Spreadsheet with SQL Server tables

As we are directly manipulating the Excel data in the worksheet as if it was a table we can do JOINs.

What about synchronising the table after editing the Excel spreadsheet?

To try this out, you’ll need to DELETE, ALTER and INSERT a few rows from the Excel spreadsheet, remembering to close it after you’ve done it.

Firstly, we’ll delete any rows from ##CambridgePubs that do not exist in the Excel spreadsheet.

Then we insert into ##CambridgePubs any rows in the spreadsheet that don’t exist in ##CambridgePubs.

All done (reverse syncronisation would be similar).

Manipulating Excel data using OPENDATASOURCE and OPENROWSET

If you don’t want to do the linking, you can also read the data like this:

You can read and write to the Excel sheet using OpenRowSet, if the mood takes you.

Creating Excel Spreadsheets using sp_makewebtask

Instead of creating the Excel spreadsheet with OLEDB One can use the sp_makewebtask.

Users must have SELECT permissions to run a specified query and CREATE PROCEDURE permissions in the database in which the query will run. The SQL Server account must have permissions to write the generated HTML document to the specified location. Only members of the sysadmin server role can impersonate other users.

This is fine for distributing information from databases but no good if you subsequently want to open it via ODBC.

OLE Automation

So far, so good. However, we really want rather more than this. When we create an Excel file for a business report, we want the data and we also want nice formatting, defined ranges, sums, calculated fields and pretty graphs. If we do financial reporting, we want a pivot table and so on in order to allow a degree of data mining by the recipient. A different approach is required.

We can, of course, use Excel to extract the data from the database. However, in this example, we’ll create a spreadsheet, write the data into it, fit the columns nicely and define a range around the data.

Now we can create our pubs spreadsheet, and can do it from any of our servers.

Or if you are using integrated security!

Although this is a very handy stored procedure, you’ll probably need to modify and add to it for particular purposes.

We use the DMO method because we like to dump build data into Excel spreadsheets e.g. users, logins, Job Histories. However, an ADODB version is very simple to do and can be made much faster for reads and writes.

We have just inserted values, but you can insert formulae and formatting numberformat) and create or change borders. You can, in fact, manipulate the spreadsheet in any way you like. When we do this, we record macros in Excel and then convert these macros to TSQL! Using the above example, it should be simple.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

Downloads

This post has been viewed 195145 times – thanks for reading.

Tags: , , , , , , , , , , ,

  • Rate
    [Total: 196    Average: 4.2/5]
  • Share


Robyn Page has worked as a consultant with Enformatica and USP Networks with a special interest in the provision of broadcast services over IP intranets. She was also a well known actress, being most famous for her role as Katie Williams, barmaid and man-eater in the Television Series Family Affairs. She is currently having a career break to raise a young family.

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 20 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

View all articles by Robyn Page and Phil Factor

  • Anonymous

    Robyn Page
    A perfect example for beauty with the brains…

  • Anonymous

    SQL Server Excel Workbench
    Hi

    It is nice

    Chears

    Lalith

  • Anonymous

    SQL Server Excel Workbench
    Geez, hectic stuff.

    I’m still learning T-SQL, so most of the things used in here are a bit over my head.

    My colleagues seem to think it’s great, so well done.
    I must agree on the previous comment posted by Anonymous on Robyn Page, extremely Intelligent and Beautiful – The perfect combination a man looks for in a woman…

  • Anonymous

    SQL Server Excel Workbench
    Congratulations!

    I am very pleased to have had the opportunity in reading this article. It is a very valuable collection of code integrating T-Sql with excel manipulating. I am also surprised as on how it got to me precisely at a time when I have to transfer data from SQL Server into Excel. Besides all of this I must refer the sharing of such valuable code on behalf of Robin Page and Phil factor. Thanks a lot and keep up the intelligent work.

  • Anonymous

    Where does Excel Need to Be Installed?
    Nice work.

    I’m no Excel expert, but where does Excel need to be installed for this?

    We don’t have it installed on the same box as the SQL Server (and we probably can’t get approved to get it), although I have Office 2000 with Excel installed on my client machine.

  • cje

    SQL Workbench
    Excellent.

  • colinsobers@bellsouth.net

    OpenDataSource “Non-Linked”
    Man this is sooooo timely! Thanks a bunch Robyn. Keep up the good work. I’m going to be using these examples to bring in a whole bunch of spreadsheets in to our SQL Server and to write out to Excel also. Once again, very timely!

    Peace and blessings
    Colin

  • colinsobers@bellsouth.net

    OpenDataSource “Non-Linked”
    Man this is sooooo timely! Thanks a bunch Robyn. Keep up the good work. I’m going to be using these examples to bring in a whole bunch of spreadsheets in to our SQL Server and to write out to Excel also. Once again, very timely!

    Peace and blessings
    Colin

  • Anonymous

    Robyn
    Best looking tech person I have ever seen
    would love to have her as a consultant

  • Robyn Page

    Re: Where does Excel Need to Be Installed?
    Until you do the full automation routine, you do not need to have Excel installed, as the Jet OLEDB driver is able to create, read to, or write from, excel files. When you do the automation, it will need to be on the server that has the database server on it, and the User with which SQL Server is operating must have the rights to access Excel.

    For anyone who wants to extend what our Excel Automation routine does, there is a good worked example using C# at http://support.microsoft.com/kb/302084
    which provides all the help you’d need to get started.

  • Anonymous

    I agree with all the comments above
    Also, Robyn, will you marry me? 😉

  • Phil Factor

    Re: I agree with all the comments above
    Bah! Nobody ever says that to me after reading my articles. 🙂

  • Anonymous

    well done
    nice article, never would have guessed this is possible using TSQL
    what I really like to know is, what keeps you Robyn busier, certainly the above article is not at the beginner level, and being able to come up with that means a serious amount of time & eduction invested in the past, is it the acting or the programming career that you’re after ?

    Hakan from Canada

  • Anonymous

    Reading Excel Sheet – without sheet name?
    Hi –
    Your information is great, but I have one dilemma: I sometimes want to read the FIRST sheet of a Workbook, but users often name that sheet differently (e.g. Sheet1, MySheet, Orders, etc.) and I don’t know in advance what they’re going to call it. Using the OPENROWSET method,
    [SELECT * FROM OPENROWSET(‘Microsoft.Jet… ]
    how can I read the first sheet of the workbook, regardless of its name? And if not by this method, what is a simple way to do it (if one exists)?
    Thank you!

  • Anonymous

    Fantastic
    Good work done by Robyn and excellent.
    She looks beautiful.

  • Robyn Page

    Re: Reading Excel Sheet – without sheet name?
    It is possible to specify the first sheet through automation, but I cannot find a way of doing it through a link or ADODB. The ‘table’ has to be named.
    The best bet might be an automation procedure that accessed the first worksheet and then, if necessary, renamed it to a standard name of your choosing? Then it is all easy.
    Has anyone else got an idea?

  • Anonymous

    SQL Server Excel Workbench
    Brilliant. I had done this kind of work previously using DTS but this approach makes it more portable and more reusable. I may even convert my old routines to use this method. If either of you (or both) are considering a DBA job in Canada let me know. Seriously, this caliber of work is worth a job offer. (In your case Phil I’d need your real name … eventually)

  • fhanlon

    SQL Server Excel Workbench
    My last post came out as Anonymous which I am not (always)

  • Phil Factor

    Re: SQL Server Excel Workbench-Job offer
    We’re both pleased you liked the article. We don’t decry the use of DTS for doing Excel integration work-we use DTS a fair amount, but sometimes one really needs to do more than DTS was designed for.

    We’re both very flattered by the job offer!

  • Anonymous

    Re: SQL Server Excel Workbench
    Ma’m Robyn,
    you made mention about ‘an ADODB
    version is very simple to do and can be made much faster for reads and writes’. Do you mean an excel workbench adodb version? If so, please write a sql server excel workbench using an ADODB. Thank you.

  • Robyn Page

    An ADODB version
    Yes. Phil and I adapted Phil’s existing DMO version simply because he was using it to save configuration stuff from a whole lot of Servers. If you use ADODB, you can use the ‘Range(MyRange).CopyFromRecordset rs’ command in order to fill the cells, which is much faster than using the iterative approach we used.

  • Anonymous

    SQL Server Excel Workbench
    Hi,

    Thanks for your artical on this subject

    I want to know more about scripting for SQL Server 2000.

  • Anonymous

    SQL Server Excel Workbench
    Thanks, some great ideas.

    But spExecute_ADODB_SQL fails if you loop more than 64 times.

    It needs a needs an extra step:
    exec @hr = sp_OAMethod @objConnection, ‘Close’

  • Anonymous

    SQL Server Excel Workbench
    Thanks, some great ideas.

    But spExecute_ADODB_SQL fails if you loop more than 64 times.

    It needs a needs an extra step:
    exec @hr = sp_OAMethod @objConnection, ‘Close’

  • Phil Factor

    Re: spExecute_ADODB_SQL fails if you loop more than 64 times.
    Oops! Quite right. Mea Culpa! Thanks very much for the bugfix. I must confess that neither Robyn or I looped it 64 times!

  • Anonymous

    SQL Server Excel Workbench
    Thanks, Robyn & Phil, for an article that was both informative and useful, especially to people like me who were not trained as DBAs but drifted into using SQL as part of their job.

    I would have posted this earlier but I was too busy adapting bits of your C.R.U.D for my own work.

  • Anonymous

    Really cool script
    Thank you for this magnificent script.

    I used the script for my reports and discovered, that if you change some small thing in the script you can actually reuse 1 file for many worksheets.

    This is the code that I Used to be able to append or create a new workbook using your code startin with a few lines of your code first.

    –so now we have the queryresults. We start up Excel
    IF @hr=0
    SELECT @strErrorMessage=’Creating the Excel Application, on ‘
    +@SourceServer, @objErrorObject=@objExcel
    IF @hr=0
    EXEC @hr=sp_OACreate ‘Excel.Application’, @objExcel OUT
    IF @hr=0 SELECT @strErrorMessage=’Getting the WorkBooks object ‘
    IF @hr=0
    EXEC @hr=sp_OAGetProperty @objExcel, ‘WorkBooks’,
    @objWorkBooks OUT

    IF @hr=0
    EXEC @hr = sp_OASetProperty @objExcel, ‘DisplayAlerts’, ‘FALSE’
    –create a workbook or append a workbook
    DECLARE @status int
    EXEC master..xp_fileexist @filename, @Status OUTPUT

    IF @status = 1
    BEGIN
    SELECT @strErrorMessage=’Opening a workbook ‘, @objErrorObject=@objWorkBooks
    IF @hr=0
    SELECT @strErrorMessage=’Opening the workbook as “‘+@filename+'”‘, @objErrorObject=@objRange, @command = ‘Open(“‘ + @filename + ‘”)’
    IF @hr=0
    EXEC @hr=sp_OAGetProperty @objWorkBooks, @command, @objWorkBook OUT
    END
    ELSE
    BEGIN
    IF @hr=0
    SELECT @strErrorMessage=’Adding a workbook ‘, @objErrorObject=@objWorkBooks
    IF @hr=0
    EXEC @hr=sp_OAGetProperty @objWorkBooks, ‘Add’, @objWorkBook OUT
    END

    Hope it helps someone.

    Regards, Denis

  • Phil Factor

    Re: Really cool script.
    Yeah. That is great, Denis. Thanks for the contribution. Much appreciated.

  • Anonymous

    Great scripts
    Hi Robyn and Phil,
    Some great scripts, nice to see several different solutions presented to a particular challenge.

    One question about the ADO script, you replace in @DDL the stub %worksheet with the worksheet name passed in the @worksheet parameter, but please can you give an example of how it is used?

    Cheers

    David

  • Phil Factor

    ge: Great scripts
    There are three examples of the use of the stored procedure just after the source of the stored procedure. The DDL is the SQL Data Definition Language command that you want to send to the data source. The DDL commands are defined by ODBC. These ODMC sources must be able to respond to a certain number of these commands, depending whether the ODBC driver is Minimun, Core or Extended.

    I hope that helps!

    see http://www.jsoftware.com/help/user/sql_statements.htm

  • Anonymous

    Dumb question
    Hi,
    Really cool stuff. One thing I seem to be missing though; how do you fill the spreadsheet with data from sql server as opposed to explicitly indicating the values you want to insert into the spreadsheet?

    My apologies for the goofy question. It’s probably in there somewhere and I missed it.

    Many thanks!

  • Anonymous

    next step – array functions
    Another Excellent Article! Thanks for the great detail Robyn and Phil!

    Using similiar steps as in your article, I am creating an Excel worksheet with two sheets from SQL; the second sheet is a summary of the data in the first sheet.
    In my testing I create the summary sheet with SQL as well and that works of course.
    For formating (ease of viewing) however I would like to use Excel functions in the second sheet.
    Examples:
    {=SUMPRODUCT((Sheet1!J2:J40)*(Sheet1!$I$2:$I$40=”PartA”))}
    or
    {=SUM(VALUE(Sheet1!J2:J40))}
    or
    {=COUNT(VALUE(Sheet1!J2:J40))}

    The “Rub” is that to enter an array function into an Excel Cell you must type it without the curly brackets and hit CTRL + SHIFT + ENTER.

    I cannot recreate this in SQL to insert the Array Function into the Cell…

    Any ideas?

    I thought about recording a Macro and having that run when the Worksheet was opened; how do I insert a Macro into a new sheet from SQL?
    At the end of my SQL Script I also email the Excel as an attachment to the User – they need to be able to open the Worksheet with few if not zero steps… (nothing new there…)

    Thanks!
    Todd

  • Anonymous

    64-bit server issue for ADODB
    Great article! Finally I’ve found other who are trying to solve the same problems that I am.

    One issue I’ve encountered though. The Microsoft.Jet.OLEDB.4.0 driver does not exist for 64-bit Windows Servers AND Microsoft apparently has no plans to create one. Very little info from Microsoft on this topic (http://connect.microsoft.com/SQLServer/feedback/Vote.aspx?FeedbackID=125117).

  • Phil Factor

    re: Next Step Array functions
    You can insert formulas into Excel just as easily as you do values from TSQL. I’m sorry if we didn’t quite make that clear. you just assign your formula to the .FORMULA attribute of the cell or cell range. You’ll see in the example in the article where we assign to the .VALUE of a cell to insert the resultset values into excel. You just do the same, but assign to the .FORMULA

    Good luck and let us know how you get on!

  • Phil Factor

    re 64-bit Server Issue for ADODB
    This is an ‘interesting’ problem. The way I get around this is to use a linked SQL Server Express ‘Grunt’ server to do this sort of job, on a 32-bit machine. Works fine: no hassle. But it aint the perfect solution.

    I’ve heard that one can get ODBC to work by running C:WINDOWSSysWOW64odbcad32.exe but I’ve never tried it.

  • Anonymous

    Script generating an error…
    Hi Robyn,
    Hi Phil,
    Thanks for the great work you’ve been doing. I think this would be a very useful script for me to have. However having copied it from the site (in steps of course)I find that I’m getting the error… “Msg 50000, Level 16, State 1, Procedure spDMOExportToExcel, Line 249
    Error whilst Creating the Excel Application, on MyServer, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]”
    So I know it has to do with the excel application (which exists) but not what to do about it. Any light for the darkness of this newbie?
    Thanks

  • Phil Factor

    Re: Script generating an error…
    Copy the script from the box at the top of the article. it is more reliable. Click on where CODE DOWNLOAD is written. The error means that there is a parameter missing in the call to the sp_oaMethod is being called to create the OLE object. As far as I’m aware, the code itself is OK, so I suspect something got lost when you pasted it in. Let us know if that doesn’t solve the problem

    We’re glad you like the article. I use the code a lot myself.

  • Anonymous

    Re: Re: Script generating an error…
    Hi Phil (and Robyn), thanks for the quick response. I did as suggested but I still get that same error message. I thought it might be related to the surface area configuration so I ensured that OLE automation was enabled as well as xp_cmdshell. However that hasn’t made a difference. Any other ideas? I’ve already got three assignments that could use this approach once I get it working. Thanks again…

  • Moses

    error 0x80004005
    Hi Robyn and Phil,
    as everybody, i love your solution. It was going to help me a lot (36 servers on which some results are to be published).
    I tested on my ntb – all went fine.
    But … On the first server I went to test, I can’t get after EXEC @hr=sp_OAMethod @objconnection, ‘Open’ – I keep getting that weird error code, with no additional info …Did you ever get this one ?
    Thanks a lot !

  • Anonymous

    Re: Re: Script generating an error…
    Hi Phil (and Robyn), thanks for the quick response. I did as suggested but I still get that same error message. I thought it might be related to the surface area configuration so I ensured that OLE automation was enabled as well as xp_cmdshell. However that hasn’t made a difference. Any other ideas? I’ve already got three assignments that could use this approach once I get it working. Thanks again…

  • Anonymous

    Deleting the spreadsheet rows
    Great work on this. Extremely helpful.

    I am able to populate the spreadsheet with data selected from my sql db using an “insert into”, and I can update specified rows.

    But I cannot determine how to delete all rows. Maybe I missed something in your detail. I tried the following script

    delete OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,

    ‘Data Source=”\FS1DriveFMisc-AdminJobInfoNew.xls”
    ;User ID=Admin;Password=;Extended properties=Excel 8.0’)
    …Newtab$

    and get the following error

    Server: Msg 7345, Level 16, State 1, Line 1
    OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ could not delete from table ‘Newtab$’. There was a recoverable, provider-specific error, such as an RPC failure.
    [OLE/DB provider returned message: Deleting data in a linked table is not supported by this ISAM.]
    OLE DB error trace [OLE/DB Provider ‘Microsoft.Jet.OLEDB.4.0’ IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

    If I remove the …Newtab$, I get the following error

    Server: Msg 170, Level 15, State 1, Line 4
    Line 4: Incorrect syntax near ‘)’.

    Could you plz assist. Thanks

  • Anonymous

    Deleting the spreadsheet rows
    Great work on this. Extremely helpful.

    I am able to populate the spreadsheet with data selected from my sql db using an “insert into”, and I can update specified rows.

    But I cannot determine how to delete all rows. Maybe I missed something in your detail. I tried the following script

    delete OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,

    ‘Data Source=”\FS1DriveFMisc-AdminJobInfoNew.xls”
    ;User ID=Admin;Password=;Extended properties=Excel 8.0’)
    …Newtab$

    and get the following error

    Server: Msg 7345, Level 16, State 1, Line 1
    OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ could not delete from table ‘Newtab$’. There was a recoverable, provider-specific error, such as an RPC failure.
    [OLE/DB provider returned message: Deleting data in a linked table is not supported by this ISAM.]
    OLE DB error trace [OLE/DB Provider ‘Microsoft.Jet.OLEDB.4.0’ IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

    If I remove the …Newtab$, I get the following error

    Server: Msg 170, Level 15, State 1, Line 4
    Line 4: Incorrect syntax near ‘)’.

    Could you plz assist. Thanks

  • Phil Factor

    re:Deleting the spreadsheet rows
    Yes. Isn’t it strange. It looks from the error message that the OLEDB provider doesn’t even support deletion of rows. I often have to shrug and go back to doing a full automation of Excel when something simple doesn’t work!
    Try the link approach, but I suspect that will not work either

  • Steve Davis

    255 chars
    Good morning and thanks for the above. The issue that i am having with this and hope that you have found a workaround is for fields with more than 255 chars. Seems that utilizing Microsoft.Jet.OLEDB.4.0 , Excel 8.0 will throw an error “Msg 8152, Level 16, State 4, Line 1
    String or binary data would be truncated.” I have tried the tweak at http://support.microsoft.com/kb/189897/EN-US/ to no avail. any help would be greatlt appreciated!
    steve@nyctechsupport.com

  • Anonymous

    Excel never ends
    Parfait! Exactly the code I was looking for.
    I just have a trouble when calling the procedure from a loop using a cursor with more than 41 calls. Excel applications never end and my server isn’t able to create new ones.
    Any ideas?

  • Franck

    Excel never ends 2
    Try this code, after having updated Postcode column :
    update ##CambridgePubs set Postcode=replace(Postcode,’ ‘,’_’)

    DECLARE @four varchar(7)
    DECLARE @file varchar(100)
    DECLARE @query varchar(8000)
    DECLARE @collist varchar(500)
    DECLARE @quote varchar(1)

    set @quote=””

    DECLARE FOURNISSEUR CURSOR FOR
    select distinct Postcode from ##CambridgePubs

    OPEN FOURNISSEUR
    FETCH NEXT
    FROM FOURNISSEUR
    INTO @four

    WHILE @@FETCH_STATUS = 0 BEGIN
    SET @query = ‘select * from ##CambridgePubs where Postcode =”’ + @four + ””
    SET @file = ‘C:tempManquants’ +@four + ‘.xls’
    EXEC spDMOExportToExcel2
    @SourceServer=NULL,
    @QueryText = @query,
    @filename = @file,
    @WorksheetName=’A’,
    @RangeName =’A’
    FETCH NEXT
    FROM FOURNISSEUR
    INTO @four
    END
    CLOSE FOURNISSEUR
    DEALLOCATE FOURNISSEUR

  • Anonymous

    Looking for example code Accces VB module and sp_OACreate
    I want to see if I can create an instance of access application and a module with code to compact an access .mdb file from sql server so I can run this from a secure sever using msdb to schedule the task. Anyone know where I can find an example of sp_OACreate working with MS Access?

  • TimothAWiseman@gmail.com

    Reading the table name from Excel
    Based on the above article, I started using this script to read the table name for the first sheet of an excel file. I am still searching for a more efficient way and perhaps one that does not use OLE if anyone knows one.

    declare @sheetname varchar(1000)
    declare @objExcel int
    declare @objworkbook int
    declare @objworksheet int
    declare @error int

    EXEC @error=sp_OACreate ‘Excel.Application’, @objExcel OUT –This opens an instance of excel, it must be closed

    if error = 0
    exec @error = sp_OAMethod @objExcel, ‘Workbooks.open’, @objWorkbook OUT, @filename = @fullfile

    if error = 0
    exec @error =sp_OAMethod @objWorkbook, ‘Worksheets(1)’, @objWorkSheet OUT

    if error = 0
    exec @error = sp_OAGetProperty @objworksheet, ‘name’, @sheetname OUT

    if error = 0
    exec @error = sp_OAMethod @objWorkBook, ‘Close’

    exec sp_OAdestroy @objworkbook

    exec sp_OAdestroy @objWorkSheet

    EXEC @error = sp_OAMethod @objExcel, ‘quit’

    exec sp_OAdestroy @objExcel

  • lava kafle

    great article
    thanks for such a great innovative solution

  • Jason

    Defining Range And/Or Header Row when Selecting from Sheet
    If the spreadsheet had the column headings in the third row, is there anyway you can specify that when doing the select from the spreadsheet.

    I often get spreadsheets where someone had decided to use the first couple of rows for comments, and actually start the data on row 3 or 4.

  • Anonymous

    Formula in sql table
    Hi

    I want the formulas in SQL table to be rolled on to excel sheet and fetch the values again to a new table in sql.
    When I rolled the formula table to excel the formula was prefixed by ‘(quote) and shows as a formula only instead of getting the value for the linked sheets.

    Can u help me on this.

  • Anonymous

    Formula in sql table
    Sample data that was roled on to excel sheet. This is the 1st Row.

    Column1 =+’Main menu’!$D$2
    Column2 =+Date!$N$4
    Column3 =+’Daily-Sum’!D8
    Column4 =+’Daily-tab3′!F25
    Column5 0

  • Lisa

    Using ADODB method and dealing with NULLs in Excel worksheets
    Hi,

    I’m using the ADODB.Connection object method. Everything is working fine except all the NULL values “sink to the bottom” of the Excel worksheet. Is there anyway way to sort the Excel worksheet once all the data is already in there?

    If I try to run a sp_OACreate ‘Excel.Application’ object, do I need Excel to be installed on my SQL Server? I’m assuming yes, because I get an invalid class when I try to do it.

    Many thanks!
    Lisa

  • tatini

    How do we use a variable in place of Datasource in OPENDATASOURCE
    Nice article which gives what i have been looking for, being a novice in t-sql.
    I have used this script myself and i was wondering whether we can use a variable in place of Datasource part of OPENDATASOURCE

    I appreciate the good work. 🙂

  • tatini

    Variable in place of datasource
    I achieved the solution for my above post. I used dynamic sql to build the OPENDATASOURCE.
    Now, is there any way to read a specific sheet from the excel sheet, say first sheet or second sheet?

  • Anonymous

    Error: Cannot access ‘CambridgePubs.xls’
    Hi,

    Great article. This may be the solution I have been searching for but when I tried your example, I receive the above error upon trying to open the Excel file after it has been created.

    Any suggestions?

  • Anonymous

    Error: Cannot access ‘CambridgePubs.xls’
    Hi,

    Great article. This may be the solution I have been searching for but when I tried your example, I receive the above error upon trying to open the Excel file after it has been created.

    Any suggestions?

  • Lady

    Dynamically creating columnlist
    Hey girl
    great article, it has helped very much keep it up, i wanted to ask if there is a way to dynamically create the column heading list for the excel table by using a function to get the column list & datatype of any given table and use the return value as a parameter into this part

    spExecute_ADODB_SQL @DDL=’Create table myexceltable
    (return value of function)’,
    @DataSource =’C:CambridgePubs.xls’

    any ideas will be appreciated
    thanks

  • Phil Factor

    re: Dynamically creating columnlist
    spDMOExportToExcel already does this to create the column headings in the excel spreadsheet.

  • uriol17

    Problems with spExecute_ADODB_SQL
    Hi,

    I have a problem using spExecute_ADODB_SQL.
    I don´t have problems creating the excel file and insert into with values.
    But when I try to insert into with a select of a sql table like this

    spExecute_ADODB_SQL @DDL=’insert into Excel(code,Description)
    SELECT Code,Description FROM Table’,
    @DataSource =’c:excel.xls’

    the programs returns me an error because the database is open or the object is only read.

    I don´t understand whats happen because with value I can write in the excel file

    Sorry for my bad english and thanks for all.

  • Anonymous

    2 worksheets
    Hi,
    I am new to T-SQL.
    How can i have two sheets with different data in the same workbook created at ‘C:MyPubDatabase.xls’. I need to have different sets of records in that?

  • Robyn Page

    re: 2 worksheets
    You’ll notice that spDMOExportToExcel creates a new workbook and puts a worksheet in it.
    The whole purpose of these workbenches is to kick-start you into developing your own code. In this case, Phil and I just use the code to show you how to create a workbook and then a worksheet. This should be enough to get you started with your own routine. It should be pretty easy to modify the code to put a new worksheet in an existing workbook, but you won’t catch us doing it for you!
    Let us know how you get on.

  • Robyn Page

    Re: Problems with spExecute_ADODB_SQL
    This generally happens when you open your excel file in Excel to have a look at the data, and forget to close it. The routine will not open a spreadsheet if it is locked for use by another application.
    Another error one sometimes gets with this routine is when you use TSQL tather than ODBC/OLEDB SQL. Remember that you are communicating with an ODBC database, not SQL Server.

  • Dunc

    Error reading from excel
    I cannot read from the spreadsheet – whether I link the spreadsheet as a linked server or not I get the same error:

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

  • Terrie

    Alphabet question
    Hi 🙂 First off – amazing article!!! Thanks so much for publishing it.

    I am running into a problem with the section that sets the range to bold the headers. If there are 26 columns in my sheet, I get an error. Here’s a simplifed mock-up I did so I could look at the affects:

    Declare @Alphabet varchar(26), @currentColumn int

    SET @Alphabet=’ABCDEFGHIJKLMNOPQRSTUVWXYZ’
    SET @currentColumn = 1

    WHILE (@currentColumn <= 30)
    begin
    print’Range(“A1:’+substring(@alphabet,@currentColumn / 26,1)+substring(@alphabet,@currentColumn % 26,1)+’1’+'”).font.bold’

    SET @currentColumn = @currentColumn + 1
    end

    ————-This returns:—————
    Range(“A1:A1”).font.bold
    Range(“A1:B1”).font.bold
    Range(“A1:C1”).font.bold
    Range(“A1:D1”).font.bold
    Range(“A1:E1”).font.bold
    Range(“A1:F1”).font.bold
    Range(“A1:G1”).font.bold
    Range(“A1:H1”).font.bold
    Range(“A1:I1”).font.bold
    Range(“A1:J1”).font.bold
    Range(“A1:K1”).font.bold
    Range(“A1:L1”).font.bold
    Range(“A1:M1”).font.bold
    Range(“A1:N1”).font.bold
    Range(“A1:O1”).font.bold
    Range(“A1:P1”).font.bold
    Range(“A1:Q1”).font.bold
    Range(“A1:R1”).font.bold
    Range(“A1:S1”).font.bold
    Range(“A1:T1”).font.bold
    Range(“A1:U1”).font.bold
    Range(“A1:V1”).font.bold
    Range(“A1:W1”).font.bold
    Range(“A1:X1”).font.bold
    Range(“A1:Y1”).font.bold
    Range(“A1:A1”).font.bold
    Range(“A1:AA1”).font.bold
    Range(“A1:AB1”).font.bold
    Range(“A1:AC1”).font.bold
    Range(“A1:AD1”).font.bold
    —————————————
    Please note that there is no:
    Range(“A1:Z1”).font.bold
    which makes sense because 26/26 is of course simply 1 with no remainder.

    Any thoughts on how I can bypass this? I love the elegance of this and would love to use it if I can get the Z problem solved.

    Thanks!!!!

  • Anonymous

    Sending the file as an attachment from within the proc.
    Hello,
    As the many previous posts declared… This is an awesome article. Thanks for posting it. I have many automated routines on multiple servers that currently use ActiveX scripts to write to the .xls files. I will be migrating to this as time permits. I have one question for you though. I was unable to email the file from the same sp that builds the .xls. The file is not unlocked until the sp completes. It was easy enough to combat this by creating a 2nd proc to use as an additional step in the job, but I’d sure like to know how to release the file and mail it from the same proc. Any input would be much appreciated. Thanks again! Bob.

    …this shows the basic mail sql I’m using.
    if
    @cnt > 0
    BEGIN
    EXEC msdb.dbo.sp_send_dbmail
    @profile_Name = N’default’,
    @recipients= @distList,
    @body= @bodyText,
    @subject = @SubjectText,
    @file_attachments = ‘C:Mail.FilesServiceCalls.xls’
    END

  • Anonymous

    Alphabet question
    Hi Terrie,

    I encountered the same problem. If your column count is 26 then 26 % 26 = 0 which nets an empty string from the alphabet.

    I got around this by creating a small function to build the cell reference. In it it checks for column numbers that are evenly divided by 26 and sets the value to ‘Z’.

  • Anonymous

    Error with sp_OACreate
    At one point during testing I received an error that caused my test procedure to fail.

    Now when I run it sp_OACreate returns the error “Server execution failed” when trying to create the excel.application object. Could it be because the object already exists? If so, how do I delete the object? I figure that I could call sp_OADestroy, but I don’t know the object’s ID.

    Any ideas?

    Thanks!

  • Anonymous

    Error with sp_OACreate
    At one point during testing I received an error that caused my test procedure to fail.

    Now when I run it sp_OACreate returns the error “Server execution failed” when trying to create the excel.application object. Could it be because the object already exists? If so, how do I delete the object? I figure that I could call sp_OADestroy, but I don’t know the object’s ID.

    Any ideas?

    Thanks!

  • Anonymous

    Error with sp_OACreate
    At one point during testing I received an error that caused my test procedure to fail.

    Now when I run it sp_OACreate returns the error “Server execution failed” when trying to create the excel.application object. Could it be because the object already exists? If so, how do I delete the object? I figure that I could call sp_OADestroy, but I don’t know the object’s ID.

    Any ideas?

    Thanks!

  • Faith

    Excel Import using SSIS
    I have 25 columns in my excel which is the second row in the excel. I have merged column comprising of 3-4 columns so like wise 7 columns as the 1st row. How can I suggest SSIS that 2ns row in the header or column names?
    Also is there any way using SSIS that I can refer to specific cell in the excel?

  • Philippe

    Drop table (tab)
    Hello,
    Too much of a good stuff here but you know we are all hungry for more.
    This one
    –you could drop it again!
    spExecute_ADODB_SQL @DDL=’drop table CambridgePubs’,
    @DataSource =’c:CambridgePubs.xls’
    Does not seem to work, I get error.
    I want to remove the excel tab from the workbook then I can re-create it. Doing it this way let me keep the rest of the workbook intact and populate the tab I re-create from cell A2. I can create the tab, I cannot just delete it. Is that possible?

  • Philippe

    Drop table (tab)
    Hello,
    Too much of a good stuff here but you know we are all hungry for more.
    This one
    –you could drop it again!
    spExecute_ADODB_SQL @DDL=’drop table CambridgePubs’,
    @DataSource =’c:CambridgePubs.xls’
    Does not seem to work, I get error.
    I want to remove the excel tab from the workbook then I can re-create it. Doing it this way let me keep the rest of the workbook intact and populate the tab I re-create from cell A2. I can create the tab, I cannot just delete it. Is that possible?

  • Philippe

    Drop table (tab)
    Hello,
    Too much of a good stuff here but you know we are all hungry for more.
    This one
    –you could drop it again!
    spExecute_ADODB_SQL @DDL=’drop table CambridgePubs’,
    @DataSource =’c:CambridgePubs.xls’
    Does not seem to work, I get error.
    I want to remove the excel tab from the workbook then I can re-create it. Doing it this way let me keep the rest of the workbook intact and populate the tab I re-create from cell A2. I can create the tab, I cannot just delete it. Is that possible?

  • Anonymous

    Thnx
    I’m new with SQL and this really helps me generating reports…ty…

  • Anonymous

    Great
    Thanks for all this… great… thanz once again

  • Anonymous

    Great
    Thanks for all this… great… thanz once again

  • Anonymous

    excellent article
    one of the best that I hav read recently, excellent job

  • Andylysm

    SQL Server Excel Workbench
    why error this :
    Server: Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure ‘CambridgePubs’.

  • Vladislav

    Thanks a lot
    Its just that I’ve been looking for. Thank you very much!

  • Neil

    Passwords
    Hi,

    If I try and add a password to the .xls file then the OPENROWSET/OPENDATASOURCE commands return an error
    [OLE/DB provider returned message: Unrecognized database format ‘C:CambridgePubs.xls’.]

    Any Ideas?

    EXEC sp_addlinkedserver ‘CambridgePubDatabase’,
    ‘Jet 4.0’,
    ‘Microsoft.Jet.OLEDB.4.0’,
    ‘C:CambridgePubs.xls’,
    NULL,
    ‘;PWD=password’,
    ‘Excel 8.0’

  • Anonymous

    Re: I agree with all the comments above
    Phil if you posted a pic like the beautiful Robyn maybe some of our lady collegues would give you the kudos. Just a suggestion….lol Thanks Robyn and Phil for all your articles.

  • Anonymous

    Naming a table after the date.
    I am trying to create the table name using the current date, but I keep getting Syntax errors, is it possible to use a varible as the table name?

  • Anonymous

    Naming a table after the date.
    I am trying to create the table name using the current date, but I keep getting Syntax errors, is it possible to use a varible as the table name?

  • Anonymous

    Naming a table after the date.
    I am trying to create the table name using the current date, but I keep getting Syntax errors, is it possible to use a varible as the table name?

  • Anonymous

    Naming a table after the date.
    I am trying to create the table name using the current date, but I keep getting Syntax errors, is it possible to use a varible as the table name?

  • Anonymous

    Naming a table after the date.
    I am trying to create the table name using the current date, but I keep getting Syntax errors, is it possible to use a varible as the table name?

  • MVV

    Thanks
    Nice work.I stumbled on this page googling and now it’s bookmarked.

    Thank you both a lot for sharing this knowledge.

  • Anonymous

    re: Naming a table after the date.
    I am sure you cannot do a Table Create and use the date to create the table name.

  • Anonymous

    re: Naming a table after the date.
    Using “spExecute_ADODB_SQL” and “exec sp_executesql”, this will allow you to create a worksheet based on date. You may be able to modify it to allow for another variable name.

    declare
    @cmd nvarchar(2000),
    @StoreProcedureName varchar(30),
    @ParameterName varchar(30),
    @tablename varchar(30),
    @therows varchar(60),
    @thesource varchar(30),
    @filename varchar(30)

    set @StoreProcedureName = ‘spExecute_ADODB_SQL @DDL= ‘
    set @ParameterName = ”’Create Table ‘
    set @tablename = CONVERT(VARCHAR(8),GetDate(),112)
    set @therows = ‘ (Pubname Text, Address Text, Postcode Text)”’
    set @thesource = ‘@DataSource’
    set @filename = ”’C:CambridgePubs.xls”’
    set @cmd = @StoreProcedureName + @ParameterName + @tablename + @therows + ‘, ‘ + @thesource + ‘=’ + @filename

    exec sp_executesql @cmd

  • Anonymous

    re: Naming a table after the date.
    Using “spExecute_ADODB_SQL” and “exec sp_executesql”, this will allow you to create a worksheet based on date. You may be able to modify it to allow for another variable name.

    declare
    @cmd nvarchar(2000),
    @StoreProcedureName varchar(30),
    @ParameterName varchar(30),
    @tablename varchar(30),
    @therows varchar(60),
    @thesource varchar(30),
    @filename varchar(30)

    set @StoreProcedureName = ‘spExecute_ADODB_SQL @DDL= ‘
    set @ParameterName = ”’Create Table ‘
    set @tablename = CONVERT(VARCHAR(8),GetDate(),112)
    set @therows = ‘ (Pubname Text, Address Text, Postcode Text)”’
    set @thesource = ‘@DataSource’
    set @filename = ”’C:CambridgePubs.xls”’
    set @cmd = @StoreProcedureName + @ParameterName + @tablename + @therows + ‘, ‘ + @thesource + ‘=’ + @filename

    exec sp_executesql @cmd

  • Srinivas Alwala

    Count no. of worksheets in Excel workbook
    Hi,

    I would like to count no. of excel sheets in excel workbook without opening the file.

    Any help will be appreciated.

    Regards,

    Srinivas Alwala

  • Chris Frank

    64 Bit server issue
    In reference to an earlier post ….

    you answered….

    This is an ‘interesting’ problem. The way I get around this is to use a linked SQL Server Express ‘Grunt’ server to do this sort of job, on a 32-bit machine. Works fine: no hassle. But it aint the perfect solution.

    Could you put a little detail around how you used the ‘grunt’ server to do this. I have tried setting something up on a linked server and then called it from the 64 bit server but I am still having a issue.

    Any help is aprreciated.

    Thanks Chris

  • Anonymous

    Re: worksheet names in ADO…
    The default worksheet names in an Excel workbook are named [Sheet1$],[Sheet2$],[Sheet3$], etc. as far as ADO is concerned.

    If you renamed the sheets, then just put square brackets around the worksheet name.

    One thing about using ADO to pump data into an existing, preformatted Excel spreadsheet is that any formatting done to the first row of the spreadsheet will be pushed down into the cells as well (just like DTS will do) …

    To determine the number of “tables” in a workbook, you might be able to use the OpenSchema method (see any reference on ADODB.Connection object)…

    I haven’t tried this per se with Excel, but there are docs out there for doing just this for Access & SQL Server…

  • Anonymous

    Excel automation & ADO…
    Also, Excel’s CopyFromRecordset method (method of Range object), when passed an ADO recordset, far and away crushes any other method for getting data into Excel from a recordset. Make sure you’re not pushing more than 65K records (Excel 2000-2003), and you’re golden.

    Next fastest is walking through the ADO recordset from the database, and using ADO methods on the Excel recordset (AddNew, field-by-field update).
    This is pretty much what DTS does behind the scenes when pumping data to an Excel file.

    Way slowest is using Excel automation to do a record-by-record, field-by-field insert. Not even worth doing in a worst-case scenario if you have more than 10 or 20 records. I hyperbolize, but not much…

  • Anonymous

    Re: worksheet names in ADO…
    The default worksheet names in an Excel workbook are named [Sheet1$],[Sheet2$],[Sheet3$], etc. as far as ADO is concerned.

    If you renamed the sheets, then just put square brackets around the worksheet name.

    One thing about using ADO to pump data into an existing, preformatted Excel spreadsheet is that any formatting done to the first row of the spreadsheet will be pushed down into the cells as well (just like DTS will do) …

    To determine the number of “tables” in a workbook, you might be able to use the OpenSchema method (see any reference on ADODB.Connection object)…

    I haven’t tried this per se with Excel, but there are docs out there for doing just this for Access & SQL Server…

  • simple-Ton

    @QueryText
    Awesome stuff.

    One propblem I’ve faced – and YES! I AM a newbie.

    If the querySQL is too long – I get the feeling it gets truncated and the SP falls over.

    I have tried altering the SP @queryText parameter to increase the allowable string length – but ot no avail.

    Do I need to make this change elsewhere.

  • Arvind

    Pretty useful stuff
    This is useful code for excel manipulation from sql server. As for Phil Factor..it has really got me interested in finding out who this was ! **looking up old news to see who this person was** AND Robyn Page..dont remember watching the show ..but now i just might ! so what were you first..an actress or a developer? 🙂

  • Anonymous

    Fraud
    Robyn Page is no developer. LOL! This is a haox to induce interest in the author and posts. Why not separate profiles?

  • Sreekumar

    gives error
    Msg 50000, Level 16, State 1, Procedure spDMOExportToExcel, Line 260
    Error whilst Creating the Excel Application, on DTRTW0871IIDV02, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

  • Anonymous

    Thank you
    This site, it is a wonderful help. God Bless U1

  • Sreekumar

    Gives Error
    Page/Phil

    Please let me know why the above mentioned error is happening, i am calling the procedure in a loop and when some error occurs next time when i re-run i get all kind of errors, i think the excel obects or work boob is properly not getting closed when error out

  • sonukapoor

    Error
    I copied the sql from the download file and it throws the following error for the sp: spDMOExportToExcel

    Msg 50000, Level 16, State 1, Procedure spDMOExportToExcel, Line 240
    Error whilst Creating the Excel Application, on SQL4, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

    Can you help out Robyn?

  • DucatiFan

    Questions on more formatting options
    Hello. Again, thanks for posting this article. It was extremely useful!

    I have a question about creating some additional formatting within the worksheet. I saw your code on bolding text, autofitting, etc. I was able to use that code as a model to set some columns to width 20 and to add some shading. To find the syntax to use I looked at VBA code.

    However, there are two things I cannot figure out for the life of me. I cannot figure out how to set a column’s horizontal alignment to center and to set a Freeze Pane. I tried using .HorizontalAlignment and .FreezePanes in a million different ways with no success. Any ideas?

    Is there an online reference we can look at that shows all the T-SQL formatting syntax that is available?

    Thanks again!

  • timothyawiseman@gmail.com

    Very useful article on 32 bit servers
    This is very useful information on 32 bit servers, but as other commenters have already mentioned it tends not to work on a 64 bit server. Phil suggested using a 32-bit “grunt” machine, which is certainly a viable work around, but is there a genuine fix?

    Is there any plan to either port Microsoft.Jet.OLEDB.4.0 to a 64-bit version or create a full replacement?

  • Nicon

    A bit of help please.
    Hi guys

    Thanks for the information. I have used this script as part of an automated export routine and it has worked fantastically on my local instance.

    I am however encountering problems when i try and run the same code on from my local machine on a remote instance. It seems that there may be a security issue when creating the worksheet on the remote machine.

    The error that is being returned is:

    Msg 50000, Level 16, State 1, Procedure usp_spDMOExportToExcel, Line 250
    Error whilst Creating the Excel Application, on C6SERVER13, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval OUT [, additional IN, OUT, or BOTH params]]
    Msg 22051, Level 16, State 1, Line 0

    I have added the service user into a server role that has full control over the server in question.

    There is some more information here http://www.sqlservercentral.com/Forums/Topic626016-145-1.aspx?Update=1

    Any help would be great.

    Regards

    Steve

  • Nicon

    A bit of help please.
    Hi guys

    Thanks for the information. I have used this script as part of an automated export routine and it has worked fantastically on my local instance.

    I am however encountering problems when i try and run the same code on from my local machine on a remote instance. It seems that there may be a security issue when creating the worksheet on the remote machine.

    The error that is being returned is:

    Msg 50000, Level 16, State 1, Procedure usp_spDMOExportToExcel, Line 250
    Error whilst Creating the Excel Application, on C6SERVER13, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval OUT [, additional IN, OUT, or BOTH params]]
    Msg 22051, Level 16, State 1, Line 0

    I have added the service user into a server role that has full control over the server in question.

    There is some more information here http://www.sqlservercentral.com/Forums/Topic626016-145-1.aspx?Update=1

    Any help would be great.

    Regards

    Steve

  • Nicon

    A bit of help please.
    Hi guys

    Thanks for the information. I have used this script as part of an automated export routine and it has worked fantastically on my local instance.

    I am however encountering problems when i try and run the same code on from my local machine on a remote instance. It seems that there may be a security issue when creating the worksheet on the remote machine.

    The error that is being returned is:

    Msg 50000, Level 16, State 1, Procedure usp_spDMOExportToExcel, Line 250
    Error whilst Creating the Excel Application, on C6SERVER13, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval OUT [, additional IN, OUT, or BOTH params]]
    Msg 22051, Level 16, State 1, Line 0

    I have added the service user into a server role that has full control over the server in question.

    There is some more information here http://www.sqlservercentral.com/Forums/Topic626016-145-1.aspx?Update=1

    Any help would be great.

    Regards

    Steve

  • Nicon

    A bit of help please.
    Hi guys

    Thanks for the information. I have used this script as part of an automated export routine and it has worked fantastically on my local instance.

    I am however encountering problems when i try and run the same code on from my local machine on a remote instance. It seems that there may be a security issue when creating the worksheet on the remote machine.

    The error that is being returned is:

    Msg 50000, Level 16, State 1, Procedure usp_spDMOExportToExcel, Line 250
    Error whilst Creating the Excel Application, on C6SERVER13, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval OUT [, additional IN, OUT, or BOTH params]]
    Msg 22051, Level 16, State 1, Line 0

    I have added the service user into a server role that has full control over the server in question.

    There is some more information here http://www.sqlservercentral.com/Forums/Topic626016-145-1.aspx?Update=1

    Any help would be great.

    Regards

    Steve

  • Nicon

    A bit of help please.
    Hi guys

    Thanks for the information. I have used this script as part of an automated export routine and it has worked fantastically on my local instance.

    I am however encountering problems when i try and run the same code on from my local machine on a remote instance. It seems that there may be a security issue when creating the worksheet on the remote machine.

    The error that is being returned is:

    Msg 50000, Level 16, State 1, Procedure usp_spDMOExportToExcel, Line 250
    Error whilst Creating the Excel Application, on C6SERVER13, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval OUT [, additional IN, OUT, or BOTH params]]
    Msg 22051, Level 16, State 1, Line 0

    I have added the service user into a server role that has full control over the server in question.

    There is some more information here http://www.sqlservercentral.com/Forums/Topic626016-145-1.aspx?Update=1

    Any help would be great.

    Regards

    Steve

  • zjasz

    save error
    Hi all!

    First I’d like to say: It’s a great article!

    I tried to use the OLE automation script to create and manipulate an excel spreadsheet but, I got in an error. Everything worked fine until I tried to save the workbook, I got this error: “Unable to get the SaveAs property of the Workbook class”. I’m using MS-SQL 2005 (SP1), Office 2003 on Vista (SP1).

    I would greatly appreciate any help!

    Regards,
    Zoltan.

  • zjasz

    save error
    Hi all!

    First I’d like to say: It’s a great article!

    I tried to use the OLE automation script to create and manipulate an excel spreadsheet but, I got in an error. Everything worked fine until I tried to save the workbook, I got this error: “Unable to get the SaveAs property of the Workbook class”. I’m using MS-SQL 2005 (SP1), Office 2003 on Vista (SP1).

    I would greatly appreciate any help!

    Regards,
    Zoltan.

  • zjasz

    save error
    Hi all!

    First I’d like to say: It’s a great article!

    I tried to use the OLE automation script to create and manipulate an excel spreadsheet but, I got in an error. Everything worked fine until I tried to save the workbook, I got this error: “Unable to get the SaveAs property of the Workbook class”. I’m using MS-SQL 2005 (SP1), Office 2003 on Vista (SP1).

    I would greatly appreciate any help!

    Regards,
    Zoltan.

  • zjasz

    save error
    Hi all!

    First I’d like to say: It’s a great article!

    I tried to use the OLE automation script to create and manipulate an excel spreadsheet but, I got in an error. Everything worked fine until I tried to save the workbook, I got this error: “Unable to get the SaveAs property of the Workbook class”. I’m using MS-SQL 2005 (SP1), Office 2003 on Vista (SP1).

    I would greatly appreciate any help!

    Regards,
    Zoltan.

  • Bhaskar Biswas

    Errror
    I am using the above script but when i change the Datasource to something like this:-\192.168.1LeadsCambridgePubs.xls.It thows error:- Error whilst Opening Connection to XLS, for file Create or Append, The Microsoft Jet database engine cannot open the file ”. It is already opened exclusively by another user, or you need permission to view its data.

    Any solution??

  • buijules

    multiple result sets
    My script returns multiple results but the excel only grabs the 1st one, does anyone know how to fix this?
    Thanks

  • roadrunner71

    Turn on Autofilters?
    Great article.

    I wanted a little more formatting done to the standard (ie. heading cell color) so I added a few more lines of code just after the font bold stuff.

    –format the headings font color
    –set the font.colorindex, then the number at the end of the execute command
    –defines the color on the output. based on excel 56 colors – 2 white – 1 black
    IF @hr=0
    SELECT @strErrorMessage=’formatting the column headings font color’,
    @objErrorObject=@objWorkSheet,
    @command=’Range(“A1:’
    +SUBSTRING(@alphabet,@currentColumn/26,1)
    +SUBSTRING(@alphabet,@currentColumn % 26,1)
    +’1’+'”).font.colorindex’
    IF @hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 2
    –format the headings cell color
    IF @hr=0
    SELECT @strErrorMessage=’formatting the column headings cell color’,
    @objErrorObject=@objWorkSheet,
    @command=’Range(“A1:’
    +SUBSTRING(@alphabet,@currentColumn/26,1)
    +SUBSTRING(@alphabet,@currentColumn % 26,1)
    +’1’+'”).interior.colorindex’
    IF @hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 16

    Does anyone know how to turn on autofilters for the headings? Also, it would be nice to turn on grid lines.

    Thanks

  • roadrunner71

    Excel 97-2003 output for machines with Excel 2007
    I also had an issue where I have a machine with Excel 2007 installed so the output from the above program was in Excel 2007 format. I have some users that are still using Excel 2003 so by adding the following code, I was able to save the file in 97-2003 format.

    old code:
    @command = ‘SaveAs(“‘ + @filename + ‘”)’
    new code:
    @command = ‘SaveAs(“‘ + @filename + ‘”,FileFormat:=56)’

    Here are a list of formats:
    51 = xlOpenXMLWorkbook (without macro’s in 2007-2010, xlsx)

    52 = xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007-2010, xlsm)

    50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro’s, xlsb)

    56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)

    One more thing, if you do not add the following quit command, after creating your file, Excel will be left open on the machine. You can see it in the task manager.

    EXEC sp_OAMethod @objExcel, ‘Quit’

  • roadrunner71

    Excel 97-2003 output for machines with Excel 2007
    I also had an issue where I have a machine with Excel 2007 installed so the output from the above program was in Excel 2007 format. I have some users that are still using Excel 2003 so by adding the following code, I was able to save the file in 97-2003 format.

    old code:
    @command = ‘SaveAs(“‘ + @filename + ‘”)’
    new code:
    @command = ‘SaveAs(“‘ + @filename + ‘”,FileFormat:=56)’

    Here are a list of formats:
    51 = xlOpenXMLWorkbook (without macro’s in 2007-2010, xlsx)

    52 = xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007-2010, xlsm)

    50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro’s, xlsb)

    56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)

    One more thing, if you do not add the following quit command, after creating your file, Excel will be left open on the machine. You can see it in the task manager.

    EXEC sp_OAMethod @objExcel, ‘Quit’

  • markLondon

    Changing Font Color using SQL Server
    Can anyone show me how to change the font color using SQL Server? The below is the programming style I’m using.

    — SET cell format to Currency two digits
    EXECUTE @rs = master.dbo.sp_OASETProperty @xlCell, ‘NumberFormat’, ‘$#,##0.00’

  • Vishnuvardhan

    Formatting
    This article helps me so much.
    But i need one more help on this.
    Suppose my Column A has integers that comes from Stored procedures.
    I want to apply formatting (like 0000,0000.00,-0000.00,etc). How to achieve this? Can you help me on this?
    Thanks in advance.

  • twl55

    Keeping Numbers as Numbers
    I use OPENROWSET to write an SQL query output to Excel. The problem I’m having is that all my numeric fields end up in Excel as text strings. Does anyone know how to make them be numbers in Excel?

    Thanks

  • MartinMaule

    ADO on server
    What I need install on SQL 2008 64bit for create excel 2007 worksheets?Is it not possible to install full Office 2007 to server.
    Our admins tried install this
    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734
    but no happened and when I try run
    spExecute_ADODB_SQL @DDL=’Create table CambridgePubs
    (Pubname Text, Address Text, Postcode Text)’,
    @DataSource =’C:CambridgePubs.xls’
    I get this error
    Msg 50000, Level 16, State 1, Procedure spExecute_ADODB_SQL, Line 105
    Error whilst Opening Connection to XLS, for file Create or Append, Provider cannot be found. It may not be properly installed.

  • MartinMaule

    ADO on server part II
    maybe is problem in spExecute_ADODB_SQL. Can I use this or have I edit with new?I think Excel 2007 is Excel 12.0?
    @ConnectionString VARCHAR(255)

    = ‘Provider=Microsoft.Jet.OLEDB.4.0;

    Data Source=%DataSource;

    Extended Properties=Excel 8.0’

  • MartinMaule

    ADO on server part II
    maybe is problem in spExecute_ADODB_SQL. Can I use this or have I edit with new?I think Excel 2007 is Excel 12.0?
    @ConnectionString VARCHAR(255)

    = ‘Provider=Microsoft.Jet.OLEDB.4.0;

    Data Source=%DataSource;

    Extended Properties=Excel 8.0’

  • bhatiaashu

    develop excel report from sqlserver
    Hello,

    I am new to T-SQL.I have to develop excel report from sqlserver So I want to know how should I start and what needs to be downloaded for this purpose

  • swapnajeet

    Getting error
    I wanted to create excel sheet from Stored procedure with some formatting.I have copied the code after downloading it but i am getting the following error when running it.
    “sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BO”

    I was using the following command to run it

    spDMOExportToExcel @SourceServer=’My DB Server Name’,
    @QueryText = ‘use MyDatabase select Pubname, Address, Postcode from ##CambridgePubs’,
    @filename = ‘C:MyPubDatabase.xls’,
    @WorksheetName=’MyFavouritePubs’,
    @RangeName =’ABCDE’

    Am i missing something?What does the Parameter @Range Expect.

  • swapnajeet

    Getting error
    I wanted to create excel sheet from Stored procedure with some formatting.I have copied the code after downloading it but i am getting the following error when running it.
    “sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BO”

    I was using the following command to run it

    spDMOExportToExcel @SourceServer=’My DB Server Name’,
    @QueryText = ‘use MyDatabase select Pubname, Address, Postcode from ##CambridgePubs’,
    @filename = ‘C:MyPubDatabase.xls’,
    @WorksheetName=’MyFavouritePubs’,
    @RangeName =’ABCDE’

    Am i missing something?What does the Parameter @Range Expect.

  • swapnajeet

    Getting error
    I wanted to create excel sheet from Stored procedure with some formatting.I have copied the code after downloading it but i am getting the following error when running it.
    “sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BO”

    I was using the following command to run it

    spDMOExportToExcel @SourceServer=’My DB Server Name’,
    @QueryText = ‘use MyDatabase select Pubname, Address, Postcode from ##CambridgePubs’,
    @filename = ‘C:MyPubDatabase.xls’,
    @WorksheetName=’MyFavouritePubs’,
    @RangeName =’ABCDE’

    Am i missing something?What does the Parameter @Range Expect.

  • swapnajeet

    Getting error
    I wanted to create excel sheet from Stored procedure with some formatting.I have copied the code after downloading it but i am getting the following error when running it.
    “sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BO”

    I was using the following command to run it

    spDMOExportToExcel @SourceServer=’My DB Server Name’,
    @QueryText = ‘use MyDatabase select Pubname, Address, Postcode from ##CambridgePubs’,
    @filename = ‘C:MyPubDatabase.xls’,
    @WorksheetName=’MyFavouritePubs’,
    @RangeName =’ABCDE’

    Am i missing something?What does the Parameter @Range Expect.

  • swapnajeet

    Getting error
    I wanted to create excel sheet from Stored procedure with some formatting.I have copied the code after downloading it but i am getting the following error when running it.
    “sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BO”

    I was using the following command to run it

    spDMOExportToExcel @SourceServer=’My DB Server Name’,
    @QueryText = ‘use MyDatabase select Pubname, Address, Postcode from ##CambridgePubs’,
    @filename = ‘C:MyPubDatabase.xls’,
    @WorksheetName=’MyFavouritePubs’,
    @RangeName =’ABCDE’

    Am i missing something?What does the Parameter @Range Expect.

  • CodeMonkey

    Missing Close
    You forgot to close the connection. It leaves a connection open every time you run a command.

  • jeanligas

    Hi maam
    I Got an error on this: when I run sp:

    Msg 50000, Level 16, State 1, Procedure spDMOExportToExcel , Line 497
    Error whilst instantiating the DMO, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

    please help me..
    how I call sp:

    exec spDMOExportToExcel @SourceServer=’localhost’,
    @SourceUID= ‘sa’,
    @SourcePWD = ‘Passw0rd’,
    @QueryText = ‘select * from emp’,
    @filename = ‘C:Emp.xls’,
    @WorksheetName=’MyFavouritePubs’,
    @RangeName =’MyRangeName’

    did I supply invalid parameters help me please and please input sample RangeName

  • jeanligas

    Hi maam
    I Got an error on this: when I run sp:

    Msg 50000, Level 16, State 1, Procedure spDMOExportToExcel , Line 497
    Error whilst instantiating the DMO, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

    please help me..
    how I call sp:

    exec spDMOExportToExcel @SourceServer=’localhost’,
    @SourceUID= ‘sa’,
    @SourcePWD = ‘Passw0rd’,
    @QueryText = ‘select * from emp’,
    @filename = ‘C:Emp.xls’,
    @WorksheetName=’MyFavouritePubs’,
    @RangeName =’MyRangeName’

    did I supply invalid parameters help me please and please input sample RangeName