I am working on a application which needs to read data from a excel file and then, depending on the value, write it to a access file (.mdb)
the problem is that how do i give the condition (till end of file )for the excel sheet
these are the excel and access connections i am using:

Set xlApp = New Excel.Application
xlApp.Workbooks.Open ("E:\nikhil\excel2access\input.xls") 'the excel file is now opened
xlApp.Worksheets("Sheet1").Activate ' the excel worksheet is now active for reading
i = 1
c = 2
Set cn = New Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "E:\nikhil\excel2access\output.mdb;Persist Security Info=False"

cn.Open

cmd.ActiveConnection = cn

Hey,

You can use the VisibleRows property, but that goes all the way to the end of the sheet. I believe I just checked for the presense of values (where cell still has text). I haven't found a way using the API's where you can stop at the end of the data.

Another approach would be to load the excel sheet as a data source (using the DataAdapter) and access it that way.

Brian

Hi,
first you need to select first cell and then goto last cell
as given below

objExcel.application.Range("A1").Select
objExcel.application.Selection.End(xlDown).Select

so now you are in the last row

objExcel.application.Selection.End(xlToRight).Sele ct

it takes you to last column

then you can find out which row and which column you are in


Tip: If you want to know anything which is possible in EXCEL using VB
Just Record a macro with all the requirements.then study the macro code....thats it...:D

with thanks
Aditya

Hi All
Hey Aditya..thats a cool idea.. Here is another way for solving it

'VB.NET code
'To select the used range in WorkSheet
        Dim xlWsheet As Excel.Worksheet = xlApp.Worksheets("Sheet1")
        Dim r As Excel.Range = xlWsheet.UsedRange

' To get the first Value
        MsgBox(r.Cells(1, 1).Value)

' TO get the last value
        MsgBox(r.cells(r.rows.count()-1,r.columns.count()-1).value)


Thanks
PM