0

I wasn't aware C# was even supposed to create a background process when copying and pasting from one Worksheet object to another, but here we are. I'm using .NET Core 6.0 on Windows 10, if it helps. A code snippet that recreates my issue is as follows:

using Reporting_WPF_Library;
using Microsoft.Office.Interop.Excel;
using Marshal = System.Runtime.InteropServices.Marshal;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Outlook;
using Outlook = Microsoft.Office.Interop.Outlook;
using System.Windows.Navigation;
using System.Windows;
using ADODB;
using System.Diagnostics;

static void RevenueNotPosted()
{
    string SavePath = "Z:\\Reporting\\Revenue Reports\\XMonthly\\Revenue Not Posted\\";

    Excel.Application XLApp = XL.App(true);
    Outlook.Application OLApp = OL.App();

    Excel.Workbooks XLWB = XLApp.Workbooks;

    Excel.Workbook? Report = XL.FileRef(XLWB, SavePath, "Revenue Not Posted", CheckAge: -1)
        ?? throw new System.Exception("RevenueNotPosted failure: FileRef on Report");
    
    Excel.Workbook? Data = XL.FileRef(XLWB, SavePath, "Dataset", ".csv", CheckAge: -1)
        ?? throw new System.Exception("RevenueNotPosted failure: FileRef on Data");

    //This is the line with the issue. If it's commented out, presto, no issue.
    //I've also tried the copy/paste route; the issue remains.
    Report.Worksheets["Report"].Columns["A:S"] = Data.Worksheets[1].Columns["A:S"].Value;

    XL.Rel(XLApp, ref Data, false);
    XL.Rel(XLApp, ref Report, false);

    Marshal.ReleaseComObject(XLWB);

    XLApp.Quit();

    Marshal.ReleaseComObject(OLApp);
    Marshal.ReleaseComObject(XLApp);

    XL.TryGettingAReservationAtDorsiaNow();
}

RevenueNotPosted();

And here's the Rel WPF library function which releases the workbooks themselves:

public static void Rel(Excel.Application XLApp, ref Workbook? WB, bool SaveBeforeClosing = true)
    {
        if (WB == null) return;
        Properties(XLApp, true);
        if (SaveBeforeClosing == true) SaveBeforeClosing = !WB.Saved;
        WB.Close(SaveChanges: SaveBeforeClosing);
        Marshal.ReleaseComObject(WB);
        WB = null;
        return;
    }

As I indicated in my code comments, if I comment out the copy/paste action, no more issue. Excel releases all its objects and closes gracefully. But as soon as I copy worksheet data, I'm left with a background process I can see in Task Manager that refuses to die gracefully, no matter what FinalReleaseComObject or GC.Collect actions I throw at it. I'm assuming it's an object, since I can then copy and paste to my heart's content and no further background processes are created. After three days of querying, even GPT couldn't figure this one out, so here's your chance, Team Humans.

Colin Gifford
  • 3
  • 3
  • 1
    https://stackoverflow.com/a/25135685/17034 – Hans Passant May 01 '23 at 21:30
  • This is useful info, and I appreciate it! But there's one problem - calling GC.Collect() doesn't kill the Excel background process created by copy/pasting. – Colin Gifford May 02 '23 at 13:09
  • Yes, I just ran a test, took out all Marshal.ReleaseComObject() calls, and just used GC.Collect; GC.WaitForPendingFinalizers;. It did indeed release all the ComObjects that I'd been using Marshal to release - thanks for that! Buuut the copy/paste zombie persists. – Colin Gifford May 02 '23 at 14:26

0 Answers0