Automating Excel

[beckyr]

Hello!
I was wondering if someone could tell me where im going wrong. Im trying to copy a recordset into an open excel sheet starting where the next empty cell in columnA is (the excel sheet was created in a previous macro, is still open and not saved, and does contain data). My code is the following but i get an error:

Dim objXL As Excel.Application
Set objXL = GetObject(, "Excel.Application")
Dim objWkb As Object
Set objWkb = Excel.ActiveWorkbook

With objWkb.ActiveSheet.Range("a1").End(xlDown).Offset(1, 0)
.CopyFromRecordset rs
End With

The error is: application-defined or object-defined error. Any ideas please?

 

[DJkarl]

Hello!
I was wondering if someone could tell me where im going wrong. Im trying to copy a recordset into an open excel sheet starting where the next empty cell in columnA is (the excel sheet was created in a previous macro, is still open and not saved, and does contain data). My code is the following but i get an error:

Dim objXL As Excel.Application
Set objXL = GetObject(, "Excel.Application")
Dim objWkb As Object
Set objWkb = Excel.ActiveWorkbook
With objWkb.ActiveSheet.Range("a1").End(xlDown).Offset(1, 0)
.CopyFromRecordset rs
End With

The error is: application-defined or object-defined error. Any ideas please?

Well without saying what line you're getting the error at I'm just guessing, but I think it's the line I've highlighted in red. You don't define "Excel" as an object so the objWkb isn't getting set.

Try

Set objWkb = objXL.Application.ActiveWorkbook

 

[beckyr]

That's great - thank you very much!