0

I have an Excel VSTO add-in which copy/pastes shapes from one source worksheet to a destination worksheet (both in same workbook). The destination worksheet contains data entries (one row per entry). A routine performs the following, for each row:

  1. Get data
  2. Calculate X-Y position based on data
  3. Copy shape from source worksheet (shape name depends on data)
  4. Paste shape in destination worksheet
  5. Move to calculated X-Y position

The routine works fine on my two testing computers for up to 300 rows (did not try for more). A client is having problems with all their computers, for "large" data tables. The code breaks at some row between 100 and 130, but to a different row each time the same routine is run on the same file (it is kind of random).

OpenClipboard Failed (Exception from HRESULT: 0x800401D0 (CLIPBRD_E_CANT_OPEN))

at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)

at Microsoft.Office.Interop.Excel.Shape.Cut()

at ExcelAddIn.MyCode

I believe the routine breaks at step 4, and suspect the clipboard stack might get overwhelmed? I read here this HResult may refer to race conditions, but in my case we are trying to cut, so the shape is not yet inside the clipboard.

Ama
  • 1,373
  • 10
  • 24
  • Are there any event in the workbook. Because it is random, I suspect that you are using the selected shape for the copy and for some reason the shape is not active. It looks like you are copying the shape to the clipboard and the clip board is empty. And event could remove the selection for the object. – jdweng Mar 03 '20 at 13:51
  • The source shape is accessed by its name, I never use Selected. Step 3 does: TempShape = SourceSheet.Shapes(Name).Duplicate; TempShape.Cut – Ama Mar 03 '20 at 14:08
  • Is SourceSheet valid. Did you try TempShape = Sheet("Name").Shapes(Name).Duplicate; Maybe try a VBA script to see if same random error occurs. I've used to be very good with the interop excel, but got tired of tackling all the errors and decided to move back to VBA. Beside the speed with interop was very slow. – jdweng Mar 03 '20 at 14:28
  • Yes, SourceSheet is valid: the shape is duplicated (I can see it in SourceSheet), TempShape is also valid (I always test `String s = TempShape.name` in a `Try ... Catch` to make sure the reference was not lost). I am now investigating on this thread: http://www.vbforums.com/showthread.php?602934-RESOLVED-Error-When-macro-copies-too-much-times – Ama Mar 03 '20 at 14:35
  • Debugging VSTO is indeed painful.. It also does wrap so many of the .Net features, which makes a good 10% of the framework documentation not valid under VSTO environment.. – Ama Mar 03 '20 at 14:37
  • The clipboard is not great for high-speed operations like this. As you note, race conditions can appear. For instance, if you look at Microsoft's own implementation of [System.Windows.Clipboard](https://referencesource.microsoft.com/#PresentationCore/Core/CSharp/System/Windows/Clipboard.cs,effa85bd9e8468ae), even they have retry logic. I have two suggestions - 1) either purposefully slow it down, or 2) use Excel's AddShape method to add shapes that are equal to the one you find - without using the Clipboard. – Alexander Høst Mar 28 '20 at 16:24
  • Thanks for your help Alexander. I noted I am now having the same bug with my Windows 10 testing machine (for as little as 40 rows), but never triggers with the Windows 7 testing machine. I have now added a `Try ... Catch` block around the operation, and offer the user to [Retry/Skip]. Retry never works, Skip allows to keep going with the remainder without throwing exceptions. I will update my question to reflect these findings. – Ama Mar 28 '20 at 16:28
  • As per your suggestions: 1) good idea, I will check if adding a sleep(100ms) prior to each Copy/Paste will help. 2) that is not possible, as the purpose of copying from another sheet is that the user can format these shapes to their liking before seeing them ploted on the chart. – Ama Mar 28 '20 at 16:31

0 Answers0