kthorson16
New Member
- Joined
- Sep 3, 2015
- Messages
- 27
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
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