Selecting a range using numeric index

[drkestrel]

A Cell could be referenced using the numeric row column index like

objXlsWorksheet.Cells(2,1)

for Cell A2.

But how could I select a Range using the numeric index (e.g. B2:N50)??

 

[TonyJollans]

Hi drkestrel,

Sure you'll get lots of quick replies to this one, here's my effort, try:

objXlsWorksheet.Range(Cells(2, 2), Cells(50, 14))

Enjoy,
Tony

 

[drkestrel]

It works in VBA, but I am acutally programming in Visual Basic .NET, added Excel as a project reference, and with Option Strict on, I am having trouble casting Cells(2,2) to an Excel.Range interface, I get exception saying Cannot convert to 'interface range'

Also tried, Diming objXlsWorksheet as Excel.WorksheetClass, but unfortunately both the following failing with exception Cannot convert to 'class Excel.WorksheetClass'
objXlWorkSheet = DirectCast(objXlWorkBk.Worksheets.Add(),
Excel.WorksheetClass)
objXlWorkSheet = DirectCast(objXlWorkBk.Worksheets.Item(1), Excel.worksheetClass)


Also tried using .NET's Type library to assembly converted (tlbimp.exe) to convert Excel.exe (from Office XP) to a .NET assembly, but unfortunately, same errors!

Note, without option strict on, running objXlWorksheet.Cells(2,2).Address in the command window quite happily returns $B$2.

 

[TonyJollans]

Have to bow out of this one, I'm afraid. I know nothing about .NET. There must be someone out there who does, though.

Good luck,
Tony

 

[Zathras]

I don't know .NET either, but perhaps you could use something like this:
[blue]

Option Explicit

Sub test()
  MsgBox BuildCellAddress(2, 2) & ":" & BuildCellAddress(50, 14)
End Sub

Function BuildCellAddress(ARow As Long, AColumn As Integer) As String
  BuildCellAddress = Chr(64 + AColumn) & ARow
End Function

[/color]

What I don't understand is if you can use

    objXlsWorksheet.Cells(2,1) for Cell A2.

then why can't you use

    objXlsWorksheet.Range(Cells(2,2),Cells(50,14)) for Cells B2:N50?

 

[xlbo]

Heh heh - that's what I didn't understand either - hence no answer from me. Maybe .NET doesn't fully implement all of excel's native methods and properties.....

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Zathras]

Geoff: Or maybe where he is using it only a single cell range is allowed. He didn't give us a lot to work with.

 

[xlbo]

One thing I have noticed - in the 1st thread, the syntax is:
objXlsWorksheet.Cells(2,1)
and also in the latest thread from drKestrel, they post:
running objXlWorksheet.Cells(2,2).Address

Now, both you and Tony have posted replies that conform to the syntax
objXlWorksheet.Range(Cells(2,2))

DrKestrel - have you tried using the cells syntax with the RANGE qualifier ??

I used to use that whenever I used the cells syntax and it was not that long ago I stopped (mainly due to laziness)

DrKestrel - if you want to use a multi cell reference, you MUST use the range qualifier as in:
Range(cells(2,1),cells(10,4)).select
as the Cells object refers to singular cells only and it might be that you need to use the RANGE qualifier for single cell refs in .NET......... just a thought

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde