Avoiding the Excel "Picture Is Too Large" error when reading the Clipboard

[JnLlnd]

Reading the Clipboard (or ClipboardAll) with AHK (v1) after copying cells in Excel displays the error message "The Picture Is Too Large and Will Be Truncated" (the error message is displayed by Excel, not AHK). This issue has been raised a few times on this forum (here and here) but it seems that it was never investigated.

This Techinline Blog post pretends that this error occurs when a third party app (my AHK script in this case) retrieves the content of the Clipboard for CF_METAFILEPICT format.

If your application interacts with the Windows clipboard using OLE technology, this indicates that somewhere your source code calls of the IDataObject interface. This interface contains the GetData method, which can result in the described Excel error when it tries to receive clipboard content in CF_METAFILEPICT format.

How to fix:
Skip the GetData call if the requested format is CF_METAFILEPICT.

My app is not reading specifically the CF_METAFILEPICT. It reads the system variables Clipboard and ClipboardAll. It also retrieves some specific formats (like HTML or RTF) using the WinClip library. But it never gets the CF_METAFILEPICT format.

It would be great if @lexikos or another AHK insiders could take a look at it. Does the AHK runtime retrieve the CF_METAFILEPICT format when building ClipboardAll?

TIA

[flyingDman]

As you and @kon earlier stated, the problem here is Excel and not AHK. I guess Excel tries to take a snapshot image of the range and that picture gets too large after 64 rows. Why this manifest itself using OnClipboardChangeand not otherwise?. I don't know.
You can avoid the issue with this:

Code: Select all

#Persistent

OnClipboardChange("ClipChanged")

ClipChanged(Type)
	{
	try ComObjActive("excel.application").DisplayAlerts := False
	ClipSaved =
	ClipSaved := ClipboardAll
	ClipWait,5,1
	try ComObjActive("excel.application").DisplayAlerts := True           ; not sure this is needed
	}

I guess this post should be moved to the regular V2 section. This is not a General Discussion matter.

[JnLlnd]

Thanks for your reply @flyingDman.

As you and @kon earlier stated, the problem here is Excel and not AHK. I guess Excel tries to take a snapshot image of the range and that picture gets too large after 64 rows.

Yes, I knew. My goal was to find out what triggers this error message when the Clipboard is accessed by a script.

You can avoid the issue with this:

Thanks for this solution. I noticed that the .DisplayAlerts := True must be after any access to the Clipboard. For example, in this piece of v1.1 code, the .DisplayAlerts must stay disabled until the formats has been collected by the WinClip function:

Code: Select all

#SingleInstance Force
#requires AutoHotkey v1.1

#Persistent

#Include %A_ScriptDir%\Lib\WinClipAPI.ahk ; include this first (https://www.autohotkey.com/boards/viewtopic.php?t=29314)
#Include %A_ScriptDir%\Lib\WinClip.ahk

OnClipboardChange("ClipboardContentChanged")

return

ClipboardContentChanged(intClipboardContentType)
{
	try ComObjActive("excel.application").DisplayAlerts := False
	ClipSaved =
	ClipSaved := ClipboardAll
	ClipWait,5,1
	Sleep, 200
	
	o_Formats := WinClip.GetFormats()
	strFormats := intClipboardContentType
	for intFormat, oItem in o_Formats
		strFormats .= " | " . oItem.Name
	
	try ComObjActive("excel.application").DisplayAlerts := True           ; not sure this is needed
	
	MsgBox, %strFormats%
}

I guess this post should be moved to the regular V2 section. This is not a General Discussion matter.

Agreed. I did not expect to see code for a solution when asking my question. This could be move to "Ask For Help" section (v1.1 or v2). In anyway, I'll post a link to this thread in the two other threads.

[JnLlnd]

try ComObjActive("excel.application").DisplayAlerts := True ; not sure this is needed

To answer your question: yes, this is needed. If you don't make DisplayAlerts true, XL front-end won't display normal user alerts. For example, when you exit XL, it won't prompt you to save your workbook if you have unsaved changes.

[lexikos]

IIRC, Clipboard reads only the CF_TEXT or CF_UNICODETEXT formats.

ClipboardAll reads all formats returned by EnumClipboardFormats (with limited exceptions), by design. In order to save everything on the clipboard, the data must be retrieved. Excel presumably uses delayed rendering, and only displays the prompt when another application attempts to retrieve the data. As far as I am aware, there is no way to determine whether data on the clipboard is delay rendered, and even if there was, the data needs to be "rendered" for ClipboardAll to save it.

// EnumClipboardFormats() retrieves all formats, including synthesized formats that don't
// actually exist on the clipboard but are instead constructed on demand. Unfortunately,
// there doesn't appear to be any way to reliably determine which formats are real and
// which are synthesized (if there were such a way, a large memory savings could be
// realized by omitting the synthesized formats from the saved version).

[JnLlnd]

> IIRC, Clipboard reads only the CF_TEXT or CF_UNICODETEXT formats.

That's true. The issue only occurs with ClipboardAll.

> Excel presumably uses delayed rendering, and only displays the prompt when another application attempts to retrieve the data.

Thank you for this explanation. I did not know about delayed rendering. This explains a lot of errors I got while using XL and copying large number of cells.

I'll continue my tests to see how (or if) I can get a copy of large Clipboard data without displaying errors.