1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| Sub test2()
'lecture fichier fermé :
Dim chem1$, chem2$, chem3$, fichier As String, wbs As Workbook, ws1 As Worksheet, tb1 As ListObject
Set wbs = Workbooks("fichier ferme")
Set ws1 = wbs.Sheets("Clients")
Set tb1 = ws1.ListObjects(1)
Application.ScreenUpdating = False
Application.EnableEvents = False
chem1 = "E:\Users\....\entre FICHIERS\tests\"
fichier = "fichier ferme.xlsm" 'à adapter
chem2 = Dir(chem1 & "fichier ferme.xlsm", vbNormal)
chem3 = "='" & chem1 & "[" & fichier & "] & page1'" & ws1.ListObjects(1).DataBodyRange ' chemin cellule ok : page1'!A1" 'fichier source
If Dir(chem1) <> "" Then 'dir sur le chemin
With Worksheets("test").Range("B2") 'fichier destination
.Value = chem3
.Value = .Value
End With
Else
MsgBox "pas de fichier"
End If
Application.EnableEvents = True
Application.ScreenUpdating = True |
Partager