Spreadsheet help needed

[angiem]

Hi

I'm running excel from access and I can run the spreadsheet once and it works fine the next time I run it
I get runtime error 1004 method sheets of object _application failed and it debugs to this line of code

Set ws = xl.Sheets(1)
in this function


Private Function FindLastRow() As Single
Dim c As Object
Dim strAddress As String
Dim LastRow As Long

'This function returns the last row of the spreadsheet

'set the xl object
Set xl = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Exit Function
End If

'set the workbook
Set wb = xl.ActiveWorkbook
'set the worksheet
'Set ws = xl.ActiveSheet

Set ws = xl.Sheets(1)
'select the cell(A1)
xl.Range("A1").Select
'get the last row
Set c = xl.cells.Find(What:="*", SearchDirection:=xlPrevious, searchOrder:=xlByRows)
If Not c Is Nothing Then
strAddress = c.Address
Do
FindLastRow = c.Row
Loop While Not c Is Nothing And c.Address <> strAddress
End If


End Function

I'd appreciate any help

Thanks


Angie

http://www.millardconsulting.ca

 

[macropod]

Hi Angie,

I think your code was crashing because 'Set ws = xl.Sheets(1)' should have been 'Set ws = wb.Sheets(1)'
I suspect that fixing that would then have your code crashing at 'xl.Range("A1").Select', which would need to have been 'ws.Range("A1").Select'.

Try this instead:

Private Function FindLastRow() As Long
'This function returns the last row of the spreadsheet

'set the xl object
  Set xl = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
       Exit Function
    End If

'set the workbook
Set wb = xl.ActiveWorkbook

'set the worksheet
Set ws = wb.ActiveSheet ' or Set ws = wb.Sheets(1)

'get the last row
FindLastRow = ws.UsedRange.Rows.Count

End Function

Note that there's no need to select the worksheet or any cells in it.

Cheers

[MS MVP - Word]

 

[Loomah]

Angie
Just as a side issue - not really to do with your problem - using UsedRange is a decidedly unreliable way to find the last row in the way you're attempting to do so.

OK it will work if your data is completely clean and begins in row 1 but otherwise you could run into problems.

Check out the FAQ section of this forum where there are (at least) 2 FAQs on finding the "real" last row.

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[angiem]

I changed the code and when it runs for the second time it now shows runtime error 91 object variable with block variable not set and debugs on the same line of code

Angie

http://www.millardconsulting.ca

 

[angiem]

It seems to be that the when I set the workbook the first time round it is ok but the second time it doesn't set it.

 

[PHV]

Just before the End Function, you may try this:
Set ws = Nothing
Set wb = Nothing
Set xl = nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[angiem]

I've tried that and still get the same error

 

[angiem]

I have fixed this now by putting End in the function before it closes excel and it works now. Thanks for the help