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.

Scott - Слава Україні
  • 22,532
StatsScared
  • 125
  • 1
  • 7

2 Answers2

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.

Ben N
  • 42,308
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

Wolfi Silverdream
  • 1