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
?
CodePudding user response:
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