Thread: VBA Question
View Single Post
  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
isabelle isabelle is offline
external usenet poster
 
Posts: 587
Default 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