[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2010 file with embedded picture

Posted on 2011-05-03
13
Medium Priority
?
949 Views
Last Modified: 2012-05-11
We are currently using Office 2010 Professional plus. One of the user in our environment create lot of excel spreadsheet and paste screenshot, when he saves it in Excel 2010 (xlsx) the file size is 7MB but if he select  'save as'   in office 2003 format, the file size reduces to 250KB. Surely, I can change the default file format to save as an office 2003 format but I want to know why Office 2010 file size is 'x' times greater than 2003 format in first place.
0
Comment
Question by:dean_b
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 18

Expert Comment

by:xtermie
ID: 35510586
First of all, I would like to say, that an xlsx file carries a lot of xml information within it.
Now that alone creates larger file sizes.  In reality an xlsx file is a collection of files.
Try renaming a xlsx file to .zip and unzipping it...you will be amazed!  his additional xml metadata causes the the file to bloat. And since native 2007 (.xlsx) is a zipped file format the file size increase is not noticeable, but when saving to .xls format (non-zipped) the file size increase is striking.

The new file type is a much more compact set of files that are then zipped together. When you back save, the graphics and features for both versions are put together in the old format.  

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35510592
The file format for 2007 and 2010 was changed to an XML/zip format. When you put an image on a worksheet that image has to be converted to a textual representation compatible with XML and which is not zipped very well. In 2003 and earlier, Excel does it's own compression of data including images and the results are a much more compact file.

Kevin
0
 

Author Comment

by:dean_b
ID: 35510604
Thanks for the information.

is there a setting I can set to compress all the pictures when the user hits save. Will that solve the problem? Our problem is user doesn't want to 'save as' everytime. He sends those files to external companies which sometimes get blocked due to attachment file size restriction applied on the other side.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35510609
No. But you can edit each picture and reduce it's size (resolution).

Kevin
0
 
LVL 18

Expert Comment

by:xtermie
ID: 35511219
you can actually compress all pictures before you save...
- if you open the pictures tools
- Format, adjust
Choose Compress, select compression settings and apply

if you need for this to be automatically before saving, a macro could do this for you
0
 
LVL 18

Expert Comment

by:xtermie
ID: 35511287
you could use a BEFORE Save event macro on your Excel files - will need to do this on the Office 2007 Template.  Something like this...the user may still have to press a few confirmation Oks..
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim mywb As Workbook
Dim sh As Worksheet
Dim img As PictureFormat

Set mywb = Application.ActiveWorkbook
    Call Compress_PIX
For Each sh In mywb.Sheets
Next
End Sub
===================
Sub Compress_PIX()
Dim octl As CommandBarControl
With Selection
    Set octl = Application.CommandBars.FindControl(ID:=6382)
    Application.SendKeys "%e~"
    Application.SendKeys "%a~"
    octl.Execute
End With
End Sub

Open in new window

0
 

Author Comment

by:dean_b
ID: 35695939
I have tried compressing pictures but it doesn't help as the size of the spreadsheet is still 7MB??
0
 
LVL 18

Expert Comment

by:xtermie
ID: 35696020
Ok, let's see, you could
(1) Try selecting the 96dpi setting
(2) if there is no problem in the version you use, you could save as Excel 2003 and see how that goes
0
 
LVL 18

Expert Comment

by:xtermie
ID: 35696028
It seems like even saving it with a new file name could also reduce file size.
Read here:
http://excel.tips.net/T002507_Reducing_File_Size.html

Basically it states that:
Try using Save As instead of Save. Doing so causes Excel to create a brand new file for your workbook, and in the process, free up some space. If that doesn't work, you should try individually copying your worksheets to a brand new workbook, and then saving the new workbook. If doing that doesn't work, then you can try copying just the worksheet data (not the actual worksheets) to a different workbook. Obviously, this can become quite time-intensive.
0
 
LVL 18

Expert Comment

by:xtermie
ID: 35696096
0
 

Accepted Solution

by:
dean_b earned 0 total points
ID: 35725577
Tried all above techniques but couldn't bring down the file size.

I have managed to reduce the file size by reducing the size of the picture and then compressing it  Below are the steps I went through

Created a new workbook, pasted the screenshot and hit saved. This time the it saved as 4.88MB file then reopened the workbook and reduced the size of the picture I pasted earlier and then compressed it. This time compression worked and the file size reduced to 308kb.


Below are my findings;

-  Reducing the size of the picture allows compression to work. if you increase the size of the picture and then hit compress it doesn't do anything and the file size stays the same.

- Even if you reduce the height in Picture size and properties by 1% and then compress the picture the same spreadsheet will reduced to 315kb.

-  You will need to resize every embedded picture you have pasted on the spreadsheet to reduce the file size

Thanks
Dean


0
 
LVL 50
ID: 35913495
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New style of hardware planning for Microsoft Exchange server.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question