0

Brief details:

Running Office 360 with Excel 64bit installed. Application is built on .NET 4.6 and CPU type is set to x64

The code that's not working - line marked where it fails

public bool CreateInvoice()
    {
        try
        {
            //Write file
            var setting = new Classes.Settings();

            string WritePath = setting.DefaultSavePath + "\\Invoices\\" + InvoiceDetails.CustomerID + "\\" + InvoiceDetails.InvoiceNumber + ".xlsx";
            if (!System.IO.Directory.Exists(setting.DefaultSavePath + "\\Invoices\\" + InvoiceDetails.CustomerID))
                System.IO.Directory.CreateDirectory(setting.DefaultSavePath + "\\Invoices\\" + InvoiceDetails.CustomerID);
            if (System.IO.File.Exists(WritePath))
                System.IO.File.Delete(WritePath);

            System.IO.File.WriteAllBytes(WritePath, AccountManagement.Properties.Resources.Invoice);

            //Prepare Excel
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbooks xlWorkBooks = null;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null;
            Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
            Microsoft.Office.Interop.Excel.Worksheet xlExportSheet = null;

            Classes.GetExcelProcessID getExcelProcessID = new GetExcelProcessID();


            xlWorkBooks = xlApp.Workbooks; //***********FAILS HERE*************
            xlWorkBook = xlWorkBooks.Add(WritePath);
            xlSheets = xlApp.Worksheets;
            xlExportSheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlSheets[1]);


            var cells = xlExportSheet.Cells;

            var Process = Classes.GetExcelProcessID.ExcelProcesID(xlApp);

            try
            {
                xlApp.Visible = false;
                xlApp.DisplayAlerts = false;

                //This is where I make my edits.

                xlWorkBook.SaveAs(WritePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
                xlWorkBook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, setting.DefaultSavePath + "\\Invoices\\" + InvoiceDetails.CustomerID + "\\" + InvoiceDetails.InvoiceNumber + ".pdf");
                InvoiceSavePath = setting.DefaultSavePath + "\\Invoices\\" + InvoiceDetails.CustomerID + "\\" + InvoiceDetails.InvoiceNumber + ".pdf";
                InvoiceDetails.InvoiceSavePath = InvoiceSavePath;

            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                //Ensue all COM objects are closed and realesed or EXCEl will remain open.                             
                xlWorkBook.Close(0, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                xlWorkBooks.Close();
                //xlApp.Application.Quit();
                xlApp.Quit();

                while (Marshal.FinalReleaseComObject(xlApp) != 0) { }
                while (Marshal.FinalReleaseComObject(xlWorkBooks) != 0) { }
                while (Marshal.FinalReleaseComObject(xlWorkBook) != 0) { }
                while (Marshal.FinalReleaseComObject(xlSheets) != 0) { }
                while (Marshal.FinalReleaseComObject(xlExportSheet) != 0) { }
                while (Marshal.FinalReleaseComObject(cells) != 0) { }

                xlApp = null;
                xlWorkBooks = null;
                xlWorkBook = null;
                xlSheets = null;
                xlExportSheet = null;
                cells = null;

                try
                {
                    Process.Kill();
                }
                catch
                {
                }

                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }

        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            return false;
        }

        return true;

    }

This is the exception I keep getting.

Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application'

I initially thought the issue was about my application possibly still being built at x86 but I have a interop function with Word that runs perfectly fine - and that's 64bit.

I've tried google but all the fixes are to do with 64bit office and a 32bit application. Any tips?

Lift
  • 546
  • 2
  • 4
  • 24
  • 1
    There some reason you need to use Excel interop rather than EPPlus or ClosedXML or Open XML SDK? That'd simplify this a lot. And by the way: returning a boolean from this method doesn't make a lot of sense. It'd be better to let the exception bubble up to the point it can be handled. – mason Mar 10 '20 at 20:38
  • @mason worth noting: EPPlus is going paid: So maybe it's a corporate app unwilling to pay or use library that might not be around for a critical bug. – Austin T French Mar 10 '20 at 20:41
  • @AustinTFrench True, but I also mentioned two other libraries, including one that has the backing of Microsoft. – mason Mar 10 '20 at 20:41
  • @mason I only mentioned anything because it is basically the default recommendation I see. It's great and was meant partly for the OP as well, as it's sadly less viable now than it has been. – Austin T French Mar 10 '20 at 20:45
  • What happens if you get rid of this line: "Microsoft.Office.Interop.Excel.Workbooks xlWorkBooks = null;" and replace this one:"xlWorkBooks = xlApp.Workbooks;" with: "var xlWorkBooks = xlApp.Workbooks;" – Kevin Mar 10 '20 at 20:47
  • https://stackoverflow.com/questions/28066719/unable-to-cast-com-object-of-type-microsoft-office-interop-excel-applicationcla – Hans Passant Mar 10 '20 at 21:02
  • @Kevin - still throws the same exception unfortunately. – Lift Mar 10 '20 at 22:48
  • @HansPassant - I've followed that thread and still have the same issue, thanks though. – Lift Mar 10 '20 at 22:49
  • @Lift At that line or later in the code? – Kevin Mar 11 '20 at 13:29
  • @Kevin - That line causes the exception. – Lift Mar 11 '20 at 22:47
  • This is not VSTO, right? – Hambone Mar 12 '20 at 00:39
  • @Hambone - No it is no. I've switched to using ClosedXML as Mason suggested, however, it would be nice to know why this doesn't work! I'm completely stuck. – Lift Mar 12 '20 at 02:46

0 Answers0