Sub ADO()
Dim lsSQL As String, Cnn As Object, Fso As Object, Fi As Object, lrs As Object
Set Fso = CreateObject("Scripting.FileSystemObject")
Set Cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With Fso
For Each Fi In .GetFolder(ThisWorkbook.Path).Files
If Fi.Name <> ThisWorkbook.Name Then
MsgBox Fi.Path
With Cnn
If Val(Application.Version) < 12 Then
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Fi.Path & ";Extended Properties=""Excel 8.0;HDR=No"";"
Else
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fi.Path & ";Extended Properties=""Excel 12.0;HDR=No"";"
End If
.Open
End With
lsSQL = "Select * From [Sheet1$A2:Z65536]"
lrs.Open lsSQL, Cnn
Range("A65536").End(3).Offset(1, 0).CopyFromRecordset lrs
Cnn.Close
End If
Next
End With
Set lrs = Nothing
Set Cnn = Nothing
Set Fso = Nothing
End Sub
