VBA Question
hi carl,
i suppose column "A" contains data like "J:\Projects\ORF\Meeds\xlsx\005\ORF.xlsx"
you have to add reference Microsoft ActiveX Data Objects 2.8
if "Microsoft.ACE.OLEDB.10.0" is not installed on your PC, just tell us,
there are many others possibilities.
Sub test()
For i = 1 To 223
ReadFile Range("A" & i), "ORF_Charge", "A5:L5" 'adapt range
Next
End Sub
Function ReadFile(Fichier As String, Sh As String, Rgn As String)
Dim Source As ADODB.Connection
Dim Donnees As Variant
Dim Rst As ADODB.Recordset
Set Source = New ADODB.Connection
With Source
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 10.0;HDR=YES;"""
.Open
End With
Donnees = "SELECT * FROM [" & Sh & "$" & Rgn & "]"
Set Rst = New ADODB.Recordset
Set Rst = Source.Execute(Donnees)
Sheets(2).Range("A" & i).CopyFromRecordset Rst 'adapt sheet name or index
Source.Close
Set Source = Nothing
End Function
--
isabelle
|