kthorson16
New member
- Joined
- Sep 29, 2015
- Messages
- 12
- Reaction score
- 0
- Points
- 0
I am trying to modify the below code to loop thru a list of path location where databases are located: rDirList = Sheets("DRG").Range("I6:I99") and the paste the header table results on the row below. Anyone have any suggestions on how to get this to work?
Code:
Sub OpenTable()
Dim cn As Object
Dim rs As Object
Dim i As Integer
Dim Conn As String
Dim strTable As String
Dim Coll As New Collection
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
strTable = "[COLOR=#333333][FONT=Verdana]Rx[/FONT][/COLOR]"
Conn = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source=" & _
"H:\VBADatabase\Test_PR_YTD_thru_JAN_2016.mdb;"
cn.Open Conn
rs.Open Source:=strTable, ActiveConnection:=cn, CursorType:=1, LockType:=3
For i = 0 To rs.Fields.Count - 1
'get field / column name:
Coll.Add rs.Fields(i).Name
Next i
'send names to row 1 of active worksheet
For i = 1 To Coll.Count
Cells(1, i) = Coll(i)
Last edited by a moderator:
