PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
How to get ActiveCell>Row Number in .Net?-VBForums
Results 1 to 4 of 4

Thread: How to get ActiveCell>Row Number in .Net?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 2016
    Posts
    94

    How to get ActiveCell>Row Number in .Net?

    I am trying to get the row number of the ActiveCell on my worksheet using .Net however I have had no luck. I have reviewed the Excel Object Model and tried different things but I can't quite figure out how to get this done.

    When I try and find the activecell below I get the error "Public member 'ActiveCell' on type 'Range' not found.

    I have also tried using Cells.ActiveCell but I get the same error message. Actually, '.Range' will not work at all. I have to use .Cells to query and ranges on the worksheet.

    The most success I have had is using Cells.Rows.Count to try and return some integer reference to use with this range:

    TagIdentifier = objWorkbook.Cells(RowNum, 50).value()

    Code:
    Public Sub InitiateExcel()
    
            Const excelFileName As String = "Path to excel file"
            Dim objWorkbook As Object = GetObject(excelFileName)
            Dim objWorksheet As Object = objWorkbook.Worksheets("Sheet1")
    
            If objWorkbook Is Nothing Then
                Exit Sub
            End If
    
            Dim RowNum As Integer
            RowNum = objWorksheet.Range.ActiveCell
    
            MsgBox(RowNum)
    
            'Do stuff with Excel
            'TagIdentifier = objWorkbook.Cells(RowNum, 50).value()
            'ufs.Disp.SetHighlight(TagIdentifier, 1)
    
        End Sub
    It should be noted that I don't have access to the Microsoft.Office.Interop namespace. Otherwise I would use it.

    Thanks.

  2. #2
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: How to get ActiveCell>Row Number in .Net?

    You sure do manage to make make your life difficult.

    Quote Originally Posted by JohnnyWaffles View Post
    It should be noted that I don't have access to the Microsoft.Office.Interop namespace. Otherwise I would use it.
    Why not? Even if you choose to ultimately use late-binding, there is no reason not to add the Excel reference to your project during development to ensure proper syntax.

    I would define a compiler directive constant (UseLateBinding) to assist with this.

    Name:  Compiler Option.jpg
Views: 1044
Size:  28.3 KB

    I prefer to setup any late-binding in a separate code file. Luckily VB.Net has the concept of partial classes that allows you use multiple code files to define a class; this is how it hides a form's visual designer code.
    VB.Net Code:
    1. #If UseLateBinding Then
    2. Option Strict Off
    3. Imports Excel = Microsoft.Office.Interop.Excel
    4. #End If
    5.  
    6. Partial Public Class Form1
    7.     Public Sub InitiateExcel()
    8.  
    9.         Const excelFileName As String = "Path to excel file"
    10.  
    11. #If UseLateBinding Then
    12.         Dim objWorkbook As Object = GetObject(excelFileName)
    13.         dim app as Object = objWorkbook.Application
    14.         Dim objWorksheet As Object = objWorkbook.Worksheets("Sheet1")
    15.         Dim rng as Object
    16.         Dim objWorkbook As Excel.Workbook = CType(GetObject(excelFileName), Excel.Workbook)
    17.         Dim app As Excel.Application = objWorkbook.Application
    18.         Dim objWorksheet As Excel.Worksheet = CType(objWorkbook.Worksheets("Sheet1"), Excel.Worksheet)
    19.         Dim rng As Excel.Range
    20.  
    21. #End If
    22.  
    23.         If objWorkbook Is Nothing Then
    24.         Exit Sub
    25.         End If
    26.  
    27.         Dim RowNum As Integer
    28.         rng = app.ActiveCell
    29.         RowNum = rng.Row
    30.  
    31.         MsgBox(RowNum)
    32.  
    33.     End Sub
    34. End Class
    ActiveCell is property on an Excel.Application object. The code above shows how to get this value. However, it is generally inadvisable to use Active_Anything; instead, create concrete references to you need object.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Dec 2016
    Posts
    94

    Re: How to get ActiveCell>Row Number in .Net?

    Quote Originally Posted by TnTinMN View Post
    You sure do manage to make make your life difficult. .
    This is more true than you know haha. For some reason I always overcomplicate things. Anyway, I should be more specific about my development environment. I only have access to an .Net interpreter within a CAD environment where I have access to a limited number of namespaces. Unfortunately Excel libraries are not included. I'm not allowed to compile code outside of that. Otherwise I would have used Excel libraries.

    As for the use with ActiveCell, my program requires the location of the cell selected by the user for processing. I don't know how else to reference a user selected cell.

    Your code for latebinding worked by the way! Thank you for your help. By the way, exactly what is LateBinding lol?

  4. #4
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: How to get ActiveCell>Row Number in .Net?

    Quote Originally Posted by JohnnyWaffles View Post
    This is more true than you know haha. For some reason I always overcomplicate things. Anyway, I should be more specific about my development environment. I only have access to an .Net interpreter within a CAD environment where I have access to a limited number of namespaces.
    I've never heard of a .Net interpreter before, but a quick search indicates that at least one exists. Anyways, I would investigate its ability to add external references further as the inability to do so seems to be a severe handicap that most would find unacceptable.

    Quote Originally Posted by JohnnyWaffles View Post
    By the way, exactly what is LateBinding lol?
    see: Early and Late Binding (Visual Basic)

    Also, my standard advice for Excel automation via .Net:

    Prototype the code in Excel's VBA IDE to get a working model - the instant feedback you get in this environment will speed your development. Converting working VBA code to VB.Net is relatively painless.

    Use the Excel macro recorder to obtain the general syntax for the command. Also learn to use the "Object Explorer" (hit F2 key) to learn which type a given method or property is defined on. Use the built-in help when needed (hit F1 Key).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width