2

I have a workbook with over 150 worksheets. Each sheet contains an image that was copied to each work sheet from another 150 sheet workbook. The process was done by manually copying image 1 from workbook A to workbook B, image 2 from workbook A to workbook B, and so on.

The issue is that the workbook where pictures were copied to turned out huge since images were pasted as 'pictures' rather than 'keeping the source formatting'.

How can I compress all images in the large workbook? I already applied Excel's default "Compress Pictures" (E-mail (96 ppi)) resolution. This helped reduce the size of the document, but it's still huge compared to the document from which images were copied.

1

You can use PowerShell! We can adapt a script I wrote for PowerPoint presentations to work for Excel documents.

[CmdletBinding()]
Param(
    [Parameter(Mandatory = $true)][string]$File,
    [Parameter()][int]$Quality = 50
)
Add-Type -AssemblyName System.IO.Compression
Add-Type -AssemblyName System.Drawing
$fs = New-Object System.IO.FileStream (Resolve-Path $File), 'Open'
$zip = New-Object System.IO.Compression.ZipArchive $fs, 'Update'
$zip.Entries | ? {$_.FullName.StartsWith('xl/media/')} | % {
    $s = $_.Open()
    $img = [System.Drawing.Image]::FromStream($s)
    $s.Position = 0
    $codec = [System.Drawing.Imaging.ImageCodecInfo]::GetImageDecoders() | ? {$_.FormatId -eq [System.Drawing.Imaging.ImageFormat]::Jpeg.Guid}
    $qualityprop = [System.Drawing.Imaging.Encoder]::Quality
    $encodeparams = New-Object System.Drawing.Imaging.EncoderParameters 1
    $encodeparams.Param[0] = New-Object System.Drawing.Imaging.EncoderParameter $qualityprop, $Quality
    $img.Save($s, $codec, $encodeparams)
    $s.SetLength($s.Position)
    $s.Close()
}
$zip.Dispose()

Given an XLSX file, the script will convert all embedded images to JPEG format with an adjustable quality setting that defaults to 50. To use it, first save that script as a PS1 file, e.g. xlsximg.ps1. If you haven't already, follow the instructions in the Enabling Scripts section of the PowerShell tag wiki. Then open a PowerShell prompt to the folder where you saved the script and run it like this:

.\xlsximg.ps1 C:\path\to\workbook.xlsx

When I tested that, it reduced a workbook containing an embedded screenshot from 109 KB to 49 KB. If you need to compress it further, you can manually specify a lower quality setting, like so:

.\xlsximg.ps1 C:\path\to\workbook.xlsx -Quality 20

Note that it modifies the file in-place, so you might want to make a backup first.

  • This is just brilliant. I tried it and it reduced my file from 26 MB to 2.6 MB! I did want to confirm the following. Upon running your script I was promoted to supply value for the "file" parameter. Rather than saving the script like you instruct, I simply entered my .xlsx's path and hit ENTER. After a few moments my file was smaller in size. But did I miss anything or cause any issues by not saving the script before running it? – StatsScared Jun 29 '18 at 14:27
  • @StatsScared You ran the script right from the PowerShell console - that works too. Glad I could help! – Ben N Jun 29 '18 at 20:45
0

Excel is not made for storing images. You could try to embed the image from an external link, therefore the worksheet would not contain the image itself but fetch it from another source

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.