Cannot read selected row
 

I am using Excel 2000 with sp3.

I am attempting to run code from the current workbook and open a second
instance of Excel, add a new workbook, paste in data from a recordset from an
Access database.

All works well until I attempt to read the row for thencurrently selected
cell and store to a variable.

Here is my code:
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oSheet.Activate
'next line adds data from open recordset - works well
oSheet.Range("A1").CopyFromRecordset myrs
oSheet.Range("A1").Select
oSheet.Range("A1").End(xlDown).Select
'next line reads row from last active worksheet of workbook where code
'is running from, not from the new workbook
MyEnd = Selection.Row

Any help is appreciated.
--
HTH

Mr B

Cannot read selected row
 

Without any object reference Selection will refer to the workbook that is
running the code. But you don't need to select, anyway, before doing
anything in code: just do this:
MyRow = oSheet.Range("A1").End(xlDown).Row
--
- K Dales


"Mr B" wrote:

I am using Excel 2000 with sp3.

I am attempting to run code from the current workbook and open a second
instance of Excel, add a new workbook, paste in data from a recordset from an
Access database.

All works well until I attempt to read the row for thencurrently selected
cell and store to a variable.

Here is my code:
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oSheet.Activate
'next line adds data from open recordset - works well
oSheet.Range("A1").CopyFromRecordset myrs
oSheet.Range("A1").Select
oSheet.Range("A1").End(xlDown).Select
'next line reads row from last active worksheet of workbook where code
'is running from, not from the new workbook
MyEnd = Selection.Row

Any help is appreciated.
--
HTH

Mr B

Cannot read selected row
 

Thanks for the help. That worked great.
--
HTH

Mr B


"K Dales" wrote:

Without any object reference Selection will refer to the workbook that is
running the code. But you don't need to select, anyway, before doing
anything in code: just do this:
MyRow = oSheet.Range("A1").End(xlDown).Row
--
- K Dales


"Mr B" wrote:

I am using Excel 2000 with sp3.

I am attempting to run code from the current workbook and open a second
instance of Excel, add a new workbook, paste in data from a recordset from an
Access database.

All works well until I attempt to read the row for thencurrently selected
cell and store to a variable.

Here is my code:
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oSheet.Activate
'next line adds data from open recordset - works well
oSheet.Range("A1").CopyFromRecordset myrs
oSheet.Range("A1").Select
oSheet.Range("A1").End(xlDown).Select
'next line reads row from last active worksheet of workbook where code
'is running from, not from the new workbook
MyEnd = Selection.Row

Any help is appreciated.
--
HTH

Mr B