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

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

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