Member Avatar for Pats2KDynasty
Pats2KDynasty 0 Newbie Poster

I have searched and nothing that I have found hits directly on what I am looking for (or maybe my searches have not hit on the combo of words).

In C# I have created an Excel sheet, using Interop.Excel, in which I insert some data and create a chart. This all works fine when I do a xlWorkBook.SaveAs.

What I want to do is prompt the user to put the automated workbook somewhere with thier own file name. I have tried (<http://p2p.wrox.com/vb-how/63900-disabling-second-excel-save-prompt.html>) where he basically does a new SaveFileDialog then if its == OK he builds his Excel sheet then he says that his workbook.SaveAs(FilePathFromSaveAsDialog) causes a prompt. When I try it, I get the "Showing modal dialog box when application is not running in UserInteractive mode is not a valid operation" error.
I would paste all my code but it is on a seperate system, however the just of it is:

using Excel = Microsoft.Office.Interop.Office 
   
    //....then on click of link button....

    Excel.Application xlApp;
    Excel.Workbook xlWorbook;
    Excel.Workbooks xlWorkbooks;
    Excel.Sheets xlSheets;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    xlApp = new Excel.ApplicationClass();
    xlWorkBooks = xlApp.Workbooks;
    xlWorkBook = xlWorbooks.Add(misValue);
    xlSheets = xlWorkBook.Worksheets;
    xlWorkSheet = (Excel.Worksheet)xlSheets.get_Item(1);
    
    //....Now I fill my Excel sheet data and make my chart >>> then I close like below...
   
    xlApp.DisplayAlerts = true;

    //HERE IS WHERE I WANT TO EITHER PASS THE PATH AND FILE NAME FROM USER OR USE A PROMPT
    xlWorkBook.SaveAs("Test.xls", Excel.XFileFormat.XlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);    
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    //finally I release all my objects via Marshal.ReleaseComObject then GC.Collect
  • 1 Contributor
  • 0 Replies
  • 186 Views

Dani AI

Generated

— that exception means a modal UI (SaveFileDialog or Excel alert) tried to open while your process had no interactive desktop. That commonly happens when code runs inside ASP.NET, a Windows Service, or a background thread. The fix depends on where the code runs.

If this is a desktop app (WinForms/WPF): show the SaveFileDialog on the UI thread first, get the full path, then create/populate the workbook and call SaveAs to that path. Make sure any thread that displays dialogs is STA. Also disable Excel prompts before saving: set xlApp.DisplayAlerts = false so Excel won’t try to pop up overwrite/compatibility dialogs.

If this runs on a web server: do not show client dialogs on the server. Instead generate the workbook server-side and stream it to the browser with a Content-Disposition attachment so the browser shows the Save As dialog. Using a library that writes Excel files in-memory is much safer than automating Excel on the server. Example (ASP.NET MVC + EPPlus):

public ActionResult DownloadReport()
{
    using(var pkg = new ExcelPackage())
    {
        var ws = pkg.Workbook.Worksheets.Add("Sheet1");
        ws.Cells["A1"].Value = "Report";
        var bytes = pkg.GetAsByteArray();
        return File(bytes,
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            "Report.xlsx");
    }
}

If you must continue with Interop, set xlApp.DisplayAlerts = false, call xlWorkBook.Close(false) then xlApp.Quit(), and release COM objects in reverse creation order with Marshal.FinalReleaseComObject(...). Follow that with GC.Collect() and GC.WaitForPendingFinalizers() (run twice). Finally, a strong caution: Office automation on servers is unsupported and fragile; using Open XML/EPPlus/ClosedXML/NPOI avoids UI problems and is far more reliable for web scenarios.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.