Getting the LastRow in ExcelSheet from Word

[Jan Kronsell]

I'm trying to find the last used row in column C in a sheet, using
automation from Word.

I use the following code:


Dim rk As Integer
On Error Resume Next
Set xlapp = GetObject(, "Excel.application")
If Err.Number <> 0 Then
Set xlapp = CreateObject("excel.Application")
End If
xlapp.Workbooks.Open FileName:= _
"C:\Documents and Settings\Administrator\Dokumenter\Deltagere.xls"
xlapp.Visible = True
rk = xlapp.activeworkbook.sheets(1).Range("c65536").End(xlUp).Row
It opens the workbook and selects the last used cell in column C, but rk
return a 0 in stead of thec expected 27 in this case. How can I Find the row
number of last used cell in column C?

Jan

 

[Jean-Yves]

Works fine with me, but it should retrun at least 1 if cell has data.
Is it really sheet 1 you want to check ?

 

[Nigel]

It works for me, check your references to a worksheet. You might also like
to Dim rk as type long rather than integer (limiting it to 32k rows)

 

[Jan Kronsell]

Strange! If I run these lines from Word:

rk = xlapp.ActiveWorkbook.Sheets(1).Range("c65536").End(xlUp).Row
MsgBox rk

it opens the correct workbook, finds the correct sheet, selects the last
cell in row c, containing data, but MsGBOX rk displays a 0. Debug.print
also returns a 0.

If I run these lines, directly from Excel

rk = ActiveWorkbook.Sheets(1).Range("c65536").End(xlUp).Row
MsgBox rk

It do not select the last cell in row, but stays in C65536m but 27 is
displayed in the MSGBOX as supposed to. So somehow it got to have something
to do with running the code from Word.

Jan

 

[Nigel]

I tried running the following from Word and Excel (2007), same code with
different workbook reference, the MsgBox shows in this case 10 as expected.

Sub test()
Dim rk As Integer
On Error Resume Next
Set xlapp = GetObject(, "Excel.application")
If Err.Number <> 0 Then
Set xlapp = CreateObject("excel.Application")
End If
xlapp.Workbooks.Open FileName:="D:\Book1.xls"
xlapp.Visible = True

rk = xlapp.ActiveWorkbook.Sheets(1).Range("c65536").End(xlUp).Row

MsgBox rk

End Sub
--

Regards,
Nigel
(e-mail address removed)

 

[Nigel]

One thought, is the scope of rk catching you out? rk is only valid in this
procedure, you could make it public or pass it back from a function?

--

Regards,
Nigel
(e-mail address removed)

 

[Dave Peterson]

I'd turn error checking back on before you open the workbook.

Maybe you're masking the error with that "on error resume next" line.

 

[Jan Kronsell]

I tried exactly the same, wih the same workbook as you, 10 rows of data, and
stills get 0. So maybe it's a setting thatas not correct. I don't know where
to look though. I'm running Word and Excel 2003 though.

Jan

 

[Jan Kronsell]

You got it! Turning error handling back on showed that I was indeed masking
an error. I t was a reference, that was not set properly.

Thank you to Dave and to the rest of you, that have being very helpful.

Jan