CopyFromRecordset

[Ernst Guckel]

Hello,

I have a named range 'DayRange' that I am trying to use the
copyfromrecordset method to move data to a worksheet. The problem is that
the data contains 35 fields and the named range contains 35 cells but the
named range's cells are not continuous. Apparently the copyfromrecordset
method copys the data starting at the first cell in the range and from there
on. It this the case? Is there anyway around this?

Any Ideas would be great,

Ernst.

[Wild Bill]

Can't you just copy to a scratch area, and pluck to your true
destination a la B1=Sheet1!A2 ? Alternatively: Is your recordsource an
Access table? Your recordset could be a query instead of a table, which
returns records with fields

UsefulField1
"Filler"
"Filler"
UsefulField2
UsefulField3
etc.

which orients correctly to your destination.

It's difficult to answer without knowing what your recordset is.

[Ernst Guckel]

> Can't you just copy to a scratch area, and pluck to your true
> destination a la B1=Sheet1!A2 ? Alternatively: Is your recordsource an
> Access table? Your recordset could be a query instead of a table, which
> returns records with fields
>
> UsefulField1
> "Filler"
> "Filler"
> UsefulField2
> UsefulField3
> etc.
>
> which orients correctly to your destination.
>
> It's difficult to answer without knowing what your recordset is.

Recordset is an access table... I have a monthly ledger with data from every
day... The data is stored in an access table but the worksheet has columns
umungst the data...

Ernst.

[michelxld]

Hello Ernst , Hello Wild Bill

this example import records from database to discontinuous named range in the Worksheet
the number on cells in the discontious named range ("DayRange") is supposed the same that the number of recordCount in the table ("maTable")


I hope this help you


Sub DataBaseImport_InDiscontinuous_NamedRange()
Dim cn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim Fichier As String, TableName As String
Dim Cell As Range
Dim i As Byte

Fichier = ThisWorkbook.Path & "\MaBase_V01.mdb"
TableName = "maTable"

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Fichier & ";"
Set Rs = New ADODB.Recordset

With Rs
.ActiveConnection = cn
.Open "SELECT * FROM " & TableName, , adOpenStatic, adLockOptimistic, adCmdText
End With

'the number on cells in the discontious named range ("DayRange") is supposed the same
'that the number of recordCount in the table ("maTable")
For Each Cell In Range("DayRange")
Cell = Rs.Fields(0).Value
Rs.MoveNext
Next

Rs.Close
Set Rs = Nothing
cn.Close
Set cn = Nothing
End Sub




Regards ,
michel

[Tom Ogilvy]

Copy from recordset only copys to a contiguous area.

--
Regards,
Tom Ogilvy


"Ernst Guckel" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have a named range 'DayRange' that I am trying to use the
> copyfromrecordset method to move data to a worksheet. The problem is that
> the data contains 35 fields and the named range contains 35 cells but the
> named range's cells are not continuous. Apparently the copyfromrecordset
> method copys the data starting at the first cell in the range and from
there
> on. It this the case? Is there anyway around this?
>
> Any Ideas would be great,
>
> Ernst.