none
How do I force Excel formulas to be included when using Range.Copy() RRS feed

  • Question

  • Hi all;

    I am trying to us Control.DoDragDrop() to drag a worksheet and drop it in another worksheet. THis is for an app that has a bunch of little XLSX doclets that you can drag/drop to place in a file. This is all done in an Excel COM API AddIn (ie not VSTO).

    What I'm doing is reading in the doclet and copying it as an additional worksheet in the destination workbook. I then copy the UsedRange of that worksheet to the clipboard. Then call Clipboard.GetDataObject() for the object to pass to DoDragDrop().

    I inspected the UsedRange object in the debugger and the cells are set to the formula. If I comment out the call to DoDragDrop (because on return it has cleared the clipboard), then the clipboard can do a paste that includes the formulas.

    It all works great except the formulas are dropped and instead it is the calculated value.

    How can I get it to bring the formulas across too? Using DoDragDrop (it does work using copy/paste)?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Tuesday, March 21, 2017 6:07 PM

All replies

  • I am trying to copy a range from one worksheet to another (different workbooks in different Application objects). It all works great except the formulas are being dropped.

    What I'm doing is a little different, I copy the first to the clipboard, and then use the clipboard to pass to a Control.DoDragDrop() call.

    So code to get the object is:

    			excelApp = new Application();
    			excelApp.DisplayAlerts = false;
    			workbooks = excelApp.Workbooks;
    			workbook = workbooks.Open(srcFile);
    			sheet = (Worksheet) workbook.ActiveSheet;
    			sheet.UsedRange.Copy(Type.Missing);
    
    			data = Clipboard.GetDataObject();

    After calling DoDragDrop(), it drops formatted cells where I drop them (Yah!). But the formula in one cell is replaced with the literal value.

    If I go to Paste in Excel, I can paste text, formatted text, or a link. But no way to pass with the formulas.

    I also try calling the following after - same thing, formatted but no formula.

    ((Worksheet)autoTagWrkbk.ActiveSheet).PasteSpecial(XlPasteType.xlPasteFormulas, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);

    The srcFile .xlsx file does have the formula.

    Any ideas? Is there some app level setting that turns off pasting formulas? If so, how do I programmaticly turn that off?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing


    Monday, March 20, 2017 8:41 PM
  • Hi DavidThi808,

    from your overall description I understand that you are not able to copy the formula with data from one workbook to another workbook.

    also I don't know why you are using the clip board to copy data.

    you can refer code below , which able to copy data with their formulas with formatting from one workbook to another.

    private void button1_Click(object sender, EventArgs e)
            {
                string filePath = @"C:\Users\v-padee\Desktop\excel files\book2.xlsx";
                string filePath1 = @"C:\Users\v-padee\Desktop\demo.xlsx";
    
                Microsoft.Office.Interop.Excel.Application xlobj = new Microsoft.Office.Interop.Excel.Application();
    
                Workbook w = default(Workbook);
                Workbook w1 = default(Workbook);
    
                Worksheet s = default(Worksheet);
                Worksheet s1 = default(Worksheet);
    
                Object missing = Type.Missing;
    
                try
                {
                    w = xlobj.Workbooks.Open(filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                    w1 = xlobj.Workbooks.Open(filePath1, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
    
                    s = w.Worksheets.get_Item(1) as Worksheet;
                    s1 = w1.Worksheets.get_Item(1) as Worksheet;
    
                    // it will copy and paste sheet from one to another with formula
                    s.UsedRange.Copy(missing);
                    s1.Paste(missing, missing);
    
                    s1.UsedRange.Formula = s.UsedRange.Formula;
    
                    w.Save();
                    w1.Save();
    
                    w.Close(false, null, null);
                    w1.Close(false, null, null);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
    
                    w.Save();
    
                    w1.Save();
    
                    w.Close(missing, missing, missing);
    
                    w1.Close(missing, missing, missing);
    
                }
            }

    you can try to modify the code as per your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 21, 2017 2:48 AM
    Moderator
  • Update: I've created a new question here that get's to the key need we have. So you can ignore my questions below if there's an answer to my new post.

    Hi Deepak;

    Thank you for your example. Unfortunately two problems. I show it in this test program I wrote.

    1. Calling "s1.UsedRange.Formula = s.UsedRange.Formula" causes some major problems on output. This seems to be dependent on what is already in the dest worksheet.
    2. You have to use the same Application object for the source and dest. This is a giant problem for our use case.

    So two follow-on questions for you:

    1. What's the purpose of assigning the Formula property across?
    2. Is there a way to do this use 2 distinct Application objects?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing


    • Edited by DavidThi808 Tuesday, March 21, 2017 6:09 PM
    Tuesday, March 21, 2017 2:35 PM
  • Oh, and get this message when going between Application objects


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Tuesday, March 21, 2017 2:47 PM
  • In my option, Range.Copy() has included the Excel formulas. If you try Range.PasteSpecial Paste:=xlPasteFormulas, the formula will be copied. Are you able to use Range.PasteSpecial?
        Selection.Copy
        Range("B11").Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    >> Then call Clipboard.GetDataObject() for the object to pass to DoDragDrop().
    Are you able to get formula from Clipboard.GetDataObject()? If not, I would suggest you try to set formula for Clipboard, and then paste the formula.
        Dim DataObj As MSForms.DataObject
        Set DataObj = New MSForms.DataObject
        Dim f As String
        f = ActiveSheet.Range("B9").Formula
        DataObj.GetFromClipboard
        DataObj.SetText (f)
        string1 = DataObj.GetText(1)



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 22, 2017 7:54 AM
  • Hi;

    Yes, Copy() & PasteSpecial() works fine. However, we need to do drag drop to let the user drag the doclet to the place they want it pasted. There's no way to do that with Copy()/PasteSpecial().

    How can we do this using DoDragDrop()?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Wednesday, March 22, 2017 11:44 AM
  • Hi DavidThi808,

    I find that you created a new thread for the same issue.

    to avoid the duplication of the threads , I will merger these two threads.

    you can carry on your conversation on that new thread.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 23, 2017 5:22 AM
    Moderator
  • I am afraid it is impossible. I failed to get the formula from Clipboard object. I think you need to store the formula from UserRange first, and then set the formula to target.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 23, 2017 6:14 AM
  • Hi Edward;

    How can I determine the cells that the drag-drop dropped the clipboard content into?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Thursday, March 23, 2017 12:14 PM
  • What do you mean by this? Do you mean you could not get the content of copied cell? Based on your original code "sheet.UsedRange.Copy(Type.Missing);", you copy UsedRange to clipboard. I think you could loop the UsedRange to get each formula for each cell, and then copy the formulas.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 24, 2017 9:00 AM
  • Hi Edward;

    What I mean is the user does a drag/drop via DoDragDrop(). When that returns the UsedRange from the source worksheet has been dropped (pasted) into the destination worksheet at the location the user dropped them in.

    But the dropped (pasted) cells do not have formulas.

    I need to know where it was dropped so I can now copy/paste to that same location.

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Friday, March 24, 2017 11:27 AM
  • >>I need to know where it was dropped so I can now copy/paste to that same location.

    It is impossible. We could not get formula from Clipboard object.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 27, 2017 3:15 AM