×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Unusual Excel File Size

Unusual Excel File Size

Unusual Excel File Size

(OP)
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

RE: Unusual Excel File Size

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.

RE: Unusual Excel File Size

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.

CODE

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.

RE: Unusual Excel File Size

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)

CODE

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

----signature below----
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close