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

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

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


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 = "";


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]);
//Save the worksheet as the new
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,


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" );
//Re-enable Excel's alerts
oXL.DisplayAlerts = true;
//Exit the Excel application

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

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


Nov 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.