What am I doing wrong?

J

jhh

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);
}
 
N

NickHK

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

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);
}
Click to expand...
 
J

Jon H

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);



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

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);
}
Click to expand...
Click to expand...
 
N

NickHK

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 said:
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);



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

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);
}
Click to expand...
Click to expand...
Click to expand...
 
J

Jon H

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




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 said:
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);



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

<[email protected]>
???????:[email protected]...
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);
}
Click to expand...
Click to expand...
Click to expand...
 
N

NickHK

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 said:
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




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 said:
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
Click to expand...
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

<[email protected]>
???????:[email protected]...
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);
}
Click to expand...
Click to expand...
Click to expand...
 
J

Jon H

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.


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 said:
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




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.


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

<[email protected]>
???????:[email protected]...
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);
}
Click to expand...
Click to expand...
Click to expand...
 
N

NickHK

Glad you found it
As you are working with Excel, you can record a macro (Tools>Macro>Record
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 said:
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.


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 said:
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
Click to expand...
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
Click to expand...
geared
to
Interop stuff. Doesn't look that busy though.


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,
Click to expand...
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

<[email protected]>
???????:[email protected]...
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
Click to expand...
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] =
Click to expand...
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);
}
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell formatting when converting from DataGridView to Excel 0
Creating a formula for a format condition in Excel 0
Excel 2007 c# saveas method error 0
Excel Late Binding with C# 2
Object Error: what am i doing wrong?? 11
Stumped - What am I doing wrong? 2
VBA Range error - How does this work and what am I doing wrong? 6

Top