| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What am I doing wrong?
I'm trying to delete a worksheet from a workbook using C# and late
binding. What ever I try, I don't seem to get rid of the sheet. I get no errors or no messages. Below is the basic of the code i try to run. Can anybody please enlighten me? object oExcelApp = null; object oExcelWorkbooks; object oExcelWorkbook = null; object oExcelSheets; object oExcelSheet; object[] Parameters; int numberOfSheets = 0; Type objClassType; // Get the class type and instantiate Excel. objClassType = Type.GetTypeFromProgID("Excel.Application"); oExcelApp = Activator.CreateInstance(objClassType); //Get the workbooks collection. oExcelWorkbooks = oExcelApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, oExcelApp, null); // Open a workbook Parameters = new object[15]; Parameters[0] = @"C:\TestSet.xls"; ; Parameters[3] = 5; // Format = Nothing. Parameters[1] = Parameters[2] = Parameters[4] = Parameters[5] = Parameters[6] = Parameters[7] = Parameters[8] = Parameters[9] = Parameters[10] = Parameters[11] = Parameters[12] = Parameters[13] = Parameters[14] = Type.Missing; oExcelWorkbook = oExcelWorkbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, oExcelWorkbooks, Parameters); oExcelSheets = oExcelWorkbook.GetType().InvokeMember("Worksheets" , BindingFlags.GetProperty, null, oExcelWorkbook, null); numberOfSheets = (int)oExcelSheets.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, oExcelSheets, null); int n; String sheet; Parameters = new object[1]; for (n = 1; n <= numberOfSheets; n++) { Parameters[0] = n; //Get the first worksheet. oExcelSheet = oExcelSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, oExcelSheets, Parameters); sheet = (String)oExcelSheet.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, oExcelSheet, null); if ( String.Compare( sheet, "noleap", true) == 0) { // Delete the sheet! oExcelSheet.GetType().InvokeMember("Delete", BindingFlags.InvokeMethod, null, oExcelSheet, null); } } Parameters = new object[3]; Parameters[0] = Parameters[1] = Parameters[2] = Type.Missing; if (oExcelWorkbook != null) { oExcelWorkbook.GetType().InvokeMember("Save", BindingFlags.InvokeMethod, null, oExcelWorkbook, null); oExcelWorkbook.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, oExcelWorkbook, Parameters); } if (oExcelApp != null) { oExcelApp.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, oExcelApp, null); } |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What am I doing wrong?
I don't use .Net, but..
It looks like you trying to delete the sheet "noleap". What does the True argument to String.Compare indicate ? Case insensitive comparison ? Normally in Excel, when you try to Delete a sheet, there is a warning dialog asking you to confirm the deletion. In VB, you can turn this off with oExcelApp.DisplayAlerts=False resetting it to true when ready. NickHK roups.com... I'm trying to delete a worksheet from a workbook using C# and late binding. What ever I try, I don't seem to get rid of the sheet. I get no errors or no messages. Below is the basic of the code i try to run. Can anybody please enlighten me? object oExcelApp = null; object oExcelWorkbooks; object oExcelWorkbook = null; object oExcelSheets; object oExcelSheet; object[] Parameters; int numberOfSheets = 0; Type objClassType; // Get the class type and instantiate Excel. objClassType = Type.GetTypeFromProgID("Excel.Application"); oExcelApp = Activator.CreateInstance(objClassType); //Get the workbooks collection. oExcelWorkbooks = oExcelApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, oExcelApp, null); // Open a workbook Parameters = new object[15]; Parameters[0] = @"C:\TestSet.xls"; ; Parameters[3] = 5; // Format = Nothing. Parameters[1] = Parameters[2] = Parameters[4] = Parameters[5] = Parameters[6] = Parameters[7] = Parameters[8] = Parameters[9] = Parameters[10] = Parameters[11] = Parameters[12] = Parameters[13] = Parameters[14] = Type.Missing; oExcelWorkbook = oExcelWorkbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, oExcelWorkbooks, Parameters); oExcelSheets = oExcelWorkbook.GetType().InvokeMember("Worksheets" , BindingFlags.GetProperty, null, oExcelWorkbook, null); numberOfSheets = (int)oExcelSheets.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, oExcelSheets, null); int n; String sheet; Parameters = new object[1]; for (n = 1; n <= numberOfSheets; n++) { Parameters[0] = n; //Get the first worksheet. oExcelSheet = oExcelSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, oExcelSheets, Parameters); sheet = (String)oExcelSheet.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, oExcelSheet, null); if ( String.Compare( sheet, "noleap", true) == 0) { // Delete the sheet! oExcelSheet.GetType().InvokeMember("Delete", BindingFlags.InvokeMethod, null, oExcelSheet, null); } } Parameters = new object[3]; Parameters[0] = Parameters[1] = Parameters[2] = Type.Missing; if (oExcelWorkbook != null) { oExcelWorkbook.GetType().InvokeMember("Save", BindingFlags.InvokeMethod, null, oExcelWorkbook, null); oExcelWorkbook.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, oExcelWorkbook, Parameters); } if (oExcelApp != null) { oExcelApp.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, oExcelApp, null); } |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What am I doing wrong?
You are perfectly right, NickHK.
I am trying to delete a sheet named "noleap" and the 'true' argument indicates case insensitive. I tried to turn off the DisplayAlerts property as you suggested, but it didn't do any difference. Here's what i added to my code: Parameters = new object[1]; Parameters[0] = true; oExcelApp.GetType().InvokeMember("DisplayAlerts", BindingFlags.SetProperty, null, oExcelApp, Parameters); NickHK wrote: I don't use .Net, but.. It looks like you trying to delete the sheet "noleap". What does the True argument to String.Compare indicate ? Case insensitive comparison ? Normally in Excel, when you try to Delete a sheet, there is a warning dialog asking you to confirm the deletion. In VB, you can turn this off with oExcelApp.DisplayAlerts=False resetting it to true when ready. NickHK roups.com... I'm trying to delete a worksheet from a workbook using C# and late binding. What ever I try, I don't seem to get rid of the sheet. I get no errors or no messages. Below is the basic of the code i try to run. Can anybody please enlighten me? object oExcelApp = null; object oExcelWorkbooks; object oExcelWorkbook = null; object oExcelSheets; object oExcelSheet; object[] Parameters; int numberOfSheets = 0; Type objClassType; // Get the class type and instantiate Excel. objClassType = Type.GetTypeFromProgID("Excel.Application"); oExcelApp = Activator.CreateInstance(objClassType); //Get the workbooks collection. oExcelWorkbooks = oExcelApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, oExcelApp, null); // Open a workbook Parameters = new object[15]; Parameters[0] = @"C:\TestSet.xls"; ; Parameters[3] = 5; // Format = Nothing. Parameters[1] = Parameters[2] = Parameters[4] = Parameters[5] = Parameters[6] = Parameters[7] = Parameters[8] = Parameters[9] = Parameters[10] = Parameters[11] = Parameters[12] = Parameters[13] = Parameters[14] = Type.Missing; oExcelWorkbook = oExcelWorkbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, oExcelWorkbooks, Parameters); oExcelSheets = oExcelWorkbook.GetType().InvokeMember("Worksheets" , BindingFlags.GetProperty, null, oExcelWorkbook, null); numberOfSheets = (int)oExcelSheets.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, oExcelSheets, null); int n; String sheet; Parameters = new object[1]; for (n = 1; n <= numberOfSheets; n++) { Parameters[0] = n; //Get the first worksheet. oExcelSheet = oExcelSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, oExcelSheets, Parameters); sheet = (String)oExcelSheet.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, oExcelSheet, null); if ( String.Compare( sheet, "noleap", true) == 0) { // Delete the sheet! oExcelSheet.GetType().InvokeMember("Delete", BindingFlags.InvokeMethod, null, oExcelSheet, null); } } Parameters = new object[3]; Parameters[0] = Parameters[1] = Parameters[2] = Type.Missing; if (oExcelWorkbook != null) { oExcelWorkbook.GetType().InvokeMember("Save", BindingFlags.InvokeMethod, null, oExcelWorkbook, null); oExcelWorkbook.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, oExcelWorkbook, Parameters); } if (oExcelApp != null) { oExcelApp.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, oExcelApp, null); } |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What am I doing wrong?
Jon,
What if you just try to delete the sheet directly, with the correct case of the name: Worksheets("NoLeap").Delete All I can think is that your If never evaluates to true. What if you replace you .Delete with a simple MsgBox, just see if it fires. What error handling do you have in place ? Not whatever the .Net equivalent of "On Error Resume Next" ? NickHK P.S. There is NG "microsoft.public.excel.sdk" which seemed more geared to Interop stuff. Doesn't look that busy though. "Jon H" wrote in message oups.com... You are perfectly right, NickHK. I am trying to delete a sheet named "noleap" and the 'true' argument indicates case insensitive. I tried to turn off the DisplayAlerts property as you suggested, but it didn't do any difference. Here's what i added to my code: Parameters = new object[1]; Parameters[0] = true; oExcelApp.GetType().InvokeMember("DisplayAlerts", BindingFlags.SetProperty, null, oExcelApp, Parameters); NickHK wrote: I don't use .Net, but.. It looks like you trying to delete the sheet "noleap". What does the True argument to String.Compare indicate ? Case insensitive comparison ? Normally in Excel, when you try to Delete a sheet, there is a warning dialog asking you to confirm the deletion. In VB, you can turn this off with oExcelApp.DisplayAlerts=False resetting it to true when ready. NickHK roups.com... I'm trying to delete a worksheet from a workbook using C# and late binding. What ever I try, I don't seem to get rid of the sheet. I get no errors or no messages. Below is the basic of the code i try to run. Can anybody please enlighten me? object oExcelApp = null; object oExcelWorkbooks; object oExcelWorkbook = null; object oExcelSheets; object oExcelSheet; object[] Parameters; int numberOfSheets = 0; Type objClassType; // Get the class type and instantiate Excel. objClassType = Type.GetTypeFromProgID("Excel.Application"); oExcelApp = Activator.CreateInstance(objClassType); //Get the workbooks collection. oExcelWorkbooks = oExcelApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, oExcelApp, null); // Open a workbook Parameters = new object[15]; Parameters[0] = @"C:\TestSet.xls"; ; Parameters[3] = 5; // Format = Nothing. Parameters[1] = Parameters[2] = Parameters[4] = Parameters[5] = Parameters[6] = Parameters[7] = Parameters[8] = Parameters[9] = Parameters[10] = Parameters[11] = Parameters[12] = Parameters[13] = Parameters[14] = Type.Missing; oExcelWorkbook = oExcelWorkbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, oExcelWorkbooks, Parameters); oExcelSheets = oExcelWorkbook.GetType().InvokeMember("Worksheets" , BindingFlags.GetProperty, null, oExcelWorkbook, null); numberOfSheets = (int)oExcelSheets.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, oExcelSheets, null); int n; String sheet; Parameters = new object[1]; for (n = 1; n <= numberOfSheets; n++) { Parameters[0] = n; //Get the first worksheet. oExcelSheet = oExcelSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, oExcelSheets, Parameters); sheet = (String)oExcelSheet.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, oExcelSheet, null); if ( String.Compare( sheet, "noleap", true) == 0) { // Delete the sheet! oExcelSheet.GetType().InvokeMember("Delete", BindingFlags.InvokeMethod, null, oExcelSheet, null); } } Parameters = new object[3]; Parameters[0] = Parameters[1] = Parameters[2] = Type.Missing; if (oExcelWorkbook != null) { oExcelWorkbook.GetType().InvokeMember("Save", BindingFlags.InvokeMethod, null, oExcelWorkbook, null); oExcelWorkbook.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, oExcelWorkbook, Parameters); } if (oExcelApp != null) { oExcelApp.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, oExcelApp, null); } |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What am I doing wrong? Deleteing a sheet.
Thanks, NickHK!
I know that the Delete statement is executed. I have stepped through the conde *some* times. The snippet I showed here is stripped for error handling. I use exceptions for catching errors but I never end there... I have tried to make a sample using early binding. The thing is that I cant get that to work either........Here is the sample: ApplicationClass app = new ApplicationClass(); Workbook workbook = null; Worksheet worksheet = null; workbook = app.Workbooks.Open("C:\\TestSet.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value); worksheet = (Worksheet)workbook.Sheets["noleap"]; if (worksheet != null) { worksheet.Delete(); } if (workbook != null) { workbook.Close(true, Missing.Value, Missing.Value); workbook = null; } if (app != null) { app.Quit(); app = null; } What is actually the right procedure for deleting a sheet? Is it necessary to do all this: 1. Open XLS file 2. Select sheet 3. Call delete on the sheet 4. Save the file 5. Close the Workbook & Application NickHK wrote: Jon, What if you just try to delete the sheet directly, with the correct case of the name: Worksheets("NoLeap").Delete All I can think is that your If never evaluates to true. What if you replace you .Delete with a simple MsgBox, just see if it fires. What error handling do you have in place ? Not whatever the .Net equivalent of "On Error Resume Next" ? NickHK P.S. There is NG "microsoft.public.excel.sdk" which seemed more geared to Interop stuff. Doesn't look that busy though. "Jon H" wrote in message oups.com... You are perfectly right, NickHK. I am trying to delete a sheet named "noleap" and the 'true' argument indicates case insensitive. I tried to turn off the DisplayAlerts property as you suggested, but it didn't do any difference. Here's what i added to my code: Parameters = new object[1]; Parameters[0] = true; oExcelApp.GetType().InvokeMember("DisplayAlerts", BindingFlags.SetProperty, null, oExcelApp, Parameters); NickHK wrote: I don't use .Net, but.. It looks like you trying to delete the sheet "noleap". What does the True argument to String.Compare indicate ? Case insensitive comparison ? Normally in Excel, when you try to Delete a sheet, there is a warning dialog asking you to confirm the deletion. In VB, you can turn this off with oExcelApp.DisplayAlerts=False resetting it to true when ready. NickHK roups.com... I'm trying to delete a worksheet from a workbook using C# and late binding. What ever I try, I don't seem to get rid of the sheet. I get no errors or no messages. Below is the basic of the code i try to run. Can anybody please enlighten me? object oExcelApp = null; object oExcelWorkbooks; object oExcelWorkbook = null; object oExcelSheets; object oExcelSheet; object[] Parameters; int numberOfSheets = 0; Type objClassType; // Get the class type and instantiate Excel. objClassType = Type.GetTypeFromProgID("Excel.Application"); oExcelApp = Activator.CreateInstance(objClassType); //Get the workbooks collection. oExcelWorkbooks = oExcelApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, oExcelApp, null); // Open a workbook Parameters = new object[15]; Parameters[0] = @"C:\TestSet.xls"; ; Parameters[3] = 5; // Format = Nothing. Parameters[1] = Parameters[2] = Parameters[4] = Parameters[5] = Parameters[6] = Parameters[7] = Parameters[8] = Parameters[9] = Parameters[10] = Parameters[11] = Parameters[12] = Parameters[13] = Parameters[14] = Type.Missing; oExcelWorkbook = oExcelWorkbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, oExcelWorkbooks, Parameters); oExcelSheets = oExcelWorkbook.GetType().InvokeMember("Worksheets" , BindingFlags.GetProperty, null, oExcelWorkbook, null); numberOfSheets = (int)oExcelSheets.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, oExcelSheets, null); int n; String sheet; Parameters = new object[1]; for (n = 1; n <= numberOfSheets; n++) { Parameters[0] = n; //Get the first worksheet. oExcelSheet = oExcelSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, oExcelSheets, Parameters); sheet = (String)oExcelSheet.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, oExcelSheet, null); if ( String.Compare( sheet, "noleap", true) == 0) { // Delete the sheet! oExcelSheet.GetType().InvokeMember("Delete", BindingFlags.InvokeMethod, null, oExcelSheet, null); } } Parameters = new object[3]; Parameters[0] = Parameters[1] = Parameters[2] = Type.Missing; if (oExcelWorkbook != null) { oExcelWorkbook.GetType().InvokeMember("Save", BindingFlags.InvokeMethod, null, oExcelWorkbook, null); oExcelWorkbook.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, oExcelWorkbook, Parameters); } if (oExcelApp != null) { oExcelApp.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, oExcelApp, null); } |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What am I doing wrong? Deleteing a sheet.
Jon,
Correct procedure, but step #2 is not necessary. In your environment, code is case sensitive ? So your variable "worksheet" is not confused with the object "Worksheet" ? I can't tell you if your syntax is correct, but you method looks fine. I'm surprised you are not getting any errors raised. What happens if try to delete a non-existent sheet: Worksheets("NothingCalledThis").Delete You must get an error then. NickHK "Jon H" wrote in message ups.com... Thanks, NickHK! I know that the Delete statement is executed. I have stepped through the conde *some* times. The snippet I showed here is stripped for error handling. I use exceptions for catching errors but I never end there... I have tried to make a sample using early binding. The thing is that I cant get that to work either........Here is the sample: ApplicationClass app = new ApplicationClass(); Workbook workbook = null; Worksheet worksheet = null; workbook = app.Workbooks.Open("C:\\TestSet.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value); worksheet = (Worksheet)workbook.Sheets["noleap"]; if (worksheet != null) { worksheet.Delete(); } if (workbook != null) { workbook.Close(true, Missing.Value, Missing.Value); workbook = null; } if (app != null) { app.Quit(); app = null; } What is actually the right procedure for deleting a sheet? Is it necessary to do all this: 1. Open XLS file 2. Select sheet 3. Call delete on the sheet 4. Save the file 5. Close the Workbook & Application NickHK wrote: Jon, What if you just try to delete the sheet directly, with the correct case of the name: Worksheets("NoLeap").Delete All I can think is that your If never evaluates to true. What if you replace you .Delete with a simple MsgBox, just see if it fires. What error handling do you have in place ? Not whatever the .Net equivalent of "On Error Resume Next" ? NickHK P.S. There is NG "microsoft.public.excel.sdk" which seemed more geared to Interop stuff. Doesn't look that busy though. "Jon H" wrote in message oups.com... You are perfectly right, NickHK. I am trying to delete a sheet named "noleap" and the 'true' argument indicates case insensitive. I tried to turn off the DisplayAlerts property as you suggested, but it didn't do any difference. Here's what i added to my code: Parameters = new object[1]; Parameters[0] = true; oExcelApp.GetType().InvokeMember("DisplayAlerts", BindingFlags.SetProperty, null, oExcelApp, Parameters); NickHK wrote: I don't use .Net, but.. It looks like you trying to delete the sheet "noleap". What does the True argument to String.Compare indicate ? Case insensitive comparison ? Normally in Excel, when you try to Delete a sheet, there is a warning dialog asking you to confirm the deletion. In VB, you can turn this off with oExcelApp.DisplayAlerts=False resetting it to true when ready. NickHK roups.com... I'm trying to delete a worksheet from a workbook using C# and late binding. What ever I try, I don't seem to get rid of the sheet. I get no errors or no messages. Below is the basic of the code i try to run. Can anybody please enlighten me? object oExcelApp = null; object oExcelWorkbooks; object oExcelWorkbook = null; object oExcelSheets; object oExcelSheet; object[] Parameters; int numberOfSheets = 0; Type objClassType; // Get the class type and instantiate Excel. objClassType = Type.GetTypeFromProgID("Excel.Application"); oExcelApp = Activator.CreateInstance(objClassType); //Get the workbooks collection. oExcelWorkbooks = oExcelApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, oExcelApp, null); // Open a workbook Parameters = new object[15]; Parameters[0] = @"C:\TestSet.xls"; ; Parameters[3] = 5; // Format = Nothing. Parameters[1] = Parameters[2] = Parameters[4] = Parameters[5] = Parameters[6] = Parameters[7] = Parameters[8] = Parameters[9] = Parameters[10] = Parameters[11] = Parameters[12] = Parameters[13] = Parameters[14] = Type.Missing; oExcelWorkbook = oExcelWorkbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, oExcelWorkbooks, Parameters); oExcelSheets = oExcelWorkbook.GetType().InvokeMember("Worksheets" , BindingFlags.GetProperty, null, oExcelWorkbook, null); numberOfSheets = (int)oExcelSheets.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, oExcelSheets, null); int n; String sheet; Parameters = new object[1]; for (n = 1; n <= numberOfSheets; n++) { Parameters[0] = n; //Get the first worksheet. oExcelSheet = oExcelSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, oExcelSheets, Parameters); sheet = (String)oExcelSheet.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, oExcelSheet, null); if ( String.Compare( sheet, "noleap", true) == 0) { // Delete the sheet! oExcelSheet.GetType().InvokeMember("Delete", BindingFlags.InvokeMethod, null, oExcelSheet, null); } } Parameters = new object[3]; Parameters[0] = Parameters[1] = Parameters[2] = Type.Missing; if (oExcelWorkbook != null) { oExcelWorkbook.GetType().InvokeMember("Save", BindingFlags.InvokeMethod, null, oExcelWorkbook, null); oExcelWorkbook.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, oExcelWorkbook, Parameters); } if (oExcelApp != null) { oExcelApp.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, oExcelApp, null); } |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What am I doing wrong? Deleteing a sheet.
NickHK!
Problem solved! Thanks for pointing me in the right direction. The trick with the "DisplayAlerts" worked. I just made a misstake the first time I tried it. I did set the property to true in stead of false! The way I found out was to set the Visible property of the Application object to true. Then I saw all messages that I didn't see before. As for the Q's you mention below: ..NET environment, at leas C# is case sensitive so "worksheet" is not the same as "Worksheet". Again, thanks a lot for your help! Jon H. NickHK wrote: Jon, Correct procedure, but step #2 is not necessary. In your environment, code is case sensitive ? So your variable "worksheet" is not confused with the object "Worksheet" ? I can't tell you if your syntax is correct, but you method looks fine. I'm surprised you are not getting any errors raised. What happens if try to delete a non-existent sheet: Worksheets("NothingCalledThis").Delete You must get an error then. NickHK "Jon H" wrote in message ups.com... Thanks, NickHK! I know that the Delete statement is executed. I have stepped through the conde *some* times. The snippet I showed here is stripped for error handling. I use exceptions for catching errors but I never end there... I have tried to make a sample using early binding. The thing is that I cant get that to work either........Here is the sample: ApplicationClass app = new ApplicationClass(); Workbook workbook = null; Worksheet worksheet = null; workbook = app.Workbooks.Open("C:\\TestSet.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value); worksheet = (Worksheet)workbook.Sheets["noleap"]; if (worksheet != null) { worksheet.Delete(); } if (workbook != null) { workbook.Close(true, Missing.Value, Missing.Value); workbook = null; } if (app != null) { app.Quit(); app = null; } What is actually the right procedure for deleting a sheet? Is it necessary to do all this: 1. Open XLS file 2. Select sheet 3. Call delete on the sheet 4. Save the file 5. Close the Workbook & Application NickHK wrote: Jon, What if you just try to delete the sheet directly, with the correct case of the name: Worksheets("NoLeap").Delete All I can think is that your If never evaluates to true. What if you replace you .Delete with a simple MsgBox, just see if it fires. What error handling do you have in place ? Not whatever the .Net equivalent of "On Error Resume Next" ? NickHK P.S. There is NG "microsoft.public.excel.sdk" which seemed more geared to Interop stuff. Doesn't look that busy though. "Jon H" wrote in message oups.com... You are perfectly right, NickHK. I am trying to delete a sheet named "noleap" and the 'true' argument indicates case insensitive. I tried to turn off the DisplayAlerts property as you suggested, but it didn't do any difference. Here's what i added to my code: Parameters = new object[1]; Parameters[0] = true; oExcelApp.GetType().InvokeMember("DisplayAlerts", BindingFlags.SetProperty, null, oExcelApp, Parameters); NickHK wrote: I don't use .Net, but.. It looks like you trying to delete the sheet "noleap". What does the True argument to String.Compare indicate ? Case insensitive comparison ? Normally in Excel, when you try to Delete a sheet, there is a warning dialog asking you to confirm the deletion. In VB, you can turn this off with oExcelApp.DisplayAlerts=False resetting it to true when ready. NickHK roups.com... I'm trying to delete a worksheet from a workbook using C# and late binding. What ever I try, I don't seem to get rid of the sheet. I get no errors or no messages. Below is the basic of the code i try to run. Can anybody please enlighten me? object oExcelApp = null; object oExcelWorkbooks; object oExcelWorkbook = null; object oExcelSheets; object oExcelSheet; object[] Parameters; int numberOfSheets = 0; Type objClassType; // Get the class type and instantiate Excel. objClassType = Type.GetTypeFromProgID("Excel.Application"); oExcelApp = Activator.CreateInstance(objClassType); //Get the workbooks collection. oExcelWorkbooks = oExcelApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, oExcelApp, null); // Open a workbook Parameters = new object[15]; Parameters[0] = @"C:\TestSet.xls"; ; Parameters[3] = 5; // Format = Nothing. Parameters[1] = Parameters[2] = Parameters[4] = Parameters[5] = Parameters[6] = Parameters[7] = Parameters[8] = Parameters[9] = Parameters[10] = Parameters[11] = Parameters[12] = Parameters[13] = Parameters[14] = Type.Missing; oExcelWorkbook = oExcelWorkbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, oExcelWorkbooks, Parameters); oExcelSheets = oExcelWorkbook.GetType().InvokeMember("Worksheets" , BindingFlags.GetProperty, null, oExcelWorkbook, null); numberOfSheets = (int)oExcelSheets.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, oExcelSheets, null); int n; String sheet; Parameters = new object[1]; for (n = 1; n <= numberOfSheets; n++) { Parameters[0] = n; //Get the first worksheet. oExcelSheet = oExcelSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, oExcelSheets, Parameters); sheet = (String)oExcelSheet.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, oExcelSheet, null); if ( String.Compare( sheet, "noleap", true) == 0) { // Delete the sheet! oExcelSheet.GetType().InvokeMember("Delete", BindingFlags.InvokeMethod, null, oExcelSheet, null); } } Parameters = new object[3]; Parameters[0] = Parameters[1] = Parameters[2] = Type.Missing; if (oExcelWorkbook != null) { oExcelWorkbook.GetType().InvokeMember("Save", BindingFlags.InvokeMethod, null, oExcelWorkbook, null); oExcelWorkbook.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, oExcelWorkbook, Parameters); } if (oExcelApp != null) { oExcelApp.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, oExcelApp, null); } |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What am I doing wrong? Deleteing a sheet.
Glad you found it
As you are working with Excel, you can record a macro (ToolsMacroRecord New Macro) of your required step first, to at least show what you need to accomplish, then translate to C#. OK, it won't tell the .Displayalerts code, but you would have seen the dialog appear during the sequence of events and that you would have to deal with it. NickHK "Jon H" wrote in message ups.com... NickHK! Problem solved! Thanks for pointing me in the right direction. The trick with the "DisplayAlerts" worked. I just made a misstake the first time I tried it. I did set the property to true in stead of false! The way I found out was to set the Visible property of the Application object to true. Then I saw all messages that I didn't see before. As for the Q's you mention below: .NET environment, at leas C# is case sensitive so "worksheet" is not the same as "Worksheet". Again, thanks a lot for your help! Jon H. NickHK wrote: Jon, Correct procedure, but step #2 is not necessary. In your environment, code is case sensitive ? So your variable "worksheet" is not confused with the object "Worksheet" ? I can't tell you if your syntax is correct, but you method looks fine. I'm surprised you are not getting any errors raised. What happens if try to delete a non-existent sheet: Worksheets("NothingCalledThis").Delete You must get an error then. NickHK "Jon H" wrote in message ups.com... Thanks, NickHK! I know that the Delete statement is executed. I have stepped through the conde *some* times. The snippet I showed here is stripped for error handling. I use exceptions for catching errors but I never end there... I have tried to make a sample using early binding. The thing is that I cant get that to work either........Here is the sample: ApplicationClass app = new ApplicationClass(); Workbook workbook = null; Worksheet worksheet = null; workbook = app.Workbooks.Open("C:\\TestSet.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value); worksheet = (Worksheet)workbook.Sheets["noleap"]; if (worksheet != null) { worksheet.Delete(); } if (workbook != null) { workbook.Close(true, Missing.Value, Missing.Value); workbook = null; } if (app != null) { app.Quit(); app = null; } What is actually the right procedure for deleting a sheet? Is it necessary to do all this: 1. Open XLS file 2. Select sheet 3. Call delete on the sheet 4. Save the file 5. Close the Workbook & Application NickHK wrote: Jon, What if you just try to delete the sheet directly, with the correct case of the name: Worksheets("NoLeap").Delete All I can think is that your If never evaluates to true. What if you replace you .Delete with a simple MsgBox, just see if it fires. What error handling do you have in place ? Not whatever the .Net equivalent of "On Error Resume Next" ? NickHK P.S. There is NG "microsoft.public.excel.sdk" which seemed more geared to Interop stuff. Doesn't look that busy though. "Jon H" wrote in message oups.com... You are perfectly right, NickHK. I am trying to delete a sheet named "noleap" and the 'true' argument indicates case insensitive. I tried to turn off the DisplayAlerts property as you suggested, but it didn't do any difference. Here's what i added to my code: Parameters = new object[1]; Parameters[0] = true; oExcelApp.GetType().InvokeMember("DisplayAlerts", BindingFlags.SetProperty, null, oExcelApp, Parameters); NickHK wrote: I don't use .Net, but.. It looks like you trying to delete the sheet "noleap". What does the True argument to String.Compare indicate ? Case insensitive comparison ? Normally in Excel, when you try to Delete a sheet, there is a warning dialog asking you to confirm the deletion. In VB, you can turn this off with oExcelApp.DisplayAlerts=False resetting it to true when ready. NickHK roups.com... I'm trying to delete a worksheet from a workbook using C# and late binding. What ever I try, I don't seem to get rid of the sheet. I get no errors or no messages. Below is the basic of the code i try to run. Can anybody please enlighten me? object oExcelApp = null; object oExcelWorkbooks; object oExcelWorkbook = null; object oExcelSheets; object oExcelSheet; object[] Parameters; int numberOfSheets = 0; Type objClassType; // Get the class type and instantiate Excel. objClassType = Type.GetTypeFromProgID("Excel.Application"); oExcelApp = Activator.CreateInstance(objClassType); //Get the workbooks collection. oExcelWorkbooks = oExcelApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, oExcelApp, null); // Open a workbook Parameters = new object[15]; Parameters[0] = @"C:\TestSet.xls"; ; Parameters[3] = 5; // Format = Nothing. Parameters[1] = Parameters[2] = Parameters[4] = Parameters[5] = Parameters[6] = Parameters[7] = Parameters[8] = Parameters[9] = Parameters[10] = Parameters[11] = Parameters[12] = Parameters[13] = Parameters[14] = Type.Missing; oExcelWorkbook = oExcelWorkbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, oExcelWorkbooks, Parameters); oExcelSheets = oExcelWorkbook.GetType().InvokeMember("Worksheets" , BindingFlags.GetProperty, null, oExcelWorkbook, null); numberOfSheets = (int)oExcelSheets.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, oExcelSheets, null); int n; String sheet; Parameters = new object[1]; for (n = 1; n <= numberOfSheets; n++) { Parameters[0] = n; //Get the first worksheet. oExcelSheet = oExcelSheets.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, oExcelSheets, Parameters); sheet = (String)oExcelSheet.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, oExcelSheet, null); if ( String.Compare( sheet, "noleap", true) == 0) { // Delete the sheet! oExcelSheet.GetType().InvokeMember("Delete", BindingFlags.InvokeMethod, null, oExcelSheet, null); } } Parameters = new object[3]; Parameters[0] = Parameters[1] = Parameters[2] = Type.Missing; if (oExcelWorkbook != null) { oExcelWorkbook.GetType().InvokeMember("Save", BindingFlags.InvokeMethod, null, oExcelWorkbook, null); oExcelWorkbook.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, oExcelWorkbook, Parameters); } if (oExcelApp != null) { oExcelApp.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, oExcelApp, null); } |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert Calculated Field (wrong Qty*Price = wrong Amount) | Excel Discussion (Misc queries) | |||
| What's wrong with this? | Excel Programming | |||
| What am I doing wrong....!!! | Excel Programming | |||
| What is wrong? | Excel Programming | |||
| Anybody see anything wrong with this | Excel Programming | |||
Linear Mode
