Code only converting last worksheet to csv

Question

I wrote a program in C# that is supposed to convert each worksheet in my excel workbook to a csv and save it in their own files. The problem I'm having is that when I open each file, they all have the same content as the very last worksheet. Here is my code:

public void Main()
{
    Excel.Application excelApp = new Excel.Application();
    Excel.Workbook workbook = excelApp.Workbooks.Open(@"C:\Users\user\Desktop\Book1.xlsx");

    foreach (Excel.Worksheet sht in workbook.Worksheets)
    {
        sht.Select();
        System.Diagnostics.Debug.WriteLine(sht.Name.ToString());
        workbook.SaveAs(string.Format("{0}{1}.csv", @"C:\Users\user\Desktop\", sht.Name), Excel.XlFileFormat.xlCSV, Excel.XlSaveAsAccessMode.xlNoChange);
    }

    workbook.Close(false);

    Dts.TaskResult = (int)ScriptResults.Success;
}

Any help would be great, thanks!

Update 1

I don't know if it's worth mentioning that I'm trying to do this through a script task in SSIS. So it's just one script task that I run that contains the code above.

No @jdweng - OP is saving the `workbook` instead of the `worksheet`. Saving a `workbook` as a CSV will default into saving only the sheet itself. In this case, the last sheet. Save your `worksheet` as a CSV instead. This line: `workbook.SaveAs` is your issue. — gravity, Jan 28 '19 at 16:36
@MosheKatz - Yes, the names are iterative. The content is not. — gravity, Jan 28 '19 at 16:38
Does the debug line show all the sheet names? There is a difference between a WorkSheet and a Sheet. The Sheet has many different type one is a WorkSheet. So try :Excel.Worksheet sht in workbook.Sheets — jdweng, Jan 28 '19 at 16:40
@jdweng I tried both suggestions. It still seems to behave the same way and give me the same output — jim, Jan 28 '19 at 16:43
@jdweng I don't know if it's worth mentioning but when I open the file I get the message: `Excel has detected that 'filename' is a SYLK file, but cannot load it. Either the file has errors or it is not a SYLK file format. Click OK to try to open the file in a different format.` and when I click ok it opens normally it seems — jim, Jan 28 '19 at 16:46
Some answers on [this question](https://stackoverflow.com/questions/10551353/saving-excel-worksheet-to-csv-files-with-filenameworksheet-name-using-vb) imply that you need to use `sht.Copy()` to make a copy of the sheet in order to save it to CSV, but they don't say why that is true. — Moshe Katz, Jan 28 '19 at 16:48
Not an answer (yet) because I'm not totally sure, but I think you should be using [`sht.Activate()`](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.activate%28method%29) instead of `sht.Select()`. — Moshe Katz, Jan 28 '19 at 16:51
@MosheKatz thanks for the response, I tried sht.Activate() . Still no luck though — jim, Jan 28 '19 at 16:54
I'e done this lots of times and always worked unless the workbook has same data on every sheet. The SYLK error is due to the extension csv being mapped to SYLK. You can open file with notepad to check if every sheet is different. The error could be with SYLK opening the CSV file. — jdweng, Jan 28 '19 at 16:57
@jdweng the original file has different contents in every worksheet but when I open the results, they all have the same content. And its the content of the very last worksheet in the original file — jim, Jan 28 '19 at 17:01
I have tested your code with a spreadsheet of my own - three worksheets - all save as CSV files with different & correct contents. — PaulF, Jan 28 '19 at 17:04
I suspect you are using formulas and the workbook is in Manual Calculation Mode. — jdweng, Jan 28 '19 at 17:04
What you're describing seems impossible, which suggests that some assumption is off. Can you delete the files before repeating? Check the write times on the files? Try opening the files in notepad? Something tells me that the files you're looking at may not be the same ones you're writing. — Scott Hannen, Jan 28 '19 at 20:33
@ScottHannen I retried with a different file and it still only creates csv files containing contents from the very last tab. I made sure I changed it up and everything. — jim, Jan 29 '19 at 12:51
You could try copying the current sheet to a new workbook and then saving it as CSV. Not that I think that's the best long term solution (unless it's the only thing that works) but maybe it will help to shake loose whatever is going on. Please share when you find the problem. It's way more exciting than anything I'm going to work on today. — Scott Hannen, Jan 29 '19 at 14:07
Hey @ScottHannen I basically copied the code I found here: http://www.techbrothersit.com/2016/03/how-to-create-csv-file-for-each-excel.html, it manually opens all the sheets in the workbook and copies all the columns and than the data and seperates it with whatever delimiter u specify — jim, Jan 29 '19 at 16:31
Check out this link https://stackoverflow.com/questions/50122319/ssis-process-for-saving-xlsx-file-as-csv-file/50124183#50124183 — Hadi, Feb 03 '19 at 18:06

score 0 · Answer 1 · answered Mar 10 '19 at 21:21

Trying to figure out the issue

In normal cases, the code you provided will work perfectly. It may encounter some issue in case that the excel application has shown a message box, need permissions to enable editing, there are permissions issue to access other worksheets since they are protected ...

First of all, open the excel manually and check that you can access all worksheets and perform save operations manually. If you didn't encountered any issue, then you should prevent excel from showing message boxes or other promotion while using Interop.Excel library.

In addition, check that the Csv does not already exists in the destination path.

Try using a similar code:

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;

Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.Workbooks.Open(@"D:\Book1.xlsx");
workbook.DoNotPromptForConvert = true;
workbook.CheckCompatibility = false;

foreach (Microsoft.Office.Interop.Excel.Worksheet sht in workbook.Worksheets)
{
    sht.Select();
    System.Diagnostics.Debug.WriteLine(sht.Name.ToString());

    if (System.IO.File.Exists(string.Format("{0}{1}.csv", @"D:\", sht.Name)))
    {
        System.IO.File.Delete(string.Format("{0}{1}.csv", @"D:\", sht.Name);

    }

    workbook.SaveAs(string.Format("{0}{1}.csv", @"D:\", sht.Name),
                                  Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange);
}

//workbook.Close(false);
workbook.Close(false, Type.Missing, Type.Missing);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

I tested the following code and it converted all Worksheets successfully.

Code only converting last worksheet to csv

Update 1

1 Answers1

Trying to figure out the issue