Save each sheet in an Excel workbook to separate files

  • Thread starter Thread starter javzxp
  • Start date Start date
J

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
 
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(object sender, System.EventArgs e)
{
//Start Excel and get Application object.
Excel.Application oXL = new Excel.Application();

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

try
{

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

//Get the relevant workbook from the file entry textbox
oWB = (Excel._Workbook)oXL.Workbooks.Open(txtFilePath.Text, 0,
true, 5, "", "", true, Excel.XlPlatform.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.Worksheet)sheets.get_Item(p+1);
strNames[p] = worksheet.Name;
}
oWB.Close(oMissing, oMissing, oMissing);

for (int i = 0; i < strNames.Length; i++)
{
//Reopen the workbook
oWB = (Excel._Workbook)oXL.Workbooks.Open(txtFilePath.Text, 0,
true, 5, "", "", true, Excel.XlPlatform.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.Equals(strNames[x])))
{
worksheet = (Excel.Worksheet)sheets.get_Item(strNames[x]);
worksheet.Delete();
}
}
//Save the worksheet as the new
name
oWB.SaveAs(("c:\\ConvertedExcelFiles\\" +
strNames),oMissing,oMissing,oMissing,oMissing,oMissing,Excel.XlSaveAsAccessMode.xlNoChange,oMissing,oMissing,oMissing,oMissing);
//Close the original workbook
oWB.Close(oMissing, oMissing,
oMissing);
}

}

catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source );

MessageBox.Show( errorMessage, "Error" );
}
finally
{
//Re-enable Excel's alerts
oXL.DisplayAlerts = 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
 
Back
Top