Results 1 to 12 of 12
  • Thread Tools
  1. New Lounger
    Join Date
    Dec 2009
    Location
    Miami, FL, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File bloat in Excel 2010 w/ pictures

    Have used previous versions of Excel w/o issues, but 2010 is another story. A spreadsheet is used as an inspection report file...shared by users across computers at different locations, so no common folder in which to store pics and link, so picture has to be inserted as an object...considering Windows Live SkyDrive, among others as a long term solution. BUT FOR NOW:
    Adding pictures to an Excel 2010 workbook, even using picture compression, adds an extreme amount of file bloat:
    Test: Excel 2000 Excel 2003 Excel 2007 Excel 2010
    Empty SpreadSheet 13,824 13,824 15,360 22,013 54%
    1 (408K) pic 423,424 423,424 427,008 22,528
    (link in 2010)
    2010: Insert pic as object n/a n/a n/a 4,209,664 891%
    2010: Compress object pic 1,825,792 330%
    (to 96 ppi)
    Couldn't find any reasons why, or methods to reduce file size, on web.
    Anybody have any suggestions?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,851
    Thanks
    0
    Thanked 77 Times in 73 Posts
    What format is the picture, how large is it, and are you simply adding it via the Picture option on the Insert tab?
    Regards,
    Rory
    Microsoft MVP - Excel.

  4. New Lounger
    Join Date
    Dec 2009
    Location
    Miami, FL, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Picture is a jpg, 408K. I used "Insert Picture" on the test sheets above. In reality, pictures are added via VBA code ...Pictures.Insert... run from a form used to select the pics to be added. (Had to jump thru hoops in Excel 2010 'cause the ...Pictures.Insert... VBA command will only add a link, not the actual graphic.)

  5. WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,851
    Thanks
    0
    Thanked 77 Times in 73 Posts
    I couldn't replicate that in testing - the file size simply went up appropriately based on the image size. Can you post a file somewhere that shows the issue?
    Regards,
    Rory
    Microsoft MVP - Excel.

  6. New Lounger
    Join Date
    Dec 2009
    Location
    Miami, FL, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here are links to the test files I created.
    I tested Excel 2000, Excel 2003, Excel 2007, Excel 2010.
    Test 1: Open a new workbook and save (blank).
    2000 www.kcsinc.biz/ExcelTest/Excel2000Blank.xls
    2003 http://www.kcsinc.biz/ExcelTest/Excel2003Blank.xls
    2010 http://www.kcsinc.biz/ExcelTest/Excel2010Blank.xls
    All 3 files are reasonably sized...14 - 22k

    Test 2: Open a new workbook, insert one 408k jpg picture, and save.
    2000 http://www.kcsinc.biz/ExcelTest/Excel2000_1Pic.xls
    2003 http://www.kcsinc.biz/ExcelTest/Excel2003_1Pic.xls
    2010 http://www.kcsinc.biz/ExcelTest/Excel2010_1PicLink.xls
    Note that the 2000 and 2003 versions increased as expected. The 2010 version increased only slightly. This is because the default is to add a link to the picture, which only works if all computers opening the spreadsheet have the xls and jpg files in the same paths as the original.

    Test 3: For Excel 2010, open a new workbook, insert one picture (with content), and save.
    http://www.kcsinc.biz/ExcelTest/Exce...cCopyPaste.xls
    Note that the file size has increased to over 4 mb...for one picture (!?)

    RW

  7. WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,851
    Thanks
    0
    Thanked 77 Times in 73 Posts
    It appears that the larger file has the image saved as a higher resolution png file as opposed to the jpg files of the other workbooks, but I don't know why.
    Regards,
    Rory
    Microsoft MVP - Excel.

  8. New Lounger
    Join Date
    Dec 2009
    Location
    Miami, FL, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    TNX for the input.
    It's apparently something the "Office Wizards" deemed essential to the new version, although I can't fathom why. It was obviously a bad decision, but i suspect there's no way for them to go back. Hope some of them are lurking the lounge, see this thread, and say "Oh my gosh! This is terrible! What have we done? We must fix this immediately!"

    "Is that unreasonable?", Bob asked rhetorically.
    And while they're at it, they should fix the options in the VBA Insert Picture code so there's a parameter for 'Insert as link' or 'Insert with content', so we can add a graphic either way via macros.

  9. WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,851
    Thanks
    0
    Thanked 77 Times in 73 Posts
    Unfortunately, as I said, I can't replicate the problem, and without a consistent demonstrable issue, I cannot file it as a bug with MS.
    Regards,
    Rory
    Microsoft MVP - Excel.

  10. New Lounger
    Join Date
    Dec 2009
    Location
    Miami, FL, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, Rory. Let's work on the "...can't replicate..." problem, cause every time I add a picture, the file bloats.
    Steps I'm using:
    1. Opening a blank spreadsheet
    2. Using the 'Insert' tab, select 'Picture'. Navigate to, and select, jpg file and click 'Insert'.
    Standard stuff. There is no file bloat. However, the picture "in" the workbook is there by link, not by content.
    This is OK ONLY if I open this workbook on this computer and haven't moved the graphic file.
    If I copy the Excel file to another computer and open if, there is no picture, because the link is broken.
    Therefore, we must copy and paste the object:
    3. Open the file created in step 2. Select the graphic. Right click: 'Copy'. 'Delete' the (original) graphic.
    Select 'Paste Special' NOW FOR THE SURPRISE: The default is 'PNG' format. If you select 'JPEG' all seems well.
    (Haven't tried the other formats, so I don't know their effect.)

    Bottom line:
    You can insert a pic as jpg content, but it isn't as straight forward as it should be.
    If you can 'ding' M$, it should be to modify the 'Insert' command (both via GUI and VBA macro) so the process can be done in one step.

    TNX for your comments. By trying several options while trying to explain to you what I was doing, I think I've found a solution.
    Now, off to VBA, to see if I can tweak my macro to get it to do what I want

  11. WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,851
    Thanks
    0
    Thanked 77 Times in 73 Posts
    When choosing the file to insert, there should be a little dropdown on the right side of the Insert button that allows you to choose how you want to insert (mine defaults to inserting the file, not linking). Do you not have that?
    Regards,
    Rory
    Microsoft MVP - Excel.

  12. New Lounger
    Join Date
    Dec 2009
    Location
    Miami, FL, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, that's fine when inserting via GUI, if the user's can remember it's there. However, using a VBA macro is another matter...no buttons there!

  13. WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,851
    Thanks
    0
    Thanked 77 Times in 73 Posts
    If you are doing it in code, use the worksheet.Shapes.Addpicture method which allows you to specify whether to link or not. (the Pictures collection has actually been deprecated for some time, even though the recorder still uses it.)
    Regards,
    Rory
    Microsoft MVP - Excel.

Posting Permissions

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