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

Question

Post reply

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

COZYROC

One Orange Chip

Points: 28499

November 14, 2011 at 9:46 pm

#1407612

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: 245813 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?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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: 245813 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?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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/