Method 'Cells' of object'_Global' failed and I haven't a clue

[gavindave84]

Hi

I have been trying to automate some bureaucratic processes in my work. I have created a fair few macro's in my time but I am no master of vba. I have created a macro in word which is suppose to open an excel workbook, find the next available empty row and paste variable values into that row. The macro I have works first time but subsequent times is falls over with the error Method 'Cells' of object'_Global' failed. I have read a few posts and the answer seems to be to do with correctly referencing excel objects each time they are used. Or something like that. I am completely stumped and if anyone could point me in the right direction I would be eternally greatful.


Public Sub OpenExcelFile()

'open excel variables
Dim oExcel As Excel.Application
Dim oWB As Workbook
Dim oWS As Worksheet
Dim quality_database As String
Dim lastrow_available As Integer
Dim entryrow As Integer
Dim number As Integer
Dim submission_date As Date

Application.DisplayAlerts = False
Set oExcel = CreateObject("Excel.Application")
Set oWB = oExcel.Workbooks.Open("U:\G Davis\Internal checking_Quality metrics.xlsx")
Set oWS = oExcel.Worksheets("detail")
oExcel.Visible = True

submission_date = Now()

lastrow_available = oWS.Cells(Cells.Rows.Count, "A").End(xlUp).Row
entryrow = lastrow_available + 1
number = entryrow - 1


'Entry od data into database is done with the assumption that column positions do not change
'These variables are defined in an earlier subroutine
With oWS
.Range("A" & entryrow) = number
.Range("B" & entryrow) = project
.Range("C" & entryrow) = Report
.Range("D" & entryrow) = Author
.Range("E" & entryrow) = Checker
.Range("F" & entryrow) = checked_date
.Range("G" & entryrow) = submission_date
.Range("H" & entryrow) = w
.Range("I" & entryrow) = x
.Range("J" & entryrow) = y
.Range("K" & entryrow) = Z
.Range("L" & entryrow) = w + x + y + Z
End With
With oWB
.Save
.Close
End With
End Sub

The line in bold is where the error is happening. Any thoughts?

Thanks in advance

 

[Norie]

Try this.

lastrow_available = oWS.Cells(oWS.Cells.Rows.Count, "A").End(xlUp).Row

 

[gavindave84]

Try this.

lastrow_available = oWS.Cells(oWS.Cells.Rows.Count, "A").End(xlUp).Row

You solved that insultingly fast.lol. Much appreciated.