C # Excel - save each worksheet in a new workbook

Question

I have an excel with 25 or so worksheets that I just want to save each worksheet as it owns a new workbook. When I run the code, it copies the entire book, not a separate sheet. Any help would be awesome.

string FileDropLocation = @"C:\ExcelFiles"; string file_FullFileName = @"C:\ts\Conversion\v2.xlsx"; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(file_FullFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); for (int i = 0; i < workBook.Worksheets.Count; i++) { Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[i+1]; workSheet.SaveAs(FileDropLocation + "\\" + workSheet.Name); } workBook.Close(); 

Answer 1

Try this instead of the current for loop and below

  foreach(Worksheet sheet in workBook.Worksheets) { var newbook = app.Workbooks.Add(1); sheet.Copy(newbook.Sheets[1]); newbook.SaveAs(FileDropLocation + "\\" + sheet.Name); newbook.Close(); } workBook.Close(); 

To note, I believe that Workbooks.Add() is placed on a blank sheet by default (usually Sheet1), so if you want only the copied sheet, you will have to explicitly delete it.

Answer 2

I know this works.

 Microsoft.Office.Interop.Excel.Application xl = null; Microsoft.Office.Interop.Excel._Workbook wb = null; xl = new Microsoft.Office.Interop.Excel.Application(); xl.SheetsInNewWorkbook = 1; xl.Visible = true; wb = (Microsoft.Office.Interop.Excel._Workbook)(xl.Workbooks.Add(Type.Missing)); wb.SaveAs(FileDropLocation + "\\" + workBook.Sheets[i + 1].Name, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wb.Close(true, Type.Missing, Type.Missing); xl.Quit(); Microsoft.Office.Interop.Excel.Workbook destWorkbook = null; Microsoft.Office.Interop.Excel.Worksheet workSheet = null; Microsoft.Office.Interop.Excel.Worksheet newWorksheet = null; destWorkbook = app.Workbooks.Open(FileDropLocation + "\\" + workBook.Sheets[i + 1].Name + ".xls", false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[i + 1]; newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)destWorkbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); app.DisplayAlerts = false; workSheet.Copy(Type.Missing,newWorksheet); destWorkbook.Save(); 

Answer 3

... You need to create a new book in your cycle and move the sheet to this book. I am programming in VB, so I assume, but the code inside your loop should look something like this:

 Microsoft.Office.Interop.Excel.Workbook workBook2 = app.Workbooks.Add(Missing.Value) Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[i+1]; workSheet.Copy(workBook2.Sheets(1)) 

Then you can add code to delete other sheets, etc.

Hope helps.