Help with excel macro from Access

[allenjasonbrown@gmail.com]

I am trying to delete rows in an excel workbook that is currently being written to via my Access VBA (it is open using the following code).

   strPath = "C:\test\test.xlsb"
   Set ApXL = CreateObject("Excel.Application")
   'Debug.Print strPath
   Set xlWBk = ApXL.Workbooks.Open(strPath)
   ApXL.Visible = False

I have the following code which currently runs in the workbook itself but I would like help converting it to run in the Access VBA code while I write all the data to it. Does anyone know now I can convert the following code to run in access?

Thank you in advance.

Dim iRowCountA As Integer, iRowCountB As Integer
    
    iRowCountA = Range(Sheets("sheet1").Range("A2"), Sheets("sheet1").Range("A2").End(xlDown)).Rows.Count
    iRowCountB = Range(Sheets("sheet2").Range("B10"), Sheets("sheet2").Range("B10").End(xlDown)).Rows.Count

    Sheets("sheet2").Range("A" & iRowCountA + 10 & ":A" & iRowCountB + 10 - 1).EntireRow.Delete

[June7]

The code needs to reference the Excel object opened by VBA. Something like:

iRowCountA = xlWBk.Sheets("sheet1").Range("A2"), xlWBk.Sheets("sheet1").Range("A2").End(xlDown)).Ro ws.Count
iRowCountB = xlWBk.Sheets("sheet2").Range("B10"), xlWBk.Sheets("sheet2").Range("B10").End(xlDown)).R ows.Count

xlWBk.Sheets("sheet2").Range("A" & iRowCountA + 10 & ":A" & iRowCountB + 10 - 1).EntireRow.Delete

[allenjasonbrown@gmail.com]

Thanks for the reply. I am getting a compile error "syntax error". Any idea?

[June7]

The few times I have written code in Access VBA to manipulate an Excel object were challenging. Getting the referencing of sheets, ranges, cells right usually takes me a lot of trial and error. Might even have to create a sheet object. Maybe instead of Sheets, try Worksheets.

Unfortunately, I don't have functional code now. If I have time tomorrow will try some.

[June7]

I needed to put the Range reference back. This works:

iRowCountA = Range(xlWBk.Sheets("sheet1").Range("A2"), xlWBk.Sheets("sheet1").Range("A2").End(xlDown)).Ro ws.Count
iRowCountB = Range(xlWBk.Sheets("sheet2").Range("B10"), xlWBk.Sheets("sheet2").Range("B10").End(xlDown)).R ows.Count
xlWBk.Sheets("sheet2").Range("A" & iRowCountA + 10 & ":A" & iRowCountB + 10 - 1).EntireRow.Delete

Only problem - sometimes leaves an EXCEL.EXE process running in task manager. Even when I manually close the Excel application. Even after deleting the proceses, sometimes get error 462, 'The remote server machine does not exist or is unavailable'. I have encountered this before, sometimes I can resolve, sometimes I give up. Right now I give up and you are on your own.