#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2006
    Posts
    144
    Rep Power
    9

    Com Object That Has Been Separated From Its Underlying Rcw Cannot Be Used


    Hi,

    I am writing a VSTO add-in for Excel 2010 using C#. My code is below.
    Code:
    using System;
    using System.Collections.Generic;
    using Microsoft.Office.Tools.Excel;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;
    using System.Data.Odbc;
    using System.Data;
    using System.Windows.Forms;
    using System.Runtime.InteropServices;
    using Excel = Microsoft.Office.Interop.Excel;
    using Range = Microsoft.Office.Interop.Excel.Range;
    using Worksheet = Microsoft.Office.Interop.Excel.Worksheet;
    // Fred Liu. Wednesday June 11, 2012-
    
    // Need to do custom swaps
    
    namespace Fills2010
    {
        public class IRSwaps
        {
            public IRSwaps(BookRangeSettings sett, SqlDataTools sqltools)
            {
                try
                {                
                    m_xl = sett;
                    m_dataTools = sqltools;
                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Sheets["CAD_IR_Swaps"]).Select(Type.Missing);
                    general("Standard", "virs", false, "B1", "K115");
                    general("Overnight", "OIS", false, "E1", "K17");
                    general("Monthly", "irs_31", false, "B1", "C13");
                    zeros(false, "E1", "J249");
                    FRAs();
                    custom();
                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Sheets["CAD_IR_Swaps"]).Calculate();
    
                    // USD IRS
                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Sheets["US_IR_Swaps"]).Select(Type.Missing);
                    general("Standard", "VIRS_US", true, "B1", "K229");
                    zeros(true, "E1", "J5");
                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Sheets["US_IR_Swaps"]).Calculate();
                }
                catch (Exception exp)
                {
                    MessageBox.Show("Fills 2010: Error getting index options " + exp.Message + "\n Stack: " + exp.StackTrace);
                }
            }
    
            private void general(string type, string namedRange, bool isUSD, string startRangeFormat, string endRangeFormat)
            {
    
            }
    
            private void format(string startRange, string endRange, Range output)
            {
                // 2 letters ahead since columns A and B are empty
                Range format = (Range)(output.get_Range(startRange, endRange));
                format.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* \" - \"??_);_(@_)";
            }
    
            private void zeros(bool isUSD, string startRangeFormat, string endRangeFormat)
            {
    
            }
    
            private void FRAs()
            {
    
            }
    
            private void custom()
            {
                DateTime rangeReportDate = DateTime.Parse(m_xl.NamedRange("sdate").Text.ToString());
                Range output = m_xl.NamedRange("CIRS");
                string sql = "SQL here";
                ADODB.Recordset rds = m_dataTools.Query_SF(sql);
                output.ClearContents();
                output.CopyFromRecordset(rds, Type.Missing, Type.Missing);
                format("C1", "K5000", output); // Formats the cells as currency
                format("N1", "O5000", output);
                format("Q1", "R5000", output);
                format("U1", "V5000", output);
    
                string date = m_xl.Range("CAD_IR_Swaps", "E1581").Value2.ToString();
                bool ignore = false;
                int legs = 1;
                for (int i = 1581; i < rds.RecordCount + 1580; i++) // Starting row
                {
                    /* ERROR POSSIBLE */ string currentDate = m_xl.Range("CAD_IR_Swaps", "E" + i.ToString()).Value2.ToString();
                    string nextDate = currentDate;
                    if (m_xl.Range("CAD_IR_Swaps", "E" + (i + 1).ToString()).Value2 != null)
                    /* ERROR POSSIBLE */     nextDate = m_xl.Range("CAD_IR_Swaps", "E" + (i + 1).ToString()).Value2.ToString();
    
                    // If not 1st row and the current row's trade date is the same as the last row (i.e. same swap)
                    if (i > 1581 && currentDate == date)
                    {
                        if (ignore == false)
                        {
                            /* ERROR POSSIBLE */ m_xl.Range("CAD_IR_Swaps", "C" + i.ToString() + ":M" + i.ToString()).ClearContents();
                            legs++;
                        }
                        else
                            ignore = false;
                    }
                    if (nextDate != date || i >= rds.RecordCount + 1580)
                    {
                        if (nextDate != null)
                        {
                            ignore = true;
                            date = nextDate;
                        }
                        m_xl.Range("CAD_IR_Swaps", "M" + (i - legs + 1).ToString()).Value2 = legs;
                        m_xl.Range("CAD_IR_Swaps", "T" + (i - legs + 1).ToString()).Value2 = legs;
                        legs = 1;
                    }
                }
                // Get rid of the last row
                m_xl.Range("CAD_IR_Swaps", "C" + (rds.RecordCount + 1580).ToString() + ":M" + (rds.RecordCount + 1580).ToString()).ClearContents();
    
                // Count the last swap
                legs = 1;
                int k = 1580 + rds.RecordCount;
                while (m_xl.Range("CAD_IR_Swaps", "E" + k.ToString()).Value2 == null)
                {
                    k--;
                    legs++;
                }
                m_xl.Range("CAD_IR_Swaps", "M" + (1580 + rds.RecordCount - legs + 1).ToString()).Value2 = legs;
                m_xl.Range("CAD_IR_Swaps", "T" + (1580 + rds.RecordCount - legs + 1).ToString()).Value2 = legs;
            }
    
            private BookRangeSettings m_xl;
    
            private SqlDataTools m_dataTools;
        }
    }
    I've removed the contents of the functions for brevity. When I run this, it works on the 1st run (everything fills in the cells and no errors), however if I click the same button again, I get an error saying

    "Com Object That Has Been Separated From Its Underlying Rcw Cannot Be Used"

    And all subsequent runs show this error.

    I Googled and the results show solutions as releasing the objects via Marshal.Release (didn't work) and mentions multi-threaded apps, which I doubt my add-in is ... Unless it's running multi-threaded because Excel 2010 is? If so, how can I fix that?

    Any ideas as to why this happens?
    Last edited by fred2028; July 13th, 2012 at 02:36 PM.
    Cheers,
    Fred Liu

IMN logo majestic logo threadwatch logo seochat tools logo