vba 关闭文件时Excel VBA错误“图片太大，将被截断”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18880094/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Error "The picture is too large and will be truncated" when closing file
提问by Shari W
I have been working on this project for a long time and am suddenly getting a new error whenever I close my Excel file. I get the error twice "The picture is too large and will be truncated." There is no picture in my file. I am pasting formats.
我已经在这个项目上工作了很长时间，每当我关闭我的 Excel 文件时，都会突然收到一个新错误。我收到两次错误“图片太大，将被截断”。我的文件中没有图片。我正在粘贴格式。
This seems to be one of the Excel "Unsolved Mysteries".
这似乎是 Excel 的“未解之谜”之一。
I am using MS Office Professional Plus 2010 on Windows 7.
我在 Windows 7 上使用 MS Office Professional Plus 2010。
I have researched this and tried the following:
我对此进行了研究并尝试了以下方法：
- Deleted all %temp% files
- Ran CCleaner
- Set CutCopyMode = False after all paste special (formats)
- Went to add/remove programs and reconfigured Office to stop the Clip Organizer from running. (Control Panel\Programs\Programs and Features -> MS Office Professional Plus 2010 -> Change -> Add or Remove Features -> Office Shared Features -> Clip Organizer -> Not Available, etc.)
- Rebooted
- 删除了所有 %temp% 文件
- 冉CCleaner
- 在所有粘贴特殊（格式）之后设置 CutCopyMode = False
- 去添加/删除程序并重新配置 Office 以阻止剪辑管理器运行。（控制面板\程序\程序和功能 -> MS Office Professional Plus 2010 -> 更改 -> 添加或删除功能 -> Office 共享功能 -> 剪辑管理器 -> 不可用等）
- 重新启动
None of that helped, so I narrowed down the source of the problem by commenting out function and subroutine calls, running the program, saving and then pressing "x" to close. I did this until I found the right sub. Then I commented out all the lines of the sub and added them back in one logical chunk at a time until I found the problem area. Here it is:
这些都没有帮助，所以我通过注释掉函数和子程序调用、运行程序、保存然后按“x”关闭来缩小问题的根源。我一直这样做，直到我找到了合适的潜艇。然后我注释掉 sub 的所有行并将它们一次添加到一个逻辑块中，直到找到问题区域。这里是：
' *********** APPLY BASIC ROW FORMATTING FROM TEMPLATE ***********
' Copy basic row formatting from template and paste over all rows
wksTemplate.Rows(giHEADER_ROW + 1).Copy
myWS.Rows(lFirstRow & ":" & lLastRow).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
The paste contains formatting only - colors, borders, number formats, wrapping etc. It probably pastes on a range of 200 rows on average. I have not changed these 3 lines of code in months. Why now?
粘贴仅包含格式 - 颜色、边框、数字格式、换行等。它可能平均粘贴 200 行。我已经几个月没有更改这 3 行代码了。为什么现在？
Has anyone solved this mystery?
有没有人解开这个谜？
Thanks, Shari
谢谢，莎莉
回答by Simon
I got this error after copying a range and then using a set of pastespecial calls:
复制范围然后使用一组 pastespecial 调用后，我收到此错误：
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteValuesAndNumberFormats
.PasteSpecial xlPasteFormats
solution was to copy an empty cell and pastespecial xlvalues back into itself:
解决方案是复制一个空单元格并将特殊的 xlvalues 复制回自身：
' to avoid the message on closing the book - "picture is too large and will be truncated", copy and paste a singe empty cell
ThisWorkbook.Worksheets(1).Cells(1, 1).Copy
ThisWorkbook.Worksheets(1).Cells(1, 1).PasteSpecial xlValues
' clear clipboard
Application.CutCopyMode = False
回答by Patrick Lepelletier
I sometimes have the same problem as you, but i have many pictures in my files... Also sometimes i have slow down (open/close or just standard calculations(menu popup...)). Usually when i close the workbook, and reopen it, it works fine again.
我有时和你有同样的问题，但我的文件中有很多图片......有时我会变慢（打开/关闭或只是标准计算（菜单弹出......））。通常当我关闭工作簿并重新打开它时，它又可以正常工作了。
I have maybe some answer, not sure if any help : Do you use Global variables ?
我可能有一些答案，不确定是否有帮助：您使用全局变量吗？
for example, in module 1:
例如，在模块 1 中：
Option Explicit
Public BigObject as AnyBigSizeType
Sub xxx() 'code following
try to avoid using global variables, usually its a mess and not even usefull.
尽量避免使用全局变量，通常它是一团糟，甚至没有用。
Also, just to be safe, try in the immediate window : activesheet.pictures.delete (or even activesheet.shapes.delete , but this one also deletes comments and other stuff...)
此外，为了安全起见，请在直接窗口中尝试： activesheet.pictures.delete （甚至 activesheet.shapes.delete ，但这个也会删除评论和其他内容......）
