My code replace the data by the line number

[keawee]

Hello, I have a problem in a recordset.

I recover of the data since Access and I insert my data in Excel. I
place my data in A11 for example and imagine that my data go from A11
to R51. With each time, in my column A with last the recordings it me
insert not data but the number of the line, in this case 51.

I have only this problem that in a cell and the first column of my last
recording. Could you help me?.

This is my code

Public Sub CopyFromRecordset()
Dim Db1 As Database
Dim Rs1 As Recordset, Nb As Long
Dim Sh As Worksheet, Rg As Range, Nl As Range
Dim Chemin As String, Fichier As String

Dim bordure As MsoLineStyle

bordure = msoLineSingle

Set Sh = Worksheets("Test")
With Sh
Set Rg = .Range("A10")
End With

Set Db1 = DBEngine.OpenDatabase(ThisWorkbook.Path
& "\GeneralReport.mdb")

Set Rs1 = Db1.OpenRecordset("REPORT", dbOpenDynaset)

Rg.CurrentRegion.Clear

If Rs1.EOF = False Then

Nb = Rs1.Fields.Count - 1

For a = 0 To Nb
Rg(, 1 + a) = Rs1.Fields(a).Name
Next

Rg.Resize(, Nb + 1).Font.Bold = True
Rg.Resize(, Nb + 1).Interior.ColorIndex = 15


Rg.Offset(1).CopyFromRecordset Rs1

Else
MsgBox("No Record")
End If

After I kill the object
....

Thanks for your help

Keawee

 

[Tom Ogilvy]

Copyfromrecordset puts the recordset on the worksheet. It is a single
command. I have never seen any post here that says it adds stray data at
the end. If this is all that is on the worksheet, try

Rg.Parent.UsedRange.Clear

or Rg.Parent.UsedRange.Offset(19).Clear

if you don't want to clear above row 10 (assumes Usedrange starts in A1,
but it may not. You need to check).