Saving Excel files from C# - How to get rid of Compat dialog

[Tom Archer]

I have the following code that saves the contents of a ListView to an Excel spreadsheet. This all works. However, when I added code to format the data as a table, I now get the Excel Compatibility dialog upon calling SaveAs. Is there a way to format the table or save the spreadsheet such that I do not get this dialog?

    string WriteExcelFile()
    {
      Excel.Application xlApp;
      Excel.Workbook xlWorkBook;
      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);
      // Get the enumerator to collection of child nodes of selected node
      IEnumerator enumerator = lstData.Items.GetEnumerator();
      // Header columns
      xlWorkSheet.Cells[1, 1] = TITLE;
      xlWorkSheet.Cells[1, 2] = PAGEVIEWS;
      xlWorkSheet.Cells[1, 3] = URL;
      // For each ListView item...
      int row = 2;
      while (enumerator.MoveNext())
      {
        ListViewItem li = enumerator.Current as ListViewItem;
        PageInfo pageInfo = li.Tag as PageInfo;
        // Update spreadsheet
        xlWorkSheet.Cells[row, 1] = pageInfo.Title;
        xlWorkSheet.Cells[row, 2] = pageInfo.PageViews.ToString();
        xlWorkSheet.Cells[row, 3] = pageInfo.Url;
 
        row++;
      }
      // Format data as a Table
      StringBuilder endRange = new StringBuilder();
      endRange.AppendFormat("C{0}", row);
      Excel.Range range = xlWorkSheet.get_Range("A1", endRange.ToString());
      xlWorkSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, 
                                  range,
                                  Type.Missing, 
                                  Excel.XlYesNoGuess.xlYes, 
                                  Type.Missing).Name = "TestTable";
      xlWorkSheet.ListObjects["TestTable"].TableStyle = "TableStyleMedium3";
      // Create a uniquely named file
      string fileName = GetRandomFileName(FILE_EXTENSION_EXCEL);
      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();
      ReleaseComObject(xlWorkSheet);
      ReleaseComObject(xlWorkBook);
      ReleaseComObject(xlApp);

 

[Smitty]

Welcome to the Board Tom!

I don't know anything about C, but maybe you can use Application.DisplayAlerts = False?

 

[Tom Archer]

Hey Smitty! I figured that I'd see some old friends around here

Thanks much for the tip. That did the trick perfectly!

 

[Smitty]

Glad I could help you for once!

Hope all's well in your neck of the woods - Cyndi says "Hi!".

 

[Tom Archer]

Very cool. Please tell your better half we said "hi" as well