Option Explicit
Sub MonLecteurDeFichierFermes()
Dim FileToRead As Variant
Dim SheetToWrite As Worksheet
Dim SheetToRead As String
Dim FileName As String, PathString As String
Dim y As Integer, X As Integer
Dim Cell As Variant
Set SheetToWrite = ThisWorkbook.Worksheets('Facture')
SheetToRead = InputBox('Indiquer la Feuille à Lire' & vbCrLf & _
' (sinon rien et vous pourrez choiser)', 'Sheet Address', 'Devis')
FileToRead = Application.GetOpenFilename('Classeurs Excel,*.xls')
If FileToRead = False Then Exit Sub
y = Len(FileToRead)
For X = y To 1 Step -1
If Mid(FileToRead, X, 1) <> Chr(92) Then
FileName = Mid(FileToRead, X, 1) & FileName
Else
Exit For
End If
Next X
PathString = Left(FileToRead, Len(FileToRead) - Len(FileName))
For Each Cell In Array('E12', 'H12', 'E13', 'E14', 'G14', 'E15', 'L4', 'L13', _
'D18', 'E18', 'F18', 'G18', 'H18', 'I18', 'J18', 'K18', 'L18', _
'D19', 'E19', 'F19', 'G19', 'H19', 'I19', 'J19', 'K19', 'L19', _
'D20', 'E20', 'F20', 'G20', 'H20', 'I20', 'J20', 'K20', 'L20')
On Error GoTo Out
SheetToWrite.Range(Cell) = '='' & PathString & '[' & FileName & ']' & SheetToRead & ''!' & Cell
Next Cell
Exit Sub
Out:
MsgBox 'Opération Annulée'
End Sub
