Not able to extract value from a particular cell in excel sheet

Question

I am trying to access the cell A7 of an excel file which has only one excel sheet. However i am getting the following error Public member 'Range' on type '__ComObject' not found. in the statement Dim tempValue As String = owb.Worksheets(1).Range("A7") 'Error. Why does this error come and how to correct it? Btw, I also tried using the Value and Value2 after Range in the above statement but even that does not work - surprisingly the value and value2 do not appear in intellisense after Range in the error statement in the code.

Imports System
Imports System.IO
Imports Microsoft.Office.Interop
Module Program
    Dim oxl As Excel.Application
    Dim owb As Excel.Workbook
    Sub Main()
        oxl = CreateObject("Excel.Application")
        oxl.Visible = True
        Dim path As String = "G:\Amit Kapoor\Matthews Asia\Matthews Raw Data"
        Dim names As String() = Directory.GetFiles(path, "*.xlsx")
        Dim pathofFirstfile As String = names(0)
        Console.WriteLine(pathofFirstfile) 'Works fine
        owb = oxl.Workbooks.Open(pathofFirstfile)  'Works fine
        Dim tempValue As String = owb.Worksheets(1).Range("A7") 'Error
        Console.WriteLine("Scheme Name: {0}", tempValue)
        Console.ReadLine()
    End Sub
End Module

Another related question is as follows: In the above code, I have pointed at the excel workbook with the variable oxl by using oxl to open the excel file. However, if I open it, I have to close it also - that increases the length of my code. So is there any way to associate the variable oxl with the excel workbook in order to execute excel tasks without opening the excel workbook? That is, is it possibe to make oxl point to the desired excel workbook without opening the workbook using the variable oxl?

Answer 1

I dont have VB installed so cant test, looking at examples etc it looks like you need to explicitly cast some things for it to work properly?

Imports System
Imports System.IO
Imports Microsoft.Office.Interop
Module Program
    Dim oxl As Excel.Application
    Dim owb As Excel.Workbook
    Dim ows As Excel.Worksheet
    Dim owr As Excel.Range
    Sub Main()
        oxl = CreateObject("Excel.Application")
        oxl.Visible = True
        Dim path As String = "G:\Amit Kapoor\Matthews Asia\Matthews Raw Data"
        Dim names As String() = Directory.GetFiles(path, "*.xlsx")
        Dim pathofFirstfile As String = names(0)
        Console.WriteLine(pathofFirstfile)
        owb = oxl.Workbooks.Open(pathofFirstfile)
        ows = CType(owb.Sheets(1), Excel.Worksheet)
        owr = ows.Range("A7")
        Dim tempValue As String = CStr(owr.Value)
        Console.WriteLine("Scheme Name: {0}", tempValue)
        Console.ReadLine()
    End Sub
End Module