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

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?

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)

My last post came out as Anonymous which I am not (always)

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!

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.

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.

Hi,

Thanks for your artical on this subject

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

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'

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'

Oops! Quite right. Mea Culpa! Thanks very much for the bugfix. I must confess that neither Robyn or I looped it 64 times!

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.

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

Yeah. That is great, Denis. Thanks for the contribution. Much appreciated.

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