CopyFromRecordset

[acrawford]

Hello,

I hope I am in the right group.
I have the following code which causes the error: "Method 'Sheets' of
object '_Global' failed."

Can somebody help please?

Public Sub getrs()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String

sql = "Select * from tbl_Department"
filenm = "F:\Integrating Excel and Access\ops log.mdb"

Call GetCn(adoconn, adors, sql, filenm, "", "")

Dim xlsht As Excel.Worksheet
Set xlsht = Sheets("Sheet1")
xlsht.Range("A1").CopyFromRecordset adors

adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlsht = Nothing
End Sub

TIA
Antje Crawford

 

[Dirk Goldgar]

Hello,

I hope I am in the right group.
I have the following code which causes the error: "Method 'Sheets' of
object '_Global' failed."

Can somebody help please?

Public Sub getrs()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String

sql = "Select * from tbl_Department"
filenm = "F:\Integrating Excel and Access\ops log.mdb"

Call GetCn(adoconn, adors, sql, filenm, "", "")

Dim xlsht As Excel.Worksheet
Set xlsht = Sheets("Sheet1")
xlsht.Range("A1").CopyFromRecordset adors

adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlsht = Nothing
End Sub

TIA
Antje Crawford

Where are you running this code? If you run it in Access, you'll get
that error, because you aren't qualifying the reference to Sheets with
any Excel application object, and there's no Sheets property or method
that is native to Access.