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?

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:\temp\Manquants\' +@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

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?

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

thanks for such a great innovative solution

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.

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.

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

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

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

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?

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?

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?

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

spDMOExportToExcel already does this to create the column headings in the excel spreadsheet.