none
Exceptions in the Excel applications from select and hyperlink after migrating office from 2010 to 2016 RRS feed

  • Question

  • Hi,

    I have migrated my excel vsto client from 2016 to 2016 while running my solution i am getting errors below api's

    TemplateSheet.Cells(1, 1).select()

    .Cells(periodRow, columnIndex).Hyperlinks(1)

    but working well on office 2010

    TIA


    Tuesday, September 5, 2017 11:43 AM

All replies

  • Hi Vijay Akkaladevi,

    Are you developing VB.Net VSTO add-ins?

    Could please provide entire code of the method/function? What do you want to do via your code and what error message do you get?

    Best Regards,

    Terry

    Wednesday, September 6, 2017 5:47 AM
  •   TemplateSheet = Globals.ThisWorkbook.Sheets.Add(After:=Globals.ThisWorkbook.ActiveSheet)

     TemplateSheet.Activate()
     TemplateSheet.Cells(1, 1).select() // this throwing exception(Select method of Range class failed)

     With TemplateSheet (of type Worksheet)
                Dim tmpobj As Object
                Dim RARange As Range = Nothing
                Dim LineType As String
                Dim RALineStRow As Integer = 0
                If stratRow <= EndRow Then
                    Dim columnIndex As Integer = 20
                    If columnIndex > 0 Then
                        tmpobj = .Cells(19, columnIndex).Hyperlinks(1) //this throws exception as nvalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"}
    End If

    Ps: both working well on office2010 and Some times it's working well in 2016 some times those are throwing exception


    Wednesday, September 6, 2017 6:09 AM
  • Hi Vijay Akkaladevi,

    ->TemplateSheet.Cells(1, 1).select() // this throwing exception(Select method of Range class failed)

    Please try to use below code.

    Imports Excel = Microsoft.Office.Interop.Excel
    
    Dim xlApp As Excel.Application
    
            Dim xlWorkbook As Excel.Workbook
    
            Dim xlWorksheet As Excel.Worksheet
    
            xlApp = Globals.ThisAddIn.Application
    
            xlWorkbook = xlApp.ActiveWorkbook
    
            xlWorksheet = xlWorkbook.ActiveSheet
    
            TemplateSheet = xlWorkbook.Sheets.Add(After:=xlWorksheet)
    
            TemplateSheet.Cells(1, 1).Select()

    ->tmpobj = .Cells(19, columnIndex).Hyperlinks(1)

    I could reproduce your issue when the cell does not have any hyperlink.

    I would suggest you check if the cell has hyperlink before calling hyperlink object.

    Just like this.

        If .Cells(19, columnIndex).Hyperlinks.Count > 0 Then
    
                  tmpobj = .Cells(19, columnIndex).Hyperlinks(1)
    
             End If

    Best Regards,

    Terry

    Thursday, September 7, 2017 8:59 AM
  • I don't have add-in it's a workbook project

    Dim wb As Excel.Workbook  wb = Globals.ThisWorkbook.Application.Workbooks.Open(Filename:=fileName, ReadOnly:=False)
    TemplateSheet As Worksheet TemplateSheet = Globals.ThisWorkbook.Sheets.Add(After:=Globals.ThisWorkbook.ActiveSheet)
      TemplateSheet.Activate()
      wb.Sheets(sheet.Name).Range("A1").Select

    I changed to following

    wb = Globals.ThisWorkbook.Application.Workbooks.Open(Filename:=fileName, ReadOnly:=False)
                wb.Activate()
    TemplateSheet = Globals.ThisWorkbook.Sheets.Add(After:=Globals.ThisWorkbook.ActiveSheet)
    TemplateSheet.Activate()
                        TemplateSheet.Cells(1, 1).select()

    I did some thing like this. Even though i am getting same error

    Thursday, September 7, 2017 9:27 AM
  • Hi vijay Akkaladevi,

    I create a workbook project and failed to reproduce your issue. Your code works for me.

    If cell(1,1).select() does not work for you, you could try to use TemplateSheet.Range("A1").Select().

    I also suggest you create a new blank workbook project to do the test.

    Besides, I would suggest you share your solution file and excel file so we could try to reproduce your issue.

    You could share file with One Drive and put link here.

    Best Regards,

    Terry

    Friday, September 8, 2017 9:53 AM
  • When i used TemplateSheet.Range("A1").Select().

    I Used range i got the error as 

       at Microsoft.Office.Interop.Excel._Worksheet.get_Range(Object Cell1, Object Cell2).

    and also this error i am getting some times

       at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       at Microsoft.Office.Interop.Excel.Range.Select()


    Wednesday, September 13, 2017 6:14 AM
  • Hi vijay Akkaladevi,

    I would suggest you share your project so we could try to reproduce your issue.

    Here is my test project and you could take reference.

    https://1drv.ms/u/s!ArC0gnwxLv5qhisKvsZlefArOmfY

    Thanks for understanding.

    Best Regards,

    Terry

    Thursday, September 14, 2017 9:35 AM