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

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

Jean-Yves Tfelt
Europe


"Jan Kronsell" wrote:

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

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)

--

Regards,
Nigel




"Jan Kronsell" wrote in message
...
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

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" skrev i en meddelelse
...
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)

--

Regards,
Nigel




"Jan Kronsell" wrote in message
...
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

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




"Jan Kronsell" wrote in message
...
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" skrev i en meddelelse
...
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)

--

Regards,
Nigel




"Jan Kronsell" wrote in message
...
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

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




"Nigel" wrote in message
...

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




"Jan Kronsell" wrote in message
...
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" skrev i en meddelelse
...
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)

--

Regards,
Nigel




"Jan Kronsell" wrote in message
...
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

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


"Nigel" skrev i en meddelelse
...

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




"Jan Kronsell" wrote in message
...
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" skrev i en meddelelse
...
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)

--

Regards,
Nigel




"Jan Kronsell" wrote in message
...
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

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 wrote:

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

--

Dave Peterson

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


"Dave Peterson" skrev i en meddelelse
...
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 wrote:

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

--

Dave Peterson