title: Splitting Excel Tabs to Separate Files
body: <p>I'm trying to write a C#.NET function to split Excel worksheets into separate files. I'm using Excel Interop but I can't seem to get it to work like I want.  The best I'm able to do is resave the file with a specific tab selected. The MSDN documentation seems very unclear on which functions to use. I read somewhere else that some of these functions aren't behaving as expected wither. </p>
  
  <pre><code>               private void SplitFile(string targetPath, string sourceFile)
      {
          Excel.Application xlApp;
          Excel.Workbook xlFile;
          //Excel.Worksheet xlWorkSheet;
          object misValue = System.Reflection.Missing.Value;
  
          string exportFormat = "";
          if (cboExcel.Checked == true) //set the output format
              exportFormat = "XLSX";
          else if (cboCsv.Checked == true)
              exportFormat = "CSV";
          else
              Console.WriteLine("Error detecting output format");
  
          xlApp = new Excel.Application(); //object for controlling Excel
          xlFile = xlApp.Workbooks.Open(txtFilePath.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //open the source file
          //xlWorkSheet = (Excel.Worksheet)xlFile.Worksheets.get_Item(1); //Select the first tab in the file. Note: Index does NOT start are zero.
  
  
          xlApp.DisplayAlerts = false; //override Excel save dialog message
          int TabCount = xlFile.Worksheets.Count; //total count of the tabs in the file
  
          int sheetCount = 0; //this will be used to output the number of exported sheets
          for (int i = 1; i &lt;= TabCount; i++) //for each sheet in the workbook...
          {
              //Console.WriteLine(i.ToString() + ": " + xlFile.Worksheets.Item[i].Name);
              xlApp.ActiveWorkbook.Sheets[i].Select();
              string sheetName = xlFile.Sheets[i].Name; //..get the name of the sheet. It will be used for the new filename
  
              Console.WriteLine(i.ToString() + ": " + sheetName);
              string newFilename = targetPath + "\\" + sheetName; //set the filename with full path, but no extension
              Console.WriteLine(newFilename);
  
              toolStripStatus.Text = "Exporting: " + sheetName; //update the status bar
              Excel.Worksheet tempSheet = (xlApp.Worksheets[i]); //Current tab will be saved to this in a new workbook
              tempSheet.Copy();
              Excel.Workbook tempBook = xlApp.ActiveWorkbook;
  
              try
              {
                  switch (exportFormat) //if the file does NOT exist OR if does and the the user wants to overwrite it, do the export and increase the sheetCount by 1
                  {
                      case "CSV":
                          if (!File.Exists(newFilename + ".csv") || MessageBox.Show(sheetName + ".csv already exists. Overwrite?", "Confirm Overwrite", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes)
                          {
                              tempBook.Worksheets[1].SaveAs(newFilename, Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing);
                              sheetCount++;
                          }
                          break;
                      case "XLSX":
                          if (!File.Exists(newFilename + ".xlsx") || MessageBox.Show(sheetName + ".xlsx already exists. Overwrite?", "Confirm Overwrite", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes)
                          {
                              tempBook.Worksheets[1].SaveAs(newFilename, Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing);
                              //tempSheet.SaveAs(newFilename, Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing);
                              sheetCount++;
                          }
                          break;
                      default:
                          Console.WriteLine("Unexpected export format");
                          MessageBox.Show("Unexpected export format");
                          break;
                  }
              }
              catch (Exception ex)
              {
                  toolStripStatus.Text = "Error!";
  
                  string errorMessage = "Error Exporting " + sheetName + System.Environment.NewLine + "Original Message: " + ex.Message;
                  MessageBox.Show(errorMessage, "Error Exporting", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                  Console.WriteLine(errorMessage);
                  toolStripStatus.Text = "Ready";
  
                  break;
              }
          }
  
          //Closing Processes Start ===================================================
          toolStripStatus.Text = "Process Finished";
          xlApp.DisplayAlerts = true;
          xlFile.Close(true, misValue, misValue);
          xlApp.Quit();
          Console.WriteLine(sheetCount.ToString() + " files exported.");
          MessageBox.Show(sheetCount.ToString() + " files exported.","Process Complete", MessageBoxButtons.OK ,MessageBoxIcon.Information);
          toolStripStatus.Text = "Ready";
          //Closing Processes Finish ====================================================================================================
  </code></pre>
  
  <p>I expect the target file to be split into multiple files, one tab per file. </p>
  
  <p>Using the code above, I get the same file copies with just the tab selected. I've tried a number of variations on the above since then, but nothing seems to be working better.</p>