VBA find empty row in closed workbook

Hi All

Hoping someone can help me out with this. I've managed to find a nice bit of code that allows me to open a closed workbook in order to write data into it from a opened workbook. But I keep getting an Object Required error when i try to run the code.

What I'm trying to do, is have the code go into the closed workbook, find the first empty row on the sheet, then write the data into it form the sheet 'mydata' which is in the opened workbook, (hope that makes sense?):

Dim xl0 As New Excel.Application
    Dim xlw As New Excel.Workbook
    Set xlw = xl0.Workbooks.Open("I:\myfilepath\myfile.xlsm")


 
emptyRow = WorksheetFunction.CountA(x10.Sheets("Data").Range("A:A")) + 1



x10.Sheets("Data").Cells(emptyRow, 1).Value = Sheets("mydata").Range("A2").Value
x10.Sheets("Data").Cells(emptyRow, 2).Value = Sheets("mydata").Range("B2").Value
 


 
    xlw.Save
    xlw.Close


    
    Set xl0 = Nothing

Feel like i'm almost there, just can't figure out the error!

any help would be greatly appreciated!

Many thanks in advance for your help

Perhaps.

Dim xl0 As New Excel.Application
Dim xlw As New Excel.Workbook

    Set xlw = xl0.Workbooks.Open("I:\myfilepath\myfile.xlsm")
    
    emptyRow = WorksheetFunction.CountA(xlw.Sheets("Data").Range("A:A")) + 1

    xlw.Sheets("Data").Cells(emptyRow, 1).Value = Sheets("mydata").Range("A2").Value
    xlw.Sheets("Data").Cells(emptyRow, 2).Value = Sheets("mydata").Range("B2").Value
 
    xlw.Save
    xlw.Close
    
    Set xl0 = Nothing

What's in column A of sheet 'Data' in the workbook you are opening?

I'm confused, why do you want to find the last row in the closed workbook if there's no data in it?