Yes I made a mistake in strConnection. Try this (works for me):
CODE:
-----------------------------------------------------------------------
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> ConsolidateText()<br><br><SPAN style="color:#007F00">'< COMMENTS ></SPAN><br> <SPAN style="color:#007F00">'// references Micrsoft ActiveX Data Objects 2.8 Library //</SPAN><br> <SPAN style="color:#007F00">'// assumes all files are contained in a single directory folder //</SPAN><br> <SPAN style="color:#007F00">'// assumes that the folder exclusively holds the files for consolidation //</SPAN><br> <SPAN style="color:#007F00">'// (1) change to point to your directory folder //</SPAN><br> <SPAN style="color:#007F00">'// (2) data will paste to the active sheet starting from column A, change to suit //</SPAN><br> <SPAN style="color:#007F00">'// (3) assumed that the sheet in each file is called 'Sheet1', change to suit //</SPAN><br><SPAN style="color:#007F00">'< /COMMENTS ></SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> recData <SPAN style="color:#00007F">As</SPAN> ADODB.Recordset<br><SPAN style="color:#00007F">Dim</SPAN> strConnection <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strSQL <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strFileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> rngCell <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> recData = <SPAN style="color:#00007F">New</SPAN> ADODB.Recordset<br><br>strPath = "C:\Excel\Files\Text" <SPAN style="color:#007F00">' ### (1)</SPAN><br>strFileName = Dir(strPath & "\*.xls*")<br><br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> Len(strFileName) > 0<br> <SPAN style="color:#00007F">Set</SPAN> rngCell = Range("A" & Rows.Count).End(xlUp).Offset(1) <SPAN style="color:#007F00">' ### (2)</SPAN><br> strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _<br> "Data Source =" & strPath & "\" & strFileName & "; Extended Properties=Excel 8.0"<br> strSQL = "SELECT * FROM [Sheet1$]" <SPAN style="color:#007F00">' ### (3)</SPAN><br> <SPAN style="color:#00007F">Call</SPAN> recData.Open(strSQL, strConnection, adOpenForwardOnly, adLockReadOnly, adCmdText)<br> <SPAN style="color:#00007F">Call</SPAN> rngCell.CopyFromRecordset(recData)<br> recData.Close<br> strFileName = Dir<br><SPAN style="color:#00007F">Loop</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> recData = <SPAN style="color:#00007F">Nothing</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
