From help on copyfromrecordset
Copies the contents of a DAO [ADO as well in xl 2000 and later] Recordset
object onto a worksheet, beginning at the upper-left corner of the specified
range
In your example R is the specified range. R.offset(0,1) would be the cell
to the right and R.offset(0,2) would be two cells to the right.
You previously stated that R.CopyFromRecordset worked - so using offset
should work as well.
Regards,
Tom Ogilvy
Quote:
> > Hello and Help!!!
> > I have an excel form that has a range from B14 thru B27 that we use to
> enter
> > a part number and based on this part number I would like to retrieve the
> > description for it and place it 2 cells to the right. They way this is
> done
> > is when a part number has been entered into any part of the range I
would
> > select that part number entered and then click a command button that
runs
> > the code below that retrieved the description from my Iseries. The only
> > problem is that it places it 1 cell to the right and I would like to
place
> > it 2 cells to the right and have not been successful.
> > I am using SEQUEL as a provider since in the sequel program I have
created
> a
> > view has this information and sequel pulls it from the Iseries. I don't
> > know if this is the problem!!
> > The information in my Iseries is in the following path:
> > QS36F/PARTDESC..........and in the PARTDESC....I have
> > PARTNO.....that holds the part number
> > FL002......that holds the description for that part number
> > Public Sub GetPartNumbers()
> > Dim myConn As ADODB.Connection
> > Dim myRS As ADODB.Recordset
> > Set myConn = New ADODB.Connection
> > myConn.ConnectionString = "Provider=SEQUEL ViewPoint;"
> > myConn.Open
> > Set myRS = New ADODB.Recordset
> > Dim R As Range
> > Set R = Selection(1) 'cell as range that was clicked on
> > 'remove or change to whatever:
> > If Intersect(R, Range("B14:B24")) Is Nothing Then
> > MsgBox "You are not in Range(""B14:B24"")"
> > Exit Sub
> > End If
> > selVal = Val(R.Value) 'returnes the number in cell associated
> > If selVal = 0 Then
> > MsgBox "Select a Part Number then click on Get Part Number cmd
button"
> > Exit Sub
> > End If
> > myRS.Open "USACCT/DDVDPARTNO /setvar((&PARTNO " & selVal & "))", myConn
> > 'This brings in the results but it places it in the cell to the
> > 'right and I need the results 2 cells to the right
> > If myRS.RecordCount > 0 Then
> > R.CopyFromRecordset MyRS
> > 'These are all the things I have tried and they don't
> > 'seem to work
> > ' Copy myRS(1, 2)
> > ' Copy myRS.Offset(0, 1)
> > ' R.Offset(, 2).CopyFromRecordset myRS
> > ' G.CopyFromRecordset myRS
> > ' R.CopyFromRecordset myRS
> > ' Set R = R.Offset(0, 2)
> > ' R.CopyFromRecordset myRS
> > ' R.Offset(0, 2) = myRS
> > 'The else works fine...
> > Else
> > R.Offset(0, 2).Value = "No Records Returned"
> > End If
> > End Sub
> > Thanks
> > Jaime
Offset