Unusual Excel File Size

[jkb17]

Hello,

I have a DTS package that writes to a temporary excel file reiteratively. The package dumps straight table data from a SQL Server source to this excel destination file. Then I have a process that will copy, rename file and paste the file to a separate location.

Each time I write to the excel file I execute the "DROP Table <tablename>" against the Excel Datasource to clear the sheet. Subsequently, I execute the CREATE Table to initialize the table in the Excel workbook.

The problem is that as it ticks through the temporary file size grows and grows and grows despite being cleared on each run. If you open the file in Excel and re-save it the file shrinks to its appropriate size.

Any tips on this?

Thanks

Jim

 

[philhege]

You many need an ActiveX step to perform this cleanup.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[mutley1]

The way I do it is to have a template Excel file and my first step in the package is to copy the template to the report name and dump direct to that.

exec master..xp_cmdshell 'copy D:\Services\DiskSpaceTemplate.xls D:\Services\DiskSpaceReport.xls'

Then let the tasks etc. run through and populate the actual report. After that you can rename it / move it etc. to whatever you want. Saves the temp building up.

Cheers,

M.

 

[AlexCuse]

I've found mutley1's solution to work the best for me.

If you choose to go the activeX route, here is a bit of excel VBA that someone on here gave me to clear the unused space. You could probably get it into vbscript pretty easily, but you'd probably need excel installed on the server (which is another reason I used mutley's)

For Each ws In ActiveWorkbook.Worksheets
  'A2 - keep headers
  ws.Range("A2", ws.Range("A2").SpecialCells(11)).EntireRow.Delete 
  ' 11=xlLastCell
  ws.UsedRange
Next

HOpe this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom