locked
Copying Multiple Worksheets Into New Workbook RRS feed

  • Question

  • I have a master workbook where sometimes I need to copy 2 - 3 worksheets from that master book into a new book.  I have gotten close to acheiving this, but my problem is my code is copying each sheet to it's own workbook instead of copying both sheets to a new workbook.

    string[] sheetNames;
    sheetNames = new string[2]
    {
    "Sheet1",
    "Sheet2",
    };
    foeach (string sheet in sheetNames)
    {
    ws = wb.Sheets[sheet];
    if (ws != null)
    {
    ((Worksheet)ws).Copy();
    }
    }

    How can I adapt that to copy both Sheet1 and Sheet2 to the same destination workbook?

    Saturday, July 6, 2013 8:07 PM

Answers

  • In the above code i did not use the .Get_Item to fetch the list of Sheets in your from the whole workbook.

    string[] sheetNames; sheetNames = new string[2] { "Sheet1", "Sheet2", };

    ws =wb.WorkSheets;

    ((Worksheet)ws).get_Item(sheetNames)).Copy();


    Vish Mishra

    • Marked as answer by toptierdawg03 Tuesday, July 9, 2013 1:07 PM
    Sunday, July 7, 2013 9:52 AM

All replies

  • Hi,

    To copy Sheet1 and Sheet2 to another new workbook you can use the below code:

    Sheets(Array("Sheet1", "Sheet2")).Copy

    you first need to make an array of sheets in for loop and then outside of "for loop" use the above code:

    sheets(ArrayOfSheets).Copy



    Vish Mishra

    Saturday, July 6, 2013 9:35 PM
  • Hi,

    To copy Sheet1 and Sheet2 to another new workbook you can use the below code:

    Sheets(Array("Sheet1", "Sheet2")).Copy

    you first need to make an array of sheets in for loop and then outside of "for loop" use the above code:

    sheets(ArrayOfSheets).Copy



    Vish Mishra

    I am not following you....could you possibly modify my code to show with your examples so I can better understand?
    Saturday, July 6, 2013 10:26 PM

  • string
    [] sheetNames; sheetNames = new string[2] { "Sheet1", "Sheet2", }; ws = wb.Sheets[sheetnames];

    ((Worksheet)ws).Copy();


    Vish Mishra

    Saturday, July 6, 2013 10:56 PM

  • string[] sheetNames; sheetNames = new string[2] { "Sheet1", "Sheet2", }; ws = wb.Sheets[sheetnames];

    ((Worksheet)ws).Copy();


    Vish Mishra

    When it hits the ((Worksheet)ws.COpy(); line I get the following debug error:

    System.InvalidCastException: Unable to cast COMobject of Type 'System._ComObject' to interface type 'Microsoft.Office.Interop.Excel.WOrksheet'.  This operation failed because the QueryInterface call on the COM component for the interface with IID '(000208D8-0000-0000-C000-00000000000046)
     failed to the following error: No such interface supported

    Sunday, July 7, 2013 2:53 AM
  • In the above code i did not use the .Get_Item to fetch the list of Sheets in your from the whole workbook.

    string[] sheetNames; sheetNames = new string[2] { "Sheet1", "Sheet2", };

    ws =wb.WorkSheets;

    ((Worksheet)ws).get_Item(sheetNames)).Copy();


    Vish Mishra

    • Marked as answer by toptierdawg03 Tuesday, July 9, 2013 1:07 PM
    Sunday, July 7, 2013 9:52 AM
  • In the above code i did not use the .Get_Item to fetch the list of Sheets in your from the whole workbook.

    string[] sheetNames; sheetNames = new string[2] { "Sheet1", "Sheet2", };

    ws =wb.WorkSheets;

    ((Worksheet)ws).get_Item(sheetNames)).Copy();


    Vish Mishra

    On your last line of code, I get a compile error of
    Microsoft.Office.interop.Excel.Worksheet doesnot contain a definition for 'get_Item' and no extension method 'get_Item' accepting a first argument of type Microsoft.Office.Interop.Excel.Worksheet could be found?

    EDIT -- 

    If it makes a difference I have ws declared as such

    object ws = null;

    • Edited by toptierdawg03 Tuesday, July 9, 2013 12:48 PM Added WS Decleration
    Tuesday, July 9, 2013 12:47 PM
  • I got it working, it just took me a slight modification.

    I did below

    Excel.Application oXL;
    oXL.ActiveWorkbook.Worksheets.get_Item(sheetNames).Copy();

    Tuesday, July 9, 2013 1:07 PM