Click here to Skip to main content
15,936,876 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
The follow line of code...
C#
wbkSource.Sheets(strFromSht).Copy(wbk.Sheets(1));

getting error
Error	2	Non-invocable member 'Microsoft.Office.Interop.Excel._Workbook.Sheets' cannot be used like a method.	 
Can someone please help me. My intent is to copy a worksheet to another
workbook
C#
private String strWorkBookLocation = "C:\\PivotFiles\\source.xls";
       private String strWbkLocation      = "C:\\PivotFiles\\temp.xls";
       private String cstrPivotTableSheet = "Pivot Table";
       private String strFromSht          = "Source";

               Excel.Application app;
               Excel.Workbook wbk;
               Excel.Worksheet sht;
               Excel.Worksheet sh;
               Excel.Workbook wbkSource;
               DataSet dSet = new DataSet();
               DataGrid dg = new DataGrid();
               int y = 2;
               int x = 0;

               try
               {


                   FetchMFRData(ref dSet, "12/24/2010", "12/24/2010");
                   dg.DataSource = dSet;
                   dg.DataBind();
                   FileStream fs = new FileStream(strWorkBookLocation, FileMode.Create);
                   StreamWriter tw = new StreamWriter(fs);
                   HtmlTextWriter writer = new HtmlTextWriter(tw);
                   dg.RenderControl(writer);
                   writer.Flush();
                   writer.Close();
                   tw.Close();
                   fs.Close();

                   app = new Excel.Application();
                   app.DisplayAlerts = false;

                   wbkSource = app.Workbooks.Open(strWorkBookLocation,
                               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                               Type.Missing, Type.Missing);
                   wbk       = app.Workbooks.Open(strWbkLocation,
                               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                               Type.Missing, Type.Missing);

                   // This loop is used for deleting all sheets in the workbook
                   // except the Pivot table sheet.  Generally the only reason
                   // another sheet may be present is if there was an error
                   // during another run and the source sheet didn't get deleted.

                   foreach (Excel.Worksheet sheet in wbk.Sheets)
                   {
                       if (sheet.Name.Equals(cstrPivotTableSheet) == false)
                           sheet.Delete();
                   }

                  wbkSource.Sheets(strFromSht).Copy(wbk.Sheets(1));


               }
               catch (Exception ex)
               {
               }
           }
       }
Posted
Updated 29-Dec-11 10:32am
v2

1 solution

What happens if you try:
C#
Excel.Worksheet sh_temp = wbkSource.Worksheets(strFromSht);
sh_temp.Copy(wbk.Sheets(1));
 
Share this answer
 
Comments
Member 7969139 30-Dec-11 0:33am    
Hello Mike. I appreciate your help with this issue. I'm very frustrated that I cannot find the right documentation so I don't have to keep posting everytime I get stuck. Could you point me to the proper place to go for help on the coding syntax issues I'm experiancing. Should I be looking under Excel VSTO or something else?
Wendelius 30-Dec-11 1:49am    
Refarding the link, I'm using this: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel(v=office.14).aspx[^]

About the error, try definening also a temp variable for sheets (type Microsoft.Office.Interop.Excel.Sheets). What I'm thinking is that if the return values are objects, then the methof invocation may fail unless the object is casted to proper type.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900