AnsweredAssumed Answered

C# PI Datalink ResizeRange gives "specified cast is not valid" unhandled exception

Question asked by s_hoskins_ on Feb 5, 2016
Latest reply on Feb 10, 2016 by s_hoskins_

My app is supposed to create PI datalink functions and then perform a resizerange to get the PI data.

The app works fine for PI functions such as:

=PIAdvCalcDat('JBR Operation'!$B$6,'JBR Operation'!$B$3,'JBR Operation'!$B$4,"1h","average","time-weighted",0,1,129,"oilsandspi")

 

But the app gives an unhandled exception for PI functions with an expression, such as these:

=PIExpVal("'21PIC880'+'21PIC880'*5.432","TODAY",1,"oilsandspi")

=PIExpDat("'21PIC880'+'21PIC880'*5.432",ParamSheet!$B$1,"1/3/2016","1H",1,"\\oilsandspi")

 

When the code runs the resizerange line I get an unhandled exception


unhandledexception.jpg

After I click "continue" the resizerange finishes correctly.

 

How can I get the code to stop creating this exception when I have an expression?

 

The code related to the resize:

 

 

        protected void SetPIFormula(ref Excel.Range currentFind)
        {
            try
            {
                        currentFind = currentFind.get_Offset(notes.ExcelRowShift, notes.ExcelColShift);
                        currentFind.Select();
                        notes.PIaddress = currentFind.Address;
                        currentFind.Formula = notes.piFormula;
                        currentFind.FormulaArray = currentFind.FormulaR1C1;
                        PerformResizeRange();
            }
            catch (Exception ex)
            {
                notes.AddComment(string.Format("There was a problem setting the PI formula {0}", ex.Message));
            }
        }

 

 

 

        protected void PerformResizeRange()
        {
            object addinName = "PI DataLink";
            try
            {
                Microsoft.Office.Core.COMAddIn myAddin = excelApp.COMAddIns.Item(ref addinName);
                myAddin.Object.SelectRange();
                myAddin.Object.ResizeRange();
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(myAddin);
            }
            catch
            {
                // do nothing
            }
        }

Outcomes