CopyFromRecordset

[beckyr51]

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). 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

 

[PHV]

i get an error
Which error message ?
Which line of code is highlighted when in debug mode ?
If late binding is used, I'd add the following:
Const xlDown = -4121

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[boblarson]

Try changing it to this:

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


Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[PHV]

Bob, CopyFromRecordset is a method of the Range object, not of the Worksheet object.

 

[beckyr51]

Its says application-defined or object-defined error and highlights
With objWkb.ActiveSheet.Range("a1").End(xlDown).Offset(1, 0)

Im afraid i dont know what you mean by late binding?

 

[beckyr51]

I tried including Const xlDown = -4121 and it had no effect?

 

[SkipVought]

Are you trying to open a specific workbook with GetObject?

If this is a NEW workbook, use CreateObject.

But the puzzle is that your code (using the End Method) seems to assume that something is ALREADY on the activesheet. Maybe it's the workbook that you currently have open.

1) what if someone saved the workbook with an UNINTENDED sheet active, or you have the wron sheet active. You should expicitly reference the sheet you want to put this data on.

2) If, for instance, nothing is on this sheet in column A2 - A65536, then this expression objWkb.ActiveSheet.Range("a1").End(xlDown).Offset(1, 0) is one cell past the last row of the worksheet. TILT!

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[beckyr51]

The woorkbook was created in a previous function, it is definitely open, on the right sheet and has data in it