Selection.PasteExcelTable not working correctly the first time it is called

Selection.PasteExcelTable not working correctly the first time it is called

• Tom Heinzmann

  Hi Angelos,

  It seems like this behavior is happening because Excel is recieving the last parameter to Select.PasteExcelTable function, named "RTF" as true even though you are passing it as false.

  First of there is an underlying bug in Office (or design!) by wichi if you pass RTF parameter as true then you get these weird tables. I tried this out in a VBA macro. Add the following VBA macro to you document:

  Private Sub Document_Open()
  Me.Application.CommandBars("Standard").Controls("&Paste").OnAction = "MyPaste"
  End Sub

  Sub MyPaste()
  ''MsgBox ("My Paste")
      Me.Application.Selection.PasteExcelTable _
          LinkedToExcel:=True, _
          WordFormatting:=True, _
          RTF:=True  '' <<<< Try both true and false
  End Sub

  Now copy your cells from Excel and click on the Paste icon in the toolbar (not menu). You will see the same bad behavior every time you click Paste. So, it seems like if you try to paste the table in Rich Text Format, Word screws it up and you lose columns.

  So, thats the underlying Word bug. But you were passing RTF as false, so, why was word thinking that its true and that too only the first time you call PasteExcelTable

  I investigated this quite a bit and it seems like if you call PasteExcelTable method from a VBA macro you always end up passing the correct value for RTF parameter. Even from a C++ COM addin everything is fine. But if you call this method through VSTO customization or through a managed COM addin you always end up passing true on the first call.

  So, this is not a VSTO specific bug but rather an Office or CLR interop bug. I'll open a bug with them and post an update here if I find any relevant information from them.

  Thanks for reporting this bug. Very weird and interesting!

  
  
  

• XGR131

  Hi Apurva,

  Thank you for taking the time to look into this. It is good to hear that this issue has been resolved in Office 12, although I would have prefered some sort of workaround for Office 11 as well, as most of the time using a macro when implementing a solution in VSTO is not an option.

  Thank you

  Angelos

  
  
  

• Kuolong

  Hi Apurva,

  Thank you for looking into this and your detailed reply. What you say makes sense, but did you notice something even more weird Did you see that if in the handler of the button's Click you call the PasteExcelTable 10 times you are going to get it 10 times with the wrong format After that, with every click, you are going to get the tables 10 times in the correct format.

  This might make the workaround of calling it twice in a row in our code not work. I will let you know if does or not.

  Thank you

  Angelos

  
  
  

• Shrad

  Hi Jon :-)

  Word is probably not able to keep up with

  • laying out the tables
  • updating the scratch and temp files

  There are known issues with creating and formatting lots of new tables, so the factors involved are probably very similar with what you're seeing. For the known issues, there are two key things you can do to stabilize Word:

  1. Clear the Undo buffer at regular intervals
  2. Do a document.Save at regular intervals

  In your case, I think (2) is the most important. And to it with background save option OFF. That will give Word time to catch up.

  As an alternative method to copying and pasting, I'd very much recommend creating LINK fields in the document that refers to the exact range you want to import. If you don't want to retain a dynamic link, the field can be unlinked after it's been created, resulting in a static table. This has some advantages over copying and pasting

  1. You're not interfering with the user's Clipboard
  2. There's less chance you're going to be caught up by things running asynchronously
  3. It should be faster

  If you have time to test and incorporate any of this in your project, I'd be interested in your comparative results.

  
  
  

• Jouni79

  Hey Angelos,

  You can always have a VBA macro in a document that has a VSTO solution attached. A simple macro like one just calling PasteExcelTable should not cause any problem.

  In fact, there are valid scenarios where you can only do certain things to the application OM using a VBA macro e.g. override built-in menus. These situations arise because of close integration between VBA and Office apps. There are many Office OM methods that accept the name of a macro as callback, not a function pointer.

  Of course, being forced to use VBA macros when almost all your code is .NET is not a happy situation. It's is worse that in your case, the need to use VBA arises because of a bug. But I found that this bug is not of a severity to be addressed in a patch.

  Thanks. Hope that helps.

  
  
  

• Tech Guy

  Thank you Apurva. We have decided to go ahead with using a one line VBA macro just for pasting an Excel table as a link and it seems to work.

  The only issue I can see with this is the inability to pass a "Range" object from VSTO to the macro. You can't really pass the "start" and "end" values of the range either, as the range might exist within a textbox. Instead, the macro uses the Application.Selection which means that before calling the macro you are forced to select the range you want to paste into.

  Thank you for your help

  Angelos

  
  
  

• GeertG

  Hi Angelos,

  I looked into this bug and it seems to be fixed in Office 12.

  The easiest workaround fir Office11 VSTO customization I can think of is to make the call to Selection.PasteExcelTable method from a VBA macro in your document. Your VBA macro will contain only one line of code calling PasteExcelTable.

  Following post describes how you can use Application.Run method to call VBA macros in the document from your VSTO code (although in context of a different problem): http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=380414&SiteID=1

  This should solve your problem because this bug only occurs in Office11 when Selection.PasteExcelTable is called from managed code.

  Hope that helps. Thanks for reporting the bug.

  
  
  

• Mike Henrickson

  Cindy -

  Thanks for following up with a clear procedure to follow. I was going to do it this way, I make extensive use of manual steps that I then replicate in code, either via the macro recorder or by generating things like this link string. I have found though that man users don't think of this, then ask questions about links or link strings, or even about the object model, that can be readily answered by doing it first manually.

  Jon Peltier, Microsoft Excel MVP
  http://PeltierTech.com

  
  
  

• bijie85

  You can actually pass the Range object from VSTO to the macro. Passing objects to macros may again be necessary in some situations such as creating used defined functions in Excel.

  The Range object to which you have a reference in VSTO is actually a Runtime Callable Wrapper (RCW) created by the CLR for a COM interface pointer.  You can pass this RCW back to a COM method and the CLR takes care of unwrapping the RCW and passing the "raw" interface pointer to the method.

  If you look at the signature of the Application.Run method in Word OM, you will see that it can take upto 30 arguments. In C#, it's one of the ugliest method signatures I have ever laid eyes upon :-). The signature can also accomodate any return value from the VBA macro (actually any return value that you can store in a Variant e.g. an Office object).

  object missing = System.Type.Missing;
  Word.Range rng = this.Application.Selection.Range;
  object tempRng = (object)rng;

  object retVal = this.Application.Run("MyMacro", ref tempRng, ref missing, ref missing,
      ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing,
      ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing,
      ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing,
      ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);

  You should define a VBA macro called MyMacro which takes 1 parameter of type Word.Range. I defined the macro in a Module in VBA .

  
  
  

• Sarmad

  I was so certain passing objects would not be possible, I did not even bother trying it. Lesson learnt, thank you very much for your help.

  Angelos Petropoulos

  
  
  

• Mehdi311ggg

  I've just spent about six hours investigating this behavior. I'm automating Word from Excel using pure Office VBA, copying ranges in Excel and pasting into a long Word document. This is Office 2003, Windows XP SP2. Office 2007 is not a viable solution, and will not become viable in the foreseeable future.

  I have found that .PasteAsNestedTable fails the first time it is called. I use an error trap, and if this method throws an error, I paste the table using .PasteExcelTable(False, False, False). However, after some time running (50 or 60 tables) with complicated tables (10 rows, 12-15 columns, mixed combinations of merged cells), errors begin to occur in other lines which are formatting the pasted table. It seems like a timing problem, but DoEvents is not much help. Eventually Word hangs and must be terminated. The error has nothing to do with the code thinking RTF is true, as the formatting is the same as .PasteExcelTable(False, False, Flase), which has the third argument RTF = false.

  I then implemented .PasteExcelTable(False, False, False) for all of the paste operations. Like .PasteAsNestedTable, this method renders the Excel formatting accurately, and like .PasteAsNestedTable, this method begins to break down after processing a large number of complicated tables. Often the error is that the method itself is not available, or simply that the method failed; DoEvents is of limited help. It's somewhat better in that Word never gets to the point of hanging, but it is not reliable enough to release to my clients.

  I finally tried .PasteExcelTable(False, False, True), which uses RTF rather than HTML to transfer the formatting into Word. This method runs reliably without the nagging little errors, but it is not capable of rendering the merged cells reliably. In fact, this inability to format correctly led me at first to reject RTF formatting. I am having to do additional coding to remerge the cells in Word, but it is the price I pay for error free pasting of the tables into Word.

  If my tables are relatively simple, say, ten rows but only a half dozen columns and fewer merged cells, then .PasteExcelTable(False, False, False) works adequately (so does .PasteAsNestedTable except for the failure on the first call). However, I cannot count on this method.

  - Jon
  -------
  Jon Peltier, Microsoft Excel MVP
  Tutorials and Custom Solutions
  http://PeltierTech.com
  _______
  

  
  
  

• Grismath

  One test is all it took. UndoClear prevented any of the errors, and also speeded up the process substantially. The Undo stack in Excel is fragile, always resetting itself (for example, on saving a document), so I didn't think of it.

  Many thanks.

  
  
  

• lprigmore

  Hi Jon

  Glad the Undo.Clear works for you :-)

  For the sake of completeness, and in case you need it in the future, the basic steps for creating links (to anything):

  • First create the linked item in the UI, either using Edit/Paste Special or Insert/Object (whatever is appropriate)
  • If what you're inserting is a graphical object (as a chart would be), make sure the text wrap formatting is "inline with text"
  • Alt+F9 will show the field code (like Excel formula bar ) that manages the link. From this you get the basic syntax you require
  • In the object model, use the Fields.Add method. You don't have to pass a field type if you want to use the entire string you picked up from the field as the field's Text parameter. Example
    Word.Field fld = doc.Fields.Add(ref objRange, ref missing, ref objText, ref missing);
  • To unlink the field, use the unlink method on the field object

  
  
  

• Christian Liensberger

  Hi Cindy -

  This is good advice. The code already saves the document after every ten inserted charts. I will try your suggestions in this order:

  1. Document.UndoClear ' after each inserted chart

  2. Creating links for each chart, then breaking links.

  Where can I read about generating this kind of link I will start with Help and Google, but if you know of a more direct source, I'm grateful to save a little time. I will keep you posted on the results.

  
  
  
• Selection.PasteExcelTable not working correctly the first time it is called