Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a way to establish each worksheets contribution to the overall file size of a workbook? I have a workbook that is 30mb and I can see no reason for that, so I would like to be able to quickly identify which ws(s) are causing the bloat.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't know of a really quick way. You could create a copy of the workbook and delete the worksheets one by one from the copy, saving the workbook and noting the file size each time.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Mmm, I feared as much.

    I can create a macro to delete the active sheet and save, but how can I show the file > properties dialogue?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Is this something you're going to do on a regular basis? If it's just this once, it's hardly worth the trouble to create a macro.

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The sheer quantity of worksheets dictates that I use a macro, I don't fancy the task without one!

  6. #6
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='VegasNath' post='787829' date='06-Aug-2009 21:58']The sheer quantity of worksheets dictates that I use a macro, I don't fancy the task without one![/quote]
    Does this tip give you any ideas of how to get the newly saved file size?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is a possible macro:

    Code:
    Sub TestSheetSize()
      Dim strFile As String
      Dim wbk1 As Workbook
      Dim wbk2 As Workbook
      Dim wsh1 As Worksheet
      Dim wsh2 As Worksheet
      Dim i As Integer
      Application.DisplayAlerts = False
      Set wbk1 = ActiveWorkbook
      strFile = wbk1.FullName
      Set wbk2 = Workbooks.Add(xlWBATWorksheet)
      Set wsh2 = wbk2.Worksheets(1)
      i = 1
      wbk1.Save
      wsh2.Cells(i, 1) = "Complete"
      wsh2.Cells(i, 2) = FileLen(strFile)
      For i = 2 To wbk1.Worksheets.Count
    	wsh2.Cells(i, 1) = "Minus " & wbk1.Worksheets(1).Name
    	wbk1.Worksheets(1).Delete
    	wbk1.Save
    	wsh2.Cells(i, 2) = FileLen(strFile)
      Next i
      Application.DisplayAlerts = True
    End Sub
    The macro creates a new workbook and writes output to this workbook.

  8. #8

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •