VB.net Excel.worksheet().cells().Value

Question

Trying to write information in Excel worksheet But have some strange problems ;) i looked info on Google but no result.. So plz help )

I Add reference Microsoft.excel 14.0 Object Library and also Import Microsoft.Office.interop

I need to get Value from specific cell so i use command Checker = shXL.cells(1,1). And here i don't have Value.... i only got this (Equal / GetHashCode / GetType / toString) So question is WHY i don't have (.Value) for .cells and where is an Error

        Dim appXL As Excel.Application
        Dim wbXL As Excel.Workbook
        Dim wbsXL As Excel.Workbooks
        Dim shXL As Excel.Worksheet
        Dim Checker As Integer

        appXL = CreateObject("excel.application")
        appXL.Visible = True

        wbsXL = appXL.Workbooks
        wbXL = wbsXL.Open("D:\Некорректные Переключения\Base.xlsx", , , , 12121)
        shXL = wbXL.ActiveSheet
        Checker = shXL.Cells(1, 1).value()

Best Regards and THX!

score 10 · Accepted Answer · answered Apr 11 '14 at 06:23

10

That's because .Cells() returns an object.

You can try converting it to a Excel Cell object in another step, or you can try this (for example):

shXL.Range("A2").Value

With conversion will be:

Dim xRng As Excel.Range = CType(shXL.Cells(3,3), Excel.Range)
Dim val As Object = xRng.Value()

answered Apr 11 '14 at 06:23

SysDragon

9,692
15
60
89

Also you can use like that: shXL.Cells(3,3) = "Example text" – altan yuksel Sep 26 '17 at 09:22

score 4 · Answer 2 · answered Apr 11 '14 at 06:24

4

With Excel interop, a lot of the time a return value will be in the form of an Object so you need to cast to the correct type to get it's actual properties.

So do something like this (my VB is rusty so may not be completely correct)...

Dim rng as Excel.Range

rng = CType(shXL.Cells(1, 1), Excel.Range)
Checker = rng.Value

Note: I've separated it out into two lines because it's important not use double-dot references with Office interop (e.g., Worksheet.Cell.Value) because you end up with objects you can't release, which will cause issues with Excel not closing properly.

Note2: the .Value property also returns as an object so you'll probably want to cast that too

answered Apr 11 '14 at 06:24

MrBlue

830
6
13

Hi MrBlue, where can I get more info about the double-dot reference you mention? – Ama Mar 28 '19 at 23:58
1

@Ama I haven't used Excel interop in years so can't really point you to a current source (beyond suggesting a Google search). That said, although it's all over the place it seems this may not be the best advice - see [this post](https://stackoverflow.com/questions/25134024/clean-up-excel-interop-objects-with-idisposable/25135685#25135685) from Hans Passant. – MrBlue Apr 08 '19 at 05:29
Indeed, from the post you pointed it looks like the double dot rule is nothing but a myth.. ! – Ama Apr 16 '19 at 12:03

VB.net Excel.worksheet().cells().Value

2 Answers2

Linked