Select method od range class failed (EXCEL)

[nickjar2]

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlexcel As Excel.Worksheet
Dim sRef As String
Dim c As Variant
Dim n As Integer

On Error GoTo excelProb
' Open new instance of Excel
Set xlApp = New Excel.Application
' Open workbook
Set xlBook = xlApp.Workbooks.Open("v:\wam\bo upgrade\invoice\sept 2001\sw test.xls&quot
' Reference the first sheet
Set xlexcel = xlBook.Worksheets(2)
' Get first value from cell 10 (first column)
sRef = xlexcel.Cells(10, 1).Value

n = 10

Do While sRef <> &quot;&quot;
n = n + 1
sRef = xlexcel.Cells(n, 1).Value

Loop

n = n - 1

' EXCEL DOESN'T LIKE THE NEXT LINE
xlexcel.Range(&quot;A10&quot; & n).Select
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

Cheers,

Nick

 

[ETID]

n must be changed to a string and leading spaces removed...

replace the n with this

trim(str$)

 

[nickjar2]

ETID,

It doesn't work even if i use:
xlexcel.Range(&quot;a1:a2&quot.Select

It still fails.

Cheers,

Nick

 

[dsi]

There should not be a problem if n is declared as an Integer or a Long. I think your problem lies in the fact that you are selecting cells that are not on the active sheet. If you select the worksheet first, the code should work (at least the selection part).

xlexcel.Select
xlexcel.Range(&quot;A10&quot; & n).Select

 

[nickjar2]

dsi,

I have done this but now get an:
Application-defined or object-defined error

This easy enough to do if the xls is the open one, but for some reason using COM it doesn't like it. Hopefully we eill get there. Any other suggestions?

Cheers,

Nick

 

[dsi]

Try using xlexcel.Activate. I'm not too familiar with COM, so this is about all I have in my bag.

 

[nickjar2]

It didn't do anything. I have just got it to work. Maybe someone can explain why. I have replaced:
Set xlexcel = xlBook.Worksheets(2)

with
Set xlexcel = xlBook.Sheets(&quot;evidence 1&quot

Is there a difference???

Cheers,

Nick

 

[ETID]

work sheet indexes are in order of creation, and not necessarily in order as appearing in workbook left to right.