none
Exception from HRESULT: 0x800A03EC (System.Runtime.InteropServices.COMException) error on Select method and ('System.InvalidCastException') Visible method. RRS feed

  • Question

  •   I am getting Exception from HRESULT: 0x800A03EC on Select method and 'System.InvalidCastException' in Visible method after upgrading the excel to 2016. Any idea? It works perfect in Excel 2010.

       Application oExcel = CommonEnvironment.Instance.Excel;
                Workbook oWBAuthorized;
                Workbook oWBNew;
                Worksheet objAuthorizedFileExcelSheet;
                Worksheet objNewFileExcelSheet;

        oExcel.Workbooks[newFileName].Unprotect(password);
                    oExcel.Workbooks[authorizedFileName].Unprotect(password);

                    //get the input counts from authorized file
                    ((Worksheet)(oWBAuthorized.Worksheets["Rapid II"])).Visible = XlSheetVisibility.xlSheetVisible;
                    ((Worksheet)(oWBAuthorized.Worksheets["Rapid II"])).Select(Type.Missing);
    Tuesday, August 15, 2017 7:26 PM

All replies

  • Hi Jenifer Dholakia,

    I try to make a test with this these 2 properties with Excel 2016.

    below is the testing result.

     private void button1_Click(object sender, EventArgs e)
            {
                Globals.ThisWorkbook.Unprotect("123");
                Globals.Sheet2.Visible = Excel.XlSheetVisibility.xlSheetVisible;
                Globals.Sheet2.Select();
            }

    on my side it is working correctly without any error.

    I suggest you to check that your code unprotect the workbook correctly.

    when I try to comment that line in my above code then I got error below.

    try to check that you are trying to unprotect the correct file.

    I can see you are working with multiple files.

    other thing I can see in your code that you had created the objects but where did you initialized them in your code? I cant see that part in your code. it is possible that you had only posted the problematic part of the code.

    so you can also consider to check it and verify that it is correct.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 16, 2017 2:57 AM
    Moderator
  • Thanks Deepak. Below is the code. Please tell me which objects I have to initialise. Yes it does not work for multiple files. If I declare oWBNew  before oWBAuthorized  object then I don't get the error. But then I need to understand the reason.

      private static TypedCollection<string, PortfolioReportError> Check_Cells(string authorizedFilePathName, string newFilePathName, string password)
            {
                TypedCollection<string, PortfolioReportError> pfRptErrors = new TypedCollection<string, PortfolioReportError>();
                TypedCollection<string, PortfolioReportError> colPfRptErrors = new TypedCollection<string, PortfolioReportError>();
                
                //define variable for excel
                Application oExcel = CommonEnvironment.Instance.Excel;
                Workbook oWBAuthorized;
                Workbook oWBNew;
                Worksheet objAuthorizedFileExcelSheet;
                Worksheet objNewFileExcelSheet;

                //define variable of error object
                long i, j, x;

                //define variables others
                string authorizedFileName;
                string newFileName;

                int sheetCount;

                int inputCount;
                string value;
                object objValue;
                bool dimensionsOK;
                bool debugFlag;
                string monthAuthorized;
                string monthNew;

                double number1;
                double number2;

                string[] array;
                object[,] authorizedFileBuffer;
                object[,] newFileBuffer;

                object[,] authorizedFileFormulas;
                object[,] newFileFormulas;

                // define constant
                const string STR_REFRESH_MACRO_NAME = "RefreshMacro";

                    //set the error code                
                    debugFlag = false;

                    //get the only file name (not the path) from full names for authorized and new file
                    authorizedFileName = authorizedFilePathName.Substring(authorizedFilePathName.LastIndexOf("\\") + 1);
                    newFileName = newFilePathName.Substring(newFilePathName.LastIndexOf("\\") + 1);

                    //for debugging purpose - visible excel while running the check
                    oExcel.Visible = debugFlag;
                    oExcel.ScreenUpdating = debugFlag;

                    //open both work books 
                    oWBAuthorized = oExcel.Workbooks._Open(authorizedFilePathName, 0, true, 5,
                                Type.Missing, "", true, XlPlatform.xlWindows, "\t", false, false,
                                0, true);
                    //set for Addins 
                    
                    oWBNew = oExcel.Workbooks._Open(newFilePathName, 0, true, 5,
                                Type.Missing, "", true, XlPlatform.xlWindows, "\t", false, false,
                                0, true);

                    //get the month from both files so check if referesh macro needs to run
                    monthAuthorized = ((Worksheet)(oWBAuthorized.Worksheets["Rapid II"])).get_Range("Month", Type.Missing).Value2.ToString();
                    monthNew = ((Worksheet)(oWBNew.Worksheets["Rapid II"])).get_Range("Month", Type.Missing).Value2.ToString();

                    //activate the authorized file to run referech macro
                    ((_Workbook)oExcel.Workbooks[authorizedFileName]).Activate();

    Monday, August 28, 2017 7:21 PM
  • Hi Jenifer Dholakia,

    in your original post , I can see that you are trying to unprotect the workbook.

    in your latest code , I did not find line of code for unprotecting the workbook.

    most possible reason is in your last code you are trying to refer some other workbook and passing the password for any other workbook leads to an error.

    in your new code I can see that you are using individual object for each workbook and also not unprotecting the file (may be this time excel file does not contain password protection). so it is working without any issue.

    now if your file contain password and you want to open and assign it to particular workbook object then you can do it easily for any particular file.

    you can pass the password as an parameter in Workbook.open.

    expression . Open( FileName , UpdateLinks , ReadOnly , Format , Password , WriteResPassword , IgnoreReadOnlyRecommended , Origin , Delimiter , Editable , Notify , Converter , AddToMru , Local , CorruptLoad )

    Workbooks.Open Method (Excel)

    so I hope , now you are clear about the code above.

    if you got the answer for your question then I suggest you to mark the answer which helped you to solve the issue.

    if you have any further questions regarding the above issue then let us know about that, we will try to provide suggestions for that.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, August 29, 2017 5:47 AM
    Moderator
  • Thanks Deepak

    In my case I have two file. And I need to compare those two files. So when I try to select the range/cell from another file. It gives error. The same code works fine in 2010 but not with 2016 Excel.

    Wednesday, September 20, 2017 1:43 PM
  • Thanks Deepak

    I unprotected the file.In my case I have two file. And I need to compare those two files. So when I try to select the range/cell from another file. It gives error. The same code works fine in 2010 but not with 2016 Excel.

    Please help urgently.

    Wednesday, September 20, 2017 1:44 PM
  • Hi Jenifer Dholakia,

    can you again try to post the latest code. we will again try to make a test with it.

    the last code you had posted is one month old. so it is possible that during this days you had modified the code.

    also let us know about the latest error and on which line this error occurred.

    if we check the last code then I can see that you are opening a 2 excel files and fetch the value of cell from worksheet.

    below is another example you can try to refer. which opens two workbooks.

    fetch the values from sheets and match it.

    it working fine on Excel 2016.

     private void Form1_Load(object sender, EventArgs e)
            {
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\v-padee\Desktop\excel files\book1.xlsx");
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
                string xlRange = xlWorksheet.get_Range("A1", "A1").Value2;
    
                Excel.Workbook xlWorkbook2 = xlApp.Workbooks.Open(@"C:\Users\v-padee\Desktop\excel files\book2.xlsx");
                Excel._Worksheet xlWorksheet2 = xlWorkbook.Sheets[1];
                string xlRange2 = xlWorksheet.get_Range("A1", "A1").Value2;
                if(xlRange== xlRange2)
                {
                    MessageBox.Show("values are same from both workbook");
                }
                else
                {
                    MessageBox.Show("values are not same from both workbook");
                }
            }

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 21, 2017 3:14 AM
    Moderator
  • Hi Deepak,

    Below code was developed 10 years back by another developer. When we upgraded to excel 2016 this functionality is failing. failed on line    ((Range)(objNewFileExcelSheet.Cells[1, 1])).Select(); (i.e second file)

     private static TypedCollection<string, PortfolioReportError> Check_Cells(string authorizedFilePathName, string newFilePathName, string password)
            {
                TypedCollection<string, PortfolioReportError> pfRptErrors = new TypedCollection<string, PortfolioReportError>();
                TypedCollection<string, PortfolioReportError> colPfRptErrors = new TypedCollection<string, PortfolioReportError>();


                //define variable for excel
                Application oExcel = CommonEnvironment.Instance.Excel;
                Workbook oWBAuthorized;
                Workbook oWBNew;
                Worksheet objAuthorizedFileExcelSheet;
                Worksheet objNewFileExcelSheet;

                //define variable of error object
                long i, j, x;

                //define variables others
                string authorizedFileName;
                string newFileName;

                int sheetCount;

                int inputCount;
                string value;
                object objValue;
                bool dimensionsOK;
                bool debugFlag;
                string monthAuthorized;
                string monthNew;

                double number1;
                double number2;

                string[] array;
                object[,] authorizedFileBuffer;
                object[,] newFileBuffer;

                object[,] authorizedFileFormulas;
                object[,] newFileFormulas;

                // define constant
                const string STR_REFRESH_MACRO_NAME = "RefreshMacro";

                    //set the error code                
                    debugFlag = false;

                    //get the only file name (not the path) from full names for authorized and new file
                    authorizedFileName = authorizedFilePathName.Substring(authorizedFilePathName.LastIndexOf("\\") + 1);
                    newFileName = newFilePathName.Substring(newFilePathName.LastIndexOf("\\") + 1);

                    //for debugging purpose - visible excel while running the check
                    oExcel.Visible = debugFlag;
                    oExcel.ScreenUpdating = debugFlag;

                  //set for Addins 
                  oWBNew = oExcel.Workbooks._Open(newFilePathName, 0, true, 5,
                            Type.Missing, "", true, XlPlatform.xlWindows, "\t", false, false,
                            0, true);

                   //open both work books 
                   oWBAuthorized = oExcel.Workbooks._Open(authorizedFilePathName, 0, true, 5,
                                Type.Missing, "", true, XlPlatform.xlWindows, "\t", false, false,
                                0, true);

                    //get the month from both files so check if referesh macro needs to run
                    monthAuthorized = ((Worksheet)(oWBAuthorized.Worksheets["Rapid II"])).get_Range("Month", Type.Missing).Value2.ToString();
                    monthNew = ((Worksheet)(oWBNew.Worksheets["Rapid II"])).get_Range("Month", Type.Missing).Value2.ToString();

                    //activate the authorized file to run referech macro
                    ((_Workbook)oExcel.Workbooks[authorizedFileName]).Activate();

                    if (monthAuthorized != monthNew)
                    {
                        oExcel.Calculation = XlCalculation.xlCalculationAutomatic;

                        oExcel.Run(STR_REFRESH_MACRO_NAME, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                          Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                          Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                           Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                          Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                           Type.Missing, Type.Missing);
                    }

                    //unprotect the both files
                    oExcel.Workbooks[newFileName].Unprotect(password);
                    oExcel.Workbooks[authorizedFileName].Unprotect(password);

                    //get the input counts from authorized file
                    ((Worksheet)(oWBAuthorized.Worksheets["Rapid II"])).Visible = XlSheetVisibility.xlSheetVisible;
                    ((Worksheet)(oWBAuthorized.Worksheets["Rapid II"])).Select(Type.Missing);
                    inputCount = Int32.Parse(oExcel.get_Range("SellerValues", Type.Missing).Value2.ToString());

                    //loop through each input and copy 
                    //INPUTS from new file to authorized file
                    for (i = 1; i <= inputCount; i++)  //Start of INPUTS copy loop
                    {
                        try
                        {
                            //selecte data from new file and copy
                            ((_Workbook)oExcel.Workbooks[newFileName]).Activate();
                            objValue = oExcel.get_Range("INPUT" + i, Type.Missing).Value2;
                            if (objValue != null)
                            {
                                value = objValue.ToString();
                                oExcel.get_Range("INPUT" + i, Type.Missing).Value2 = value;

                                //copy selected data frrom New file to Authorized file
                                ((_Workbook)oExcel.Workbooks[authorizedFileName]).Activate();
                                oExcel.get_Range("INPUT" + i, Type.Missing).Value2 = value;
                            }
                            else
                            {
                                oExcel.get_Range("INPUT" + i, Type.Missing).Value2 = null;

                                //copy selected data frrom New file to Authorized file
                                ((_Workbook)oExcel.Workbooks[authorizedFileName]).Activate();
                                oExcel.get_Range("INPUT" + i, Type.Missing).Value2 = null;
                            }
                        }
                        catch
                        {
                            //log the error as input range is not there, but continue for next one
                            pfRptErrors.Add(CreatePfRptErrorObj("Locked Input Cell", "INPUT" + i.ToString() + " cell locked", "N/A", "INPUT" + i.ToString()));

                            continue;
                        }
                    } //complete of INPUTS copy loop

                    if (pfRptErrors.Count > 0) return pfRptErrors;

                    //After successfuly copy INPUTS to authorized file, now 
                    //check that the # & Names of all sheets are the same for both books
                    if (oExcel.Workbooks[authorizedFileName].Sheets.Count != oExcel.Workbooks[newFileName].Sheets.Count)
                    {
                        pfRptErrors.Add(CreatePfRptErrorObj("Sheet count differs", "# of sheets: " + oExcel.Workbooks[newFileName].Sheets.Count.ToString(),
                                         oExcel.Workbooks[authorizedFileName].Sheets.Count.ToString(), "N/A"));

                        return pfRptErrors;
                    }

                    //After checking the sheet count is same for both files now
                    //get the total sheet count
                    sheetCount = oExcel.Workbooks[authorizedFileName].Worksheets.Count;

                    //First check if sheet names match for both files
                    for (i = 1; i <= sheetCount; i++)
                    {
                        if (((Worksheet)oExcel.Workbooks[authorizedFileName].Sheets[i]).Name !=
                            ((Worksheet)oExcel.Workbooks[newFileName].Sheets[i]).Name)
                        {
                            pfRptErrors.Add(CreatePfRptErrorObj("Sheet names differs", "Sheet name: " + ((Worksheet)oExcel.Workbooks[newFileName].Sheets[i]).Name,
                                             "Sheet name: " + ((Worksheet)oExcel.Workbooks[authorizedFileName].Sheets[i]).Name, "N/A"));
                        }
                    } //complete of Sheet names checks

                    //After checking sheet names, now 
                    //loop through and do check for each sheet
                    for (x = 1; x < sheetCount; x++)
                    {
                        objAuthorizedFileExcelSheet = (Worksheet)oExcel.Workbooks[authorizedFileName].Sheets[x];
                        objNewFileExcelSheet = (Worksheet)oExcel.Workbooks[newFileName].Sheets[x];

                        //if its seller controlled sheet don't check further
                        if (!objNewFileExcelSheet.Name.StartsWith("SC_"))
                        {
                            //unprotect and copy the Authorized sheet data into buffer
                            objAuthorizedFileExcelSheet.Unprotect(password);
                            objAuthorizedFileExcelSheet.Visible = XlSheetVisibility.xlSheetVisible;
                            ((_Worksheet)objAuthorizedFileExcelSheet).Activate();
                            ((Range)(objAuthorizedFileExcelSheet.Cells[1, 1])).Select();
                            oExcel.ActiveCell.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Select();
                            oExcel.get_Range(oExcel.Selection, oExcel.Cells[1, Type.Missing]).Select();
                            authorizedFileBuffer = (Object[,])(((Range)oExcel.Selection).Value2);
                            try
                            {
                                authorizedFileFormulas = (Object[,])(((Range)oExcel.Selection).Formula);
                            }
                            catch
                            {
                                pfRptErrors.Add(CreatePfRptErrorObj("A formula is too long on authorized file", "N/A", "N/A", objAuthorizedFileExcelSheet.Name));
                                return pfRptErrors;
                            }


                            //unprotect and copy the New sheet data into buffer
                            objNewFileExcelSheet.Unprotect(password);
                            objNewFileExcelSheet.Visible = XlSheetVisibility.xlSheetVisible;
                            ((_Worksheet)objNewFileExcelSheet).Activate();
                           ((Range)(objNewFileExcelSheet.Cells[1, 1])).Select();
                            oExcel.ActiveCell.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Select();
                            oExcel.get_Range(oExcel.Selection, oExcel.Cells[1, Type.Missing]).Select();
                            newFileBuffer = (Object[,])(((Range)oExcel.Selection).Value2);
                            
                            try
                            {
                                newFileFormulas = (Object[,])(((Range)oExcel.Selection).Formula);
                            }
                            catch
                            {
                                pfRptErrors.Add(CreatePfRptErrorObj("A formula is too long on imported file", "N/A", "N/A", objNewFileExcelSheet.Name));
                                return pfRptErrors;
                            }

                            //check that dimensions are same
                            dimensionsOK = true;

                            if ((authorizedFileBuffer == null) || (newFileBuffer == null))
                            {
                                dimensionsOK = false;
                                pfRptErrors.Add(CreatePfRptErrorObj("Sheet dimensions differ", "Dimensions for worksheets are not same:",
                                                                        "N/A", objAuthorizedFileExcelSheet.Name));

                            }

                            //check if dimensions are same
                            if (dimensionsOK)
                            {
                                if (((Array)(authorizedFileBuffer)).GetLowerBound(0) != ((Array)(newFileBuffer)).GetLowerBound(0)) dimensionsOK = false;
                                if (((Array)(authorizedFileBuffer)).GetLowerBound(1) != ((Array)(newFileBuffer)).GetLowerBound(1)) dimensionsOK = false;
                                if (((Array)(authorizedFileBuffer)).GetUpperBound(0) != ((Array)(newFileBuffer)).GetUpperBound(0)) dimensionsOK = false;
                                if (((Array)(authorizedFileBuffer)).GetUpperBound(1) != ((Array)(newFileBuffer)).GetUpperBound(1)) dimensionsOK = false;

                                if (!dimensionsOK)
                                {
                                    pfRptErrors.Add(CreatePfRptErrorObj("Sheet dimensions differ", "Dimensions for worksheets are not same:",
                                                                        "N/A", objAuthorizedFileExcelSheet.Name));
                                }
                            }

                            //now compare both sheets
                            if (dimensionsOK)
                            {
                                for (i = 1; i < ((Array)(authorizedFileBuffer)).GetUpperBound(0); i++)
                                {
                                    for (j = 1; j < ((Array)(authorizedFileBuffer)).GetUpperBound(1); j++)
                                    {
                                        try
                                        {
                                            if (authorizedFileBuffer[i, j] != null && newFileBuffer[i, j] != null)
                                            {
                                                //check if both values are not null then do the following check
                                                //fixed the value before checking
                                                if (Util_DataValidation.IsNumeric(authorizedFileBuffer[i, j].ToString()) && Util_DataValidation.IsNumeric(newFileBuffer[i, j].ToString()))
                                                {
                                                    number1 = double.Parse( authorizedFileBuffer[i, j].ToString(), System.Globalization.NumberStyles.Any);
                                                    number2 = double.Parse(newFileBuffer[i, j].ToString(), System.Globalization.NumberStyles.Any);

                                                    authorizedFileBuffer[i, j] = Math.Abs(number1) >= 10 ? Math.Round(number1, 2) : Math.Round(number1, 6);
                                                    newFileBuffer[i, j] = Math.Abs(number2) >= 10 ? Math.Round(number2, 2) : Math.Round(number2, 6);

                                                    if (authorizedFileBuffer[i, j].ToString().Length != newFileBuffer[i, j].ToString().Length)
                                                    {
                                                        array = GetFixedDecimalValues(authorizedFileBuffer[i, j].ToString(), newFileBuffer[i, j].ToString()).Split(new char[] { ',' });
                                                        authorizedFileBuffer[i, j] = array[0];
                                                        newFileBuffer[i, j] = array[1];
                                                    }
                                                }

                                                //compare formulas first
                                                //add the logic so it takes 

                                                if (Util_DataValidation.IsNumeric(authorizedFileFormulas[i, j].ToString()) && Util_DataValidation.IsNumeric(newFileFormulas[i, j].ToString()))
                                                {
                                                    number1 = double.Parse(authorizedFileFormulas[i, j].ToString(), System.Globalization.NumberStyles.Any);
                                                    number2 = double.Parse(newFileFormulas[i, j].ToString(), System.Globalization.NumberStyles.Any);

                                                    authorizedFileFormulas[i, j] = number1 >= 10 ? Math.Round(number1, 2) : Math.Round(number1, 6);
                                                    newFileFormulas[i, j] = number2 >= 10 ? Math.Round(number2, 2) : Math.Round(number2, 6);
                                                }

                                                if (authorizedFileFormulas[i, j].ToString() != newFileFormulas[i, j].ToString())
                                                {
                                                    pfRptErrors.Add(CreatePfRptErrorObj("Formulas do not match", newFileFormulas[i, j].ToString(),
                                                                                         authorizedFileFormulas[i, j].ToString(),
                                                                                         objAuthorizedFileExcelSheet.Name, ColumnLetter(j) + i.ToString()));
                                                }

                                                //compare the sheet values
                                                if (authorizedFileBuffer[i, j].ToString() != newFileBuffer[i, j].ToString())
                                                {
                                                    pfRptErrors.Add(CreatePfRptErrorObj("Cells do not match", newFileBuffer[i, j].ToString(),
                                                                                         authorizedFileBuffer[i, j].ToString(), objAuthorizedFileExcelSheet.Name, ColumnLetter(j) + i.ToString()));
                                                }
                                            }
                                            //make sure that both values are null if not any of them then log error
                                            else if (!(authorizedFileBuffer[i, j] == null && newFileBuffer[i, j] == null))
                                            {
                                                pfRptErrors.Add(CreatePfRptErrorObj("Cells do not match", (newFileBuffer[i, j]) == null? "": newFileBuffer[i, j].ToString(),
                                                                                        (authorizedFileBuffer[i, j]) == null ? "": authorizedFileBuffer[i, j].ToString(),
                                                                                         objAuthorizedFileExcelSheet.Name, ColumnLetter(j) + i.ToString()));

                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            System.Windows.Forms.MessageBox.Show("Cells do not match - there is a error on sheet = " + objAuthorizedFileExcelSheet.Name, ColumnLetter(j) + i.ToString()
                                                                                 + "ERROR: " + ex.Message);
                                            throw;
                                        }

                                    } //end of for loop for j
                                } //end of for loop for i
                            }// end of if for loops for i & j
                        } //end of if with checking for ("SC_") sheet
                    } //end of main for loop with x


                    //done with excel sheet - close all the open excel workbooks
                    oExcel.Visible = debugFlag;
                    oExcel.ScreenUpdating = debugFlag;

                    oExcel.Workbooks[authorizedFileName].Close(false, Type.Missing, Type.Missing);
                    oExcel.Workbooks[newFileName].Close(false, Type.Missing, Type.Missing);
                    //oExcel.Quit();
                    //oExcel = null;

                    //check for errors and return
                    if (pfRptErrors.Count > 0) return pfRptErrors;
                    else return null;
                
            }
    Thursday, September 21, 2017 4:34 PM
  • Hi Jenifer Dholakia,

    you had mentioned that below line cause the error.

    ((Range)(objNewFileExcelSheet.Cells[1, 1])).Select(); (i.e second file)

    I suggest you to replace above line with the line below.

    xlWorksheet.get_Range("A1", "A1").Select();

    it can able to select range in Excel 2016, I had tested on my side.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 22, 2017 1:29 AM
    Moderator
  • Thank you Deepak for your valuable time and solution. It is working now. But I get error in another line of code in select method. i.e     oExcel.ActiveCell.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Select();

    It seems 2016 excel has issue in Select method while working with the second file. I don't get error in first file. Now I have to rewrite the whole code to support 2016.

    Monday, September 25, 2017 4:34 PM
  • Hi Jenifer Dholakia,

    you had mentioned that," I get error in another line of code in select method."

    oExcel.ActiveCell.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Select();

    in your above code I can see that oExcel is an instance of Excel Application.

    I suggest you to refer the instance of Workbook.

    it will solve your issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 9, 2017 7:04 AM
    Moderator
  • Hi Deepak,

    I tried this solution but it gives me same error. Last time you suggested the method xlWorksheet.get_Range("A1", "A1").Select(); in replacement of Select method. Can you please suggest me the another method instead of using select method for below code.

       oExcel.ActiveCell.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Select();
       oExcel.get_Range(oExcel.Selection, oExcel.Cells[1, Type.Missing]).Select();

    I really appreciate your time and inputs. Thanks a lot.

    Monday, October 16, 2017 6:30 PM
  • Hi Jenifer Dholakia,

    here, I think that the issue is you have one instance of Excel Application. in which you had opened 2 workbooks.

    so I think that it is not able to decide from which workbook he need to select.

    or maybe it is trying to select from incorrect workbook which workbook active currently.

    can you show us the changes you had made based on my last suggestion.

    I will try to create a Excel instance with 2 workbooks and try to test your code.

    if there is some need to make a change in code then I will try to do that and suggest you back.

    if the Workbook is already Active and Sheet is also active then you can try to use "ActiveSheet" and then try to select the range also many solve your issue.

    so you can also try to make a test with it.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 17, 2017 2:12 AM
    Moderator