cant read in excel data?

[jagguy]

Hi,

i cant read in data from excel 97 using vb.net 2003.

I get an exception error .

all i want to do is read in values from excel initially. I want to just test it using msgbox.

Dim XL As Object 'workaround for excel 97
        XL = Interaction.CreateObject("Excel.Application")
        Dim XlWbk As Excel.Workbook = XL.Workbooks.Add()
 
        Dim MyString As String
        MyString = Now().ToString("yyyy-MM-dd")
        Try
 
            ' XL.Visible = True
 
 
            Dim XlSh As Excel.Worksheet = CType(XlWbk.Sheets(1), Excel.Worksheet)
            Dim range As Excel.Range
            Dim xlWsheet As Excel.Worksheet = XL.Worksheets("Sheet1")
            Dim r As Excel.Range
 
            XlWbk = XL.Workbooks.Open("mytest.xls")
 
            XlWbk.Activate() '' System.Runtime.InteropServices.COMException,
            range = XlSh.Range("A1").Value
 
            XlWbk.Close()
 
 
            XL.Quit()
            MsgBox(r.Cells(0, 1).Value)
 
 

[QVeen72]

Hi,

In VB.net, "Activate" is used for many Objects. May be the error is because of that, use "CType"

Dim XL As Object 'workaround for excel 97
        XL = Interaction.CreateObject("Excel.Application")
        Dim XlWbk As Excel.Workbook = XL.Workbooks.Add()
 
        Dim MyString As String
        MyString = Now().ToString("yyyy-MM-dd")
        Try
 
            ' XL.Visible = True
 
 
            Dim XlSh As Excel.Worksheet = CType(XlWbk.Sheets(1), Excel.Worksheet)
            Dim range As Excel.Range
            Dim xlWsheet As Excel.Worksheet = XL.Worksheets("Sheet1")
            Dim r As Excel.Range
 
            XlWbk = XL.Workbooks.Open("mytest.xls")
 
            XlWbk= CType(XL.Workbooks.Item(1), Excel.WorkBook)              
 
            XlWbk.Activate() 
            range = XlSh.Range("A1").Value
 
            XlWbk.Close()
 
 
            XL.Quit()
            MsgBox(r.Cells(0, 1).Value)
 
 

REgards
Veena

[jagguy]

Hi,
I have changed the code and still get an error when i simply try to display a value from excel.

Dim XL As Object

XL = Interaction.Cre ateObject("Exce l.Application")

Dim XlWbk As Excel.Workbook = XL.Workbooks.Ad d()

Try


' XlSh.Range("A2" ).Value = MyString 'Set a value
Dim xlsApp As Excel.Applicati on
Dim xlsWB As Excel.Workbook
Dim xlsSheet As Excel.Worksheet
Dim xlsCell As Excel.Range
Dim xlsDatei As String

xlsApp = New Excel.Applicati on
' xlsApp.Visible = True
xlsWB = xlsApp.Workbook s.Open("book1.x ls")
xlsSheet = xlsWB.Worksheet s(1)
'xlsCell = xlsSheet.Range( "A1")
xlsCell = xlsSheet.Cells( 1, 1).value() 'cast error

MsgBox(xlsCell)

[jagguy]

also i cant use ctype with activate

CType(XlWbk, Excel.Worksheet ).Activate() 'error

I cant find any basic tutorial with vb.net and excel where you can read data from a excel file and write values back eg just summing up a few columns and write back the total.

Is this all possible because i have spent hours on seemingly just basici connection and basic tasks. Where is the help in vb.net for excel?

[QVeen72]

Hi,

I have changed the code in 19th line.(prev post)
You have not done the changes in your code..

WorkBook cannot be Casted to Worksheet.. Try This:

CType(XlWbk, Excel.WorkBook) .Activate()

Regards
Veena

[jagguy]

i still get an error as before with this

 
 
            Dim XlSh As Excel.Worksheet = CType(XlWbk.Sheets(1), Excel.Worksheet)
            Dim range As Excel.Range
 
            XlWbk = XL.Workbooks.Open("mytest.xls")
            XlWbk = CType(XL.Workbooks.Item(1), Excel.Workbook)
            CType(XlWbk, Excel.Workbook).Activate() 'same error as before
 
 
 

[jagguy]

i can connect now to a excel sheet and open and save to it.

Is there a way to assign a range of values read in from excel moved to a datagrid?