none
Creating windows with entries inside RRS feed

  • Question

  • Hello everyone,

    I would like to create a plugin for excel that would allow me to select some cells from one window and then after a single button click open that selection in a new window. I would also like to have the windows automatically resize in order to use space efficiently.

    So far I have implemented the new window creation part, but I can not do two things: copy selection from one window to another, automatically resize window. I must say, I managed to copy slelction by using clipboard. With the "clipboard" approach I have to do an extra step of holding CTRL+C before clicking the button that creates new window. Overall I don't want to use the clipboard, I want to copyselection using some other way.

    Do you think anyone can assist me? 

    Here is the implementation that relies on CTRL+C:

    ____________________________________________

    ____________________________________________

    //Positions of windows, used for positioning new window

              PosX = Globals.ThisAddIn.Application.ActiveWindow.Left;

                PosY = Globals.ThisAddIn.Application.ActiveWindow.Top;

                Width = Globals.ThisAddIn.Application.ActiveWindow.Width;

                Height = Globals.ThisAddIn.Application.ActiveWindow.Height;

     

     

             //The first window

               Excel.Workbook excelbk =  Globals.ThisAddIn.Application.Workbooks.Add(Type.Missing);

           //The sheet that information is copied from

               Excel.Worksheet sheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;

    //The part that is supposed to copy.I tried all kinds of Copy method, no result.

     

               sheet.UsedRange.Copy();  //Comment this line to enable Ctrl+C functionality.

     

     

    //second window

     

                Excel.Window wnd2 = (Excel.Window)excelbk.Windows[1];

                Excel.Worksheet sheet2 = (Excel.Worksheet)excelbk.Worksheets["Sheet1"];

              //Positioning new window

                wnd2.Height = (double)250;

     

                wnd2.Width = 350;

     

                wnd2.Left = PosX + 350;

     

                double widthP = Convert.ToInt16(PosX) + 150;

     wnd2.Top = PosY;

     

    //pasting which only works with Ctrl-C

                sheet2.Paste(Type.Missing, Type.Missing);

     

    Wednesday, February 8, 2012 1:45 AM

Answers

  • sheet.UsedRange.Copy();

    is not the right way to copy as what I understand from your snapshot. You might want to specify the exact range you want to copy or perhaps a "Selection" as Ed suggested?

    I would also recommend the copy and paste method Ed suggested. In the button click event use this code for example.

                xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                xlWorkSheet2 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
                xlrng1 = xlWorkSheet1.get_Range("A1:B1"); // ~~>Source Range
                xlrng2 = xlWorkSheet2.get_Range("A1"); // ~~> Destination Range
                xlrng1.Copy(xlrng2);




    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    Wednesday, February 8, 2012 7:04 AM
    Moderator

All replies

  • ActiveSheet.Selection.Copy ' copies selected cells to clipboard

    Sheet2.Paste Destination:=Range("A1") ' pastes clipboard contents to new sheet starting at cell A1


    Ed Ferrero
    www.edferrero.com

    Wednesday, February 8, 2012 2:03 AM
    Answerer
  • AFAIK, when the button is pressed the contents of the clipboard are reset by default. Can I simply use a variable to temporarily hold the contents of the selection and then copy the contents to the new window?

    Thank you for your input!

    Wednesday, February 8, 2012 3:15 AM
  • sheet.UsedRange.Copy();

    is not the right way to copy as what I understand from your snapshot. You might want to specify the exact range you want to copy or perhaps a "Selection" as Ed suggested?

    I would also recommend the copy and paste method Ed suggested. In the button click event use this code for example.

                xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                xlWorkSheet2 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
                xlrng1 = xlWorkSheet1.get_Range("A1:B1"); // ~~>Source Range
                xlrng2 = xlWorkSheet2.get_Range("A1"); // ~~> Destination Range
                xlrng1.Copy(xlrng2);




    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    Wednesday, February 8, 2012 7:04 AM
    Moderator