CopyFromRecordset

LB79 · Jan 7, 2011

Hi All,

I've been using the CopyFromRecordset in a very basic way for a while now, but now I need to be a bit more daring with it.
So far I've been transferring whole tables, but now I want to transfer individual fields of a record (EG in an Access table, I want field 1 of record 1 to be placed in cell A1 of my spreadsheet).
Can anyone point me at an instruction for this?

Thanks

Trevor G · Jan 7, 2011

Are you refering to the Field name or the contents of Field1 ? If so create a query for the field and do the copyFromRecordset from the query rather than the table

boblarson · Jan 7, 2011

If you are wanting specific data to go to specific locations then copyfromrecordset may not be the tool to use. Something like this instead might be what you need:

Code:

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset
Dim lngCount As Long
 
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
 
Set xlWB = objXL.Workbooks.Open("C:\Temp\MyBook.xls")
 
Set xlWS = xlWB.Worksheets("Sheet1")
 
Set rst = CurrentDb.OpenRecordset("QueryNameHere")
 
lngCount = 1
 
Do Until rst.EOF
 
    With xlWS
         .Cells(lngCount, 1).Value = rst!FieldNameHere
         .Cells(lngCount+5,2).Value = rst!Field2NameHere
         .Cells(lngCount, 4).Value = rst!Field3NameHere
    End With
    lngCount = lngCount + 1
    rst.MoveNext
Loop
 
rst.Close
Set rst = Nothing

Just an example but hopefully you get the picture.

CopyFromRecordset

LB79

Registered User.

Trevor G

Registered User.

boblarson

Smeghead

Similar threads

Users who are viewing this thread