Excel File Shrink

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a 3 MB VBA file that has grown to 50 MB+. Any ideas on how to chop
it down?

Things tried already:
-Last cell issue resolved, including contexture fix.
-Temp files in Windows & Internet purged.

I've spent hours researching this on this site and others, but nothing seems
to workl

Thanks for any help,
John
 
Do you have any pictures or shapes that you don't need in there? Any
named ranges which are no longer used?

Pete
 
Pete, thanks for the response. no name ranges or shapes. I use Microsoft's
Excess Formatting Cleaner hopefully to clean up these things. John
 
Pete wrote <<> Do you have any pictures or shapes that you don't need in
there? Any named ranges which are no longer used?>>

Similarly do you have any Pivot Tables which can be eliminated? Setting up
Pivot Tables afresh reduces the size of the file (I know not why)

Regards.

Bill Ridgeway
Computer Solutions
 
If you do have pivots, modify the pivot table settings so that it does
not save the underlying data with the table. This will easily cut the
file size in 1/2 if you have any large pivots.
 
Tim879 said:
If you do have pivots, modify the pivot table settings so that it does
not save the underlying data with the table. This will easily cut the
file size in 1/2 if you have any large pivots.
Click to expand...

Can you do that in an existing pivot table or do you have to start over
please?

Bill Ridgeway
 
Bill, thanks for the response, but no pivot tables. I read a vague
description about Excel wanting to store equations....dunno. Any other
suggestions would be welcomed. John
 
Tim, thanks for the response, but no pivot tables. I read a vague
description about Excel wanting to store equations....dunno. Any other
suggestions would be welcomed. John
 
JLatham, thanks for the try, but deleting (I assume this is what you mean by
'resetting') unused rows and columns didn't seem to help. As for
Contextures, I'm doing basically the same thing by using Microsoft's Add-In,
Excess Formatting Cleaner. Any other ideas would be appreciate. John
 
The default is to have "save data with table layout" ticked. What is the
practical difference between saving data and not saving data please.

Thanks.

Bill Ridgeway
 
Bill

If I understand the process correctly, saving data with your pivot
table actually makes a copy of your data and saves it with the pivot
table. For example if your pivot was based on a tab called data. After
creating your pivot, you could delete the data tab and still be able
to drill into your pivot table data since a copy of it was saved with
the pivot.

If you uncheck this option, a copy of the data is not saved, thus
making the file considerably smaller - especially if you work with
large data sets.

To find this option, right click on your pivot, go to table options ->
Uncheck Save Data With Pivot Table Layout.

You could use the code below to cycle through all of your pivots in a
workbook and uncheck this option as well as refresh the pivots.

Sub Update_All_Pivots()
'Cycles through each page of a work book checking for pivots. Each
pivot found
'is refreshed

continue = MsgBox("This macro will update all pivots in the workbook.
Do you want to continue?", _
vbYesNo)

If continue = vbYes Then

Dim iSheets As Integer, x As Integer
Dim iPivot As Integer, _
strCurrentSheet As String

On Error GoTo Error_Found

'Count number of sheets in workbook
iSheets = ActiveWorkbook.Sheets.Count

'remember current sheet
strCurrentSheet = ActiveSheet.Name

If Windows.Count = 0 Then _
GoTo Exit_Update_All_Pivots


strResponse = MsgBox("Do you want to save data with pivot table
(larger file size)?", vbYesNo)

Application.ScreenUpdating = False

For x = 1 To iSheets

'go to a worksheet to change pivot tables
Sheets(x).Activate

'turn warning messages off
Application.DisplayAlerts = False

'change all pivot tables on
'this worksheet one at a time
For iPivot = 1 To ActiveSheet.PivotTables.Count
'ActiveSheet.PivotTables(iPivot).HasAutoFormat = False
ActiveSheet.PivotTables(iPivot).PivotCache.Refresh

If strResponse = vbYes Then
ActiveSheet.PivotTables(iPivot).SaveData = False
End If
Next

'turn warning messages on
Application.DisplayAlerts = True

Next

'return to worksheet that you were originally at
Application.ActiveWorkbook.Sheets(strCurrentSheet).Activate

MsgBox ("Pivots updated successfully")

End If

GoTo Exit_Update_All_Pivots

Error_Found:
MsgBox ("Error Found. Macro ending.")
MsgBox Err & ": " & Error(Err)

Exit_Update_All_Pivots:
Application.CommandBars("PivotTable").Visible = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

If continue = vbNo Then MsgBox ("Cancelled")

End Sub
 
Tim879 said:
Bill

If I understand the process correctly, saving data with your pivot
table actually makes a copy of your data and saves it with the pivot
table. For example if your pivot was based on a tab called data. After
creating your pivot, you could delete the data tab and still be able
to drill into your pivot table data since a copy of it was saved with
the pivot.

If you uncheck this option, a copy of the data is not saved, thus
making the file considerably smaller - especially if you work with
large data sets.

To find this option, right click on your pivot, go to table options ->
Uncheck Save Data With Pivot Table Layout.

You could use the code below to cycle through all of your pivots in a
workbook and uncheck this option as well as refresh the pivots.

Sub Update_All_Pivots()
'Cycles through each page of a work book checking for pivots. Each
pivot found
'is refreshed

continue = MsgBox("This macro will update all pivots in the workbook.
Do you want to continue?", _
vbYesNo)

If continue = vbYes Then

Dim iSheets As Integer, x As Integer
Dim iPivot As Integer, _
strCurrentSheet As String

On Error GoTo Error_Found

'Count number of sheets in workbook
iSheets = ActiveWorkbook.Sheets.Count

'remember current sheet
strCurrentSheet = ActiveSheet.Name

If Windows.Count = 0 Then _
GoTo Exit_Update_All_Pivots


strResponse = MsgBox("Do you want to save data with pivot table
(larger file size)?", vbYesNo)

Application.ScreenUpdating = False

For x = 1 To iSheets

'go to a worksheet to change pivot tables
Sheets(x).Activate

'turn warning messages off
Application.DisplayAlerts = False

'change all pivot tables on
'this worksheet one at a time
For iPivot = 1 To ActiveSheet.PivotTables.Count
'ActiveSheet.PivotTables(iPivot).HasAutoFormat = False
ActiveSheet.PivotTables(iPivot).PivotCache.Refresh

If strResponse = vbYes Then
ActiveSheet.PivotTables(iPivot).SaveData = False
End If
Next

'turn warning messages on
Application.DisplayAlerts = True

Next

'return to worksheet that you were originally at
Application.ActiveWorkbook.Sheets(strCurrentSheet).Activate

MsgBox ("Pivots updated successfully")

End If

GoTo Exit_Update_All_Pivots

Error_Found:
MsgBox ("Error Found. Macro ending.")
MsgBox Err & ": " & Error(Err)

Exit_Update_All_Pivots:
Application.CommandBars("PivotTable").Visible = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

If continue = vbNo Then MsgBox ("Cancelled")

End Sub
Click to expand...

Thanks Tim for that comprehensive reply.

It would seem that the effect of not ticking the "Save data with data
layout" is that data is lost. If, therefore, the primary data is not lost
(either the spreadsheet is deleted or the Pivot Table transferred to another
file) there seems to be no practical reason for ticking SDWDL.

Perhaps someone could confirm this.

Thanks.

Bill Ridgeway
 
Back
Top