Cannot Paste Data into Excel 1

[ooch1]

Hello,

I'm trying to view the results of my code within an excel file, however it keeps getting stuck at the copyrecodset line, saying that the 'object does not recognise this property'.

Please can someone help with a way i can copy and paste the recordset???

My code is as follows:

Sub OpenCOOQry()
Dim rs As ADODB.Recordset
Dim strSELECT As String
Dim DBRecords As String
Dim objXL As Object, objWorkbook As Object, xlCSV As Object
Dim i As Integer

'create recordset objest
Set rs = New ADODB.Recordset

strSELECT = "SELECT *" & _
    "FROM Gas_Hull_COT_PortAnlys " & _
    "ORDER BY Gas_Hull_COT_PortAnlys.SiteRefNum, Gas_Hull_COT_PortAnlys.MeterPointRef;"

With rs
    .Open strSELECT, CurrentProject.Connection
End With
 

Set objXL = CreateObject("Excel.Application") ' sets the object as excel
    objXL.Application.Visible = True 'allows you to see the excel file
' Get and openthe dbrecords
DBRecords = "C:\WINNT\Profiles\archn01\Desktop\DB Records.xls"
Set objWorkbook = objXL.Application.Workbooks.Open(Filename:=DBRecords)
objXL.Application.displayalerts = False 'Turn off the alerts

With objWorkbook
    .Worksheets("Sheet1").Activate
[b][COLOR=red]
    .offset(1).copyfromrecordset rs[/color][/b]
    .ActiveSheet.Cells("a1").PasteSpecial

    
     For i = 0 To rs.Fields.Count - 1
        .offset(0, i) = rs.Fields(i).Name
        
        
      Next i
     
End With

End Sub

All i want to do is paste the data at this stage, so i'm sure that it is possible.

OOch

 

[PHV]

Replace this:
.offset(1).copyfromrecordset rs
with something like this:
.Range("A2").copyfromrecordset rs

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

PHV - Thanks for your response, but i'm afraid it did not work, i got the same error message as before??

OOch

 

[combo]

The Offset property in your code refers to Workbook object, should be Worksheet instead.
Try:

objWorkbook.Worksheets("Sheet1").Range("A2")
    .copyfromrecordset rs
    For i = 0 To rs.Fields.Count - 1
        .offset(-1, i) = rs.Fields(i).Name
    Next i
End With

combo

 

[ooch1]

Many thanks combo, it worked perfectly!

OOch