Saturday, 27 November 2010

Skip Clipboard during VBA.. operation

Recently i came across an error in Excel 2007 as

"Picture is too large and will be truncated."

Upon looking into the issue, i found that leaving stuff on the clipboard, used during the copy and paste operations within VBA was the culprit. This error occurred when the file was about to be closed by the user.
                                After digging in to many portals and unsuccessful attempts to resolve the error by clearing the clipboard via VBA, I found one way to fix the code by redesigning my VBA codes to skip the use of copy and paste and stick with the range based value transfers. Which later i realised is more robust and better way of doing things.

The simplest of the solutions to completed the copy and paste task is

DestinationRange.Value = SourceRange.Value
'with the format code later.

And the solution for the elimination of formulas and paste as values could be gained by

SelectionRange.Formula = SelectionRange.Value

As the office clipboard is now provided in task panes section its quite unclear as of now which solutions can clear the clipboard in VBA.

And to add upon the article
Application.CutCopyMode = False 
doesnt work to clear the office clipboard.

No comments: