Save each sheet in an Excel workbook to separate files

[javzxp]

Hi

I'd like to use C# to open an existing Excel workbook and save each
worksheet it contains into a new Excel file. The name of each new
Excel file should be the name of the worksheet copied from the existing
file.

So, as an example:
Names.xls
---------
Fred becoming Fred.xls, Mary.xls, Bob.xls, Sue.xls
Mary
Bob
Sue
If anyone could give me any pointers as to how to do this, I'd be very
grateful.

Thanks
Adrian

[javzxp]

I've now come to a solution for this, which I'll show here in case
anyone else is pondering the same problem:

private void button1_Click(o bject sender, System.EventArg s e)
{
//Start Excel and get Application object.
Excel.Applicati on oXL = new Excel.Applicati on();

Excel._Workbook oWB;
Excel._Workbook oWBNew;
Excel._Workshee t oSheet;
Excel.Range oRng;
Excel.Worksheet worksheet;
Excel.Sheets sheets;
string filename = "";

try
{

oXL.Visible = true;
//Turn off Excel message alerts
oXL.DisplayAler ts = false;

//Get the relevant workbook from the file entry textbox
oWB = (Excel._Workboo k)oXL.Workbooks .Open(txtFilePa th.Text, 0,
true, 5, "", "", true, Excel.XlPlatfor m.xlWindows, "\t", false, false,
0, true);

// get the collection of sheets in the workbook
sheets = oWB.Worksheets;
//Copy the names to an array
string[] strNames = new string[sheets.Count];
for (int p = 0; p < sheets.Count; p++)
{
worksheet = (Excel.Workshee t)sheets.get_It em(p+1);
strNames[p] = worksheet.Name;
}
oWB.Close(oMiss ing, oMissing, oMissing);

for (int i = 0; i < strNames.Length ; i++)
{
//Reopen the workbook
oWB = (Excel._Workboo k)oXL.Workbooks .Open(txtFilePa th.Text, 0,
true, 5, "", "", true, Excel.XlPlatfor m.xlWindows, "\t", false, false,
0, true);
sheets = oWB.Worksheets;
for (int x = 0; x < strNames.Length ; x++)
{
//Delete the worksheet
unless it's the one we're interested in
if (!(strNames[i].Equals(strName s[x])))
{
worksheet = (Excel.Workshee t)sheets.get_It em(strNames[x]);
worksheet.Delet e();
}
}
//Save the worksheet as the new
name
oWB.SaveAs(("c: \\ConvertedExce lFiles\\" +
strNames[i]),oMissing,oMis sing,oMissing,o Missing,oMissin g,Excel.XlSaveA sAccessMode.xlN oChange,oMissin g,oMissing,oMis sing,oMissing);
//Close the original workbook
oWB.Close(oMiss ing, oMissing,
oMissing);
}

}

catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Me ssage );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.So urce );

MessageBox.Show ( errorMessage, "Error" );
}
finally
{
//Re-enable Excel's alerts
oXL.DisplayAler ts = true;
//Exit the Excel application
oXL.Quit();
}
}

Basically, I'm opening the original workbook each time, deleting all
but the appropriate worksheet and then resaving it as the name of the
worksheet.

This works a treat, but someone may have a more elegant solution?

Adrian