Excel.Workbook.SaveAs() throws exception on clicking 'No' for file replace in C#

Question

My small console tool tries to read an excel file and create new out of that depending on set criteria.

Problem: When there is a filename conflict(file already exists with same name), programme should generate the file with an unique name. But now a message comes up with "yes/no/cancel" message box to save the file. If user clicks NO then exception thrown. The message is as follows:

A file named 'D:\sample.xls' already exists in this location. Do you want to replace it? 

The exception:

A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in ExcelSplitter.exe

Additional information: Exception from HRESULT: 0x800A03EC

The following line thorws the exception

xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); 

The code is as follows:

    private bool WriteToExcel(String fileName, List<RowEntity> headerRowObj, List<RowEntity> dataRowObj)
    {
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook = null;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        xlApp = new Excel.ApplicationClass();

        xlWorkBook = xlApp.Workbooks.Add(misValue);

        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        int headerRowTotal = 0;

        for (int i = 0; i < headerRowObj.Count; i++)
        {
            for (int j = 0; j < headerRowObj[i].ColumnValues.Count; j++)
            {
                xlWorkSheet.Cells[i + 1, j + 1] = headerRowObj[i].ColumnValues[j].ToString();
            }
            headerRowTotal++;
        }

        for (int i = 0; i < dataRowObj.Count; i++)
        {
            for (int j = 0; j < dataRowObj[i].ColumnValues.Count; j++)
            {
                xlWorkSheet.Cells[headerRowTotal + i + 1, j + 1] = dataRowObj[i].ColumnValues[j].ToString();
            }
        }

        if (IsExcelFileOpen(xlWorkBook))
        {
            errorList.Add(Error.GetError(-7));
            return false;
        }
        else
        {
            xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

        }

        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        ReleaseObject(xlWorkSheet);
        ReleaseObject(xlWorkBook);
        ReleaseObject(xlApp);

        return true;
    }

The above method is called in the following method:

 private int SeparateExcleFiles(int headerLines, int groupOnColumn, string outputPath, string inputFile, FileEntity fileObj, RowEntity rowObj)
    {
        List<RowEntity> headerRowObj = fileObj.RowValues.GetRange(0, headerLines);
        if (rowObj.ColumnValues.Count < groupOnColumn)
        {
            errorList.Add(Error.GetError(-6));
            return -6;
        }
        else
        {
            var dataRows = fileObj.RowValues.GetRange(headerLines, fileObj.RowValues.Count - (headerLines)).GroupBy(re => re.ColumnValues[groupOnColumn - 1]).ToList();

            for (int i = 0; i < dataRows.Count; i++)
            {
                var fileName = String.Format("{0}-{1}{2}", Path.GetFileNameWithoutExtension(inputFile), dataRows[i].Key.ToString(), Path.GetExtension(inputFile));
                var filePath = Path.Combine(outputPath, fileName);

                if (File.Exists(filePath))
                {
                    fileName = GetUniqueFilename(fileName);
                }

                if (WriteToExcel(filePath, headerRowObj, dataRows[i].ToList() as List<RowEntity>))
                {
                    System.Console.WriteLine("Wrote {0}", fileName);
                }
            }
            return 0;
        }
    }


 private bool IsExcelFileOpen(Workbook wBook)
    {
        Excel.Application exApp;
        exApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
        try
        {
            exApp.Workbooks.get_Item(wBook);
            return true;
        }
        catch (Exception)
        {
            return false;
        }

    }    

Where exactly am I doing it wrong?

Answer 1

Could you try this on the saveas part.

try
{
    xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue,     misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
}
catch(Exception e)
{
//BLANK (do nothing)
}

This will allow you to handle the exception, but if you don't want to do anything and just not overwrite just leave the handling process blank.

Having the exception handler will let the system think that you acknowledged the event and will let the program continue.