#If VBA7 Then
Private Declare PtrSafe Function SetTimer Lib "user32.dll" (ByVal hwnd As LongPtr, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As Long
Private Declare PtrSafe Function KillTimer Lib "user32.dll" (ByVal hwnd As LongPtr, ByVal nIDEvent As Long) As Long
#Else
Private Declare Function SetTimer Lib "user32.dll" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As Long
Private Declare Function KillTimer Lib "user32.dll" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
#End If
Dim TimerID&
Sub LitClasseurFermé()
Dim Rsource As Range, Rdest As Range, Chemin$, Fichier$, Onglet$
Chemin = "\\PC1\Users\Public" 'ThisWorkbook.Path 'chemin du fichier source
Fichier = 'nom du fichier source'Adapte le nom de ton fichier !!!!
Onglet = "Feuil1" 'feuille du fichier source
Set Rsource = [A1:A10] ' plage du fichier source
Set Rdest = Feuil1.[A1].Resize(Rsource.Rows.Count, Rsource.Columns.Count) 'destination
LitChamp Rdest, Chemin, Fichier, Onglet, Rsource 'lance l'execution
End Sub
Sub LitChamp(Rdest As Range, Chemin, Fichier, Onglet, Rsource As Range)
TimerID = SetTimer(0, 0, 100, AddressOf clickOFF)
Rdest.FormulaArray = "='" & Chemin & "\[" & Fichier & "]" & Onglet & "'!" & CStr(Rsource.Address(0, 0)) 'formule matricielle de liaison
Rdest = Rdest.Value 'supression des formulesremplacement des formules par les valeurs
End Sub
Sub clickOFF()
CreateObject("wscript.shell").SendKeys "{ENTER}"
If TimerID <> 0 Then KillTimer 0, TimerID: TimerID = 0:
End Sub