Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Oct 2002
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bloated Excel Workbook (2000)

    I keep having a problem with Excel workbooks suddenly shooting up from perhaps 250kb to 6-8MB. It has 5 worksheets, with a max of 200 rows in each. I've checked to make sure the last cell is not below the actual data. In fact, I made a copy of the workbook, selected an entire worksheet, used Edit, Clear, All, for each worksheet, but the file barely changed. Then I deleted the first worksheet, and it decreased by 75%! Deleting each empty worksheet moved the size down more until it was at 20KB with just one sheet.

    I hope that someone can tell me what is causing this.

    Thanks

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Bloated Excel Workbook (2000)

    Hi,

    This sort of thing can happen if you've got any formatting, values (inluding nulls) outside the 'used' range, and/or references to cells outside the 'used' range.

    Another trap can be if you've had an embedded object attached to a cell in a row/column that you've deleted. Sometimes that simply reduces the object's height or width to 0, so it can't be seen - but it's still in there.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Lounger
    Join Date
    Oct 2002
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bloated Excel Workbook (2000)

    Thanks Macropod... is there a way to detect such objects?

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Bloated Excel Workbook (2000)

    Other than hoping for a eureka moment when moving the cursor about the screen (it's shape will change as it passes over an embedded object), the only reliable way I know of would be via a macro. For example:

    Sub FindShapes()
    If ActiveWorkbook.ActiveSheet.Shapes.Count > 0 Then
    For Each Shp In ActiveWorkbook.ActiveSheet.Shapes
    ShapeNames = ShapeNames & Shp.Name & vbCr
    Next Shp
    MsgBox "Found Shapes:" & vbCr & ShapeNames
    End If
    End Sub


    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Lounger
    Join Date
    Oct 2002
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bloated Excel Workbook (2000)

    macropod --- I did find that there are several 'Pictires'... now I need to find out how to find them in the spreadsheet. Thanks!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bloated Excel Workbook (2000)

    You can find out where the n-th shape is by typing the following instruction in the Immediate window, then pressing Enter:

    ? ActiveSheet.Shapes(n).TopLeftCell.Address

    (replace n by a number in the range of 1 to the number of shapes). Or, to see all the top left cell for all pictures, use this variation on macropod's macro:

    Sub FindShapes()
    Dim ShapePos As String, Shp As Shape
    If ActiveWorkbook.ActiveSheet.Shapes.Count > 0 Then
    For Each Shp In ActiveWorkbook.ActiveSheet.Shapes
    ShapePos = ShapePos & vbCr & Shp.TopLeftCell.Address
    Next Shp
    MsgBox "Shape Top Left Cells:" & ShapePos
    End If
    End Sub

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Bloated Excel Workbook (2000)

    Hi,

    You can find out what cells the shapes are attached to via Hans' reply, and/or, you can reveal them via:

    <pre>Sub ResizeShapes()
    If ActiveWorkbook.ActiveSheet.Shapes.Count > 0 Then
    For Each Shp In ActiveWorkbook.ActiveSheet.Shapes
    Shp.Select
    ' The next with statement resizes all objects to an arbitrary value.
    With Selection.ShapeRange
    .Height = 100
    .Width = 100
    End With
    On Error Resume Next
    ' The next with statement resizes picture and OLE objects to their full size.
    With Selection.ShapeRange
    .ScaleHeight 1, True
    .ScaleWidth 1, True
    End With
    Next Shp
    End If
    End Sub
    </pre>


    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bloated Excel Workbook (2000)

    Edit, clear all does NOT reset the selected area. You can only do that using two methods:

    - DELETE the entire rows/columns
    - use this code:

    Dim oRange as range
    Set oRange=Activesheet.Usedrange
    Set oRange=Nothing

    To get rid of all objects, press F5 (goto), click special, check "Objects". OK your way out and hit the Del key.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Lounger
    Join Date
    Oct 2002
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bloated Excel Workbook (2000)

    macropod, hans, jan:

    I want to thank all three of you for your valuable information. Since there were no objects that I intentionally added to my worksheet, I tried Jan's suggestion, using the GoTo/Special/Objects. That selected all of the objects and allowed me to eliminate them with one keypress of the delete key!

    I will keep the VB code as a sample for future reference. Is there an comprehensive reference that lists all of the methods that can be employed in Excel VB?

    This is definitely a lesson that I will remember, and probably have the opportunity to share with my peers.

    Thanks again!

  10. #10
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Kennett Square, Pennsylvania, USA
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bloated Excel Workbook (2000)

    I am trying to use this code to resize a control that is hidden on a worksheet (so I can locate it and kill it). The code that Hans provided shows the position of this control as $E$7 (although I cannot see it). When I run this code to try to resize it, I get an error with Shp.select (method of object 'shape' failed). I did insert a Dim statement to dim shp as shape. Any idea why the code is not running?

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Bloated Excel Workbook (2000)

    You cannot select a hidden object.
    You can delete it even if it is not visible.
    <pre>activesheet.shapes(1).delete</pre>

    Or to make it visible:
    <pre>activesheet.shapes(1).visible = true</pre>


    Set the shape number as appropriate.

    Steve

Posting Permissions

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