VBA to paste next set of data under previous pasted data

[kthorson16]

Below is the following code I am using to copy the headers from an access 2010 table and paste them on a specific tab and row of an excel 2010 spreadsheet. Currently when it goes to the next access 2010 table headers to paste, it is pasting them at the end of the first set of headers. I am trying to get it to paste in the row under the previous pasted data. What am I doing wrong?

Sub OpenTable()


Dim rs As Object
Dim X As Integer
Dim Conn As String
Dim strTable As String
Dim rngDbPath As Range
Dim cn As Object
Dim i As Integer
Dim Coll As New Collection
Dim rDirList As Range

Set rs = CreateObject("ADODB.Recordset")

For Each rngDbPath In Sheets("DRG").Range("I6:I99")

strTable = "Rx"
Conn = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source=" & rngDbPath.Value

rs.Open Source:=strTable, ActiveConnection:=Conn, CursorType:=1, LockType:=3

X = X + 1
For i = 0 To rs.Fields.Count - 1
Coll.Add rs.Fields(i).Name
Next i


For i = 1 To Coll.Count
Sheet = "COSMOS New Deals Database"
Cells(25, i) = Coll(i)
Next i

rs.Close

Next

End Sub

 

[pbornemeier]

Would changing

Cells(25, i) = Coll(i)

to

Cells(X, i) = Coll(i)

give you the results you want?