By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,543 Members | 2,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,543 IT Pros & Developers. It's quick & easy.

Save each sheet in an Excel workbook to separate files

P: n/a
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

Nov 17 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.Te xt, 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.Te xt, 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[i].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[i]),oMissing,oMissing,oMissing,oMissing,oMissing,Exc el.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

Nov 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.