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.

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?

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.

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.

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)".

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!!!!

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.Files\ServiceCalls.xls'
END

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

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!

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!

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!

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?

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?

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?

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?