Export to Excel

[Eyespi20]

Created my DTS job to export data from a query to excel. Worked a charm the first time, but when run subsequently, appends data to the spreadsheet instead of overwriting it. I can't find anything in help about overwriting. Know there is something you can set in the wizard, but the package exists and I would prefer not to have to re-create it thru the wizard, so can any of you point me to where I can find this in the package properties?

Thanks Loads!

Margaret

 

[JayKusch]

Still looking myself BUT there is always a Quick&Diryt way of doing it.

Add an "Execute SQL Task" task that leverages an xp_cmdshell to delete the old xls file before the new on is created. Something like ...

EXEC MASTER..xp_CmdShell 'DEL C:\MyDir\MyExcel.xls'

The attach it w/ a Success precedence/workflow to the Source connection object of your data pump.

Thanks

J. Kusch

 

[Eyespi20]

Thanks J. Unfortunately, this particular workbook also has another worksheet in it that holds the detail to the summary (which is the DTS task), so I really don't want to delete and recreate the workbook as I'll lose my other query.

I appreciate the quick come-back though.

Margaret

 

[ablackwe]

You need to create an "Execute SQL Task" and change the Existing Connection to that of the Excel Workbook. The sql statement should read DROP '<Worksheet name>'.

This task should be before you create the table.

Hope this helps.
Adam

 

[Eyespi20]

This still doesn't do what I want done as I don't want to delete the worksheet -- it has formulas, etc in it that I need to remain intact. I just want to replace the old data with the updated information. Maybe it can't be done this way at all.

Thanks!

Margaret

 

[sqlhotdog2005]

Eyespi20,

I have a similar need... basically we have a cash flow statement in Excel that we need to update daily based on records in a SQL database. We need to UPDATE _some_ cells in Excel, not overwrite the whole spreadsheet.

Did you find a solution to your problem?

 

[Eyespi20]

Not through DTS. I ended up doing a query from Excel -- Data/Get External Data/Database Query

I first set up a view in my db that pulled in the records to be examined, then imported that into the spreadsheet. You can refresh this whenever needed or set it to refresh on open or refresh every x hours. Formulas stay the same and my pivot table will also refresh appropriately.

Margaret