File bloat in Excel 2010 w/ pictures

[RWilkes]

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?

[rory]

What format is the picture, how large is it, and are you simply adding it via the Picture option on the Insert tab?

[RWilkes]

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.)

[rory]

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?

[RWilkes]

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

[rory]

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.

[RWilkes]

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.

[rory]

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.

[RWilkes]

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

[rory]

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?

[RWilkes]

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!

[rory]

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.)