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.
1 Attachment(s)
Re: How to get ActiveCell>Row Number in .Net?
You sure do manage to make make your life difficult. :rolleyes:
Quote:
Originally Posted by
JohnnyWaffles
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.
Attachment 143903
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:
#If UseLateBinding Then
Option Strict Off
#Else
Imports Excel = Microsoft.Office.Interop.Excel
#End If
Partial Public Class Form1
Public Sub InitiateExcel()
Const excelFileName As String = "Path to excel file"
#If UseLateBinding Then
Dim objWorkbook As Object = GetObject(excelFileName)
dim app as Object = objWorkbook.Application
Dim objWorksheet As Object = objWorkbook.Worksheets("Sheet1")
Dim rng as Object
#Else
Dim objWorkbook As Excel.Workbook = CType(GetObject(excelFileName), Excel.Workbook)
Dim app As Excel.Application = objWorkbook.Application
Dim objWorksheet As Excel.Worksheet = CType(objWorkbook.Worksheets("Sheet1"), Excel.Worksheet)
Dim rng As Excel.Range
#End If
If objWorkbook Is Nothing Then
Exit Sub
End If
Dim RowNum As Integer
rng = app.ActiveCell
RowNum = rng.Row
MsgBox(RowNum)
End Sub
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.
Re: How to get ActiveCell>Row Number in .Net?
Quote:
Originally Posted by
TnTinMN
You sure do manage to make make your life difficult. :rolleyes:.
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?
Re: How to get ActiveCell>Row Number in .Net?
Quote:
Originally Posted by
JohnnyWaffles
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
By the way, exactly what is LateBinding lol?
see: Early and Late Binding (Visual Basic)
Also, my standard advice for Excel automation via .Net:
Quote:
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).