Excel Workbook over 116 MB After Loading from SQL, need to Select Range & Delete to Reduce File Size

Question

Excel Workbook over 116 MB After Loading from SQL, need to Select Range & Delete to Reduce File Size

Welsh Corgi

SSC Guru

Points: 116520
More actions
November 7, 2011 at 11:41 am

#251195

I'm loading multiple Sheets into an Excel File from SQL Server.
The macro listed below will reset the used cell ranges and should reduce the File Size.
I can't delete all of the sheets and I do not want to delete the header information for the sheets that I'm populating. The headers have coloring, bolding and other formats types.
One sheet has over 250 K Rows. Once the Excel File gets around 50 MB Visual Studio Starts hanging.
I probably want to do this in a Script Task.
Can someone provide some advise on how to best go about this?
Thanks!
Sub Reset_all_lastcells()
'David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
Dim sh As Worksheet, x As Long
For Each sh In ActiveWorkbook.Worksheets
x = sh.UsedRange.Rows.Count 'see J-Walkenbach tip 73
Next sh
End Sub
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

Using Excel automation API is not very good idea for many reasons.

If you can use third-party solutions, check the commercial CozyRoc Excel Task. It supports deletion of columns and/or rows and no programming skills are required.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

CozyRoc (11/14/2011)
Using Excel automation API is not very good idea for many reasons.
If you can use third-party solutions, check the commercial CozyRoc Excel Task. It supports deletion of columns and/or rows and no programming skills are required.

Nice, I belive I saw two prices for a single copy.

It may be a little hard to justify.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

Welsh Corgi (11/7/2011)
I'm loading multiple Sheets into an Excel File from SQL Server.
The macro listed below will reset the used cell ranges and should reduce the File Size.
I can't delete all of the sheets and I do not want to delete the header information for the sheets that I'm populating. The headers have coloring, bolding and other formats types.
One sheet has over 250 K Rows. Once the Excel File gets around 50 MB Visual Studio Starts hanging.
I probably want to do this in a Script Task.
Can someone provide some advise on how to best go about this?
Thanks!
Sub Reset_all_lastcells()
'David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
Dim sh As Worksheet, x As Long
For Each sh In ActiveWorkbook.Worksheets
x = sh.UsedRange.Rows.Count 'see J-Walkenbach tip 73
Next sh
End Sub

I don't think I fully understand the situation.

Are you saying that the Excel file starts small, then grows to 116MB in one hit? And that this can be reduced, simply by running the script you posted?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

Welsh Corgi (11/15/2011)
CozyRoc (11/14/2011)
Using Excel automation API is not very good idea for many reasons.
If you can use third-party solutions, check the commercial CozyRoc Excel Task. It supports deletion of columns and/or rows and no programming skills are required.
Nice, I belive I saw two prices for a single copy.
It may be a little hard to justify.
Thanks.

Which two prices you are referring to? There are two options to license the software:

- Purchase SSIS+ Ultimate Subscription for $399.95/Year . The subscription includes two license keys, premium support and can be used with previous, current or future versions of the product. The subscription has to be renewed every year.

- Purchase SSIS+ 1.5 perpetual for $1,599.95 per license. The perpetual license never expires, but it is only good for 1.5 version.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Phil Parkin (11/15/2011)
Welsh Corgi (11/7/2011)
I'm loading multiple Sheets into an Excel File from SQL Server.
The macro listed below will reset the used cell ranges and should reduce the File Size.
I can't delete all of the sheets and I do not want to delete the header information for the sheets that I'm populating. The headers have coloring, bolding and other formats types.
One sheet has over 250 K Rows. Once the Excel File gets around 50 MB Visual Studio Starts hanging.
I probably want to do this in a Script Task.
Can someone provide some advise on how to best go about this?
Thanks!
Sub Reset_all_lastcells()
'David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
Dim sh As Worksheet, x As Long
For Each sh In ActiveWorkbook.Worksheets
x = sh.UsedRange.Rows.Count 'see J-Walkenbach tip 73
Next sh
End Sub
I don't think I fully understand the situation.
Are you saying that the Excel file starts small, then grows to 116MB in one hit? And that this can be reduced, simply by running the script you posted?

Phil,

I create a new Excel Workbook from scratch.

I import the Pivot Tables into 7 different sheets and the size grows to 116MB even though I deleted all but the first few rows from the sheets.

Excel has a last Row Counter so that if you press ctl>> shift>>end it takes to row 159,683 after deleting the rows.

That script would clear all of the sheets stating with the first row.

I need to clear individual sheets one at a time and I need to specify what row to start with.

I hope that this is more clear.

Sorry for the confusion.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I believe that I saw an old post with an old price but the price that you quoted is what I saw this past weekend on your site.

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Phil Parkin SSC Guru Points: 246965 More actions · Answer 7

I'm still a little puzzled.

I create a new Excel Workbook from scratch.
I import the Pivot Tables into 7 different sheets and the size grows to 116MB even though I deleted all but the first few rows from the sheets

If you are creating a new workbook, why did you have to delete any rows?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Phil Parkin (11/15/2011)
I'm still a little puzzled.
I create a new Excel Workbook from scratch.
I import the Pivot Tables into 7 different sheets and the size grows to 116MB even though I deleted all but the first few rows from the sheets
If you are creating a new workbook, why did you have to delete any rows?

Because the workbook contains the Data Connections, Pivot Table list etc and the Headings.

I'm not an expert in Excel and I'm looking to get with someone who is so that I can find a work around to this issue.

Two people have tried on this monster Excel Application and then it got handed off to me.:w00t:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/