Private Sub CommandButton2_Click()
Dim t, wbDest As Workbook, myPath$, myFile$, mySheetName$, n%, x$, s
t = Timer
Set wbDest = ThisWorkbook
'myPath = "D:\Lycée Stéphane Hessel\OneDrive - Lycée Stéphane Hessel\Karl EPS\Protocoles BGT BCP CAP 23\Import Fichiers Santorin\"
myPath = wbDest.Path & "\"
myFile = Dir(myPath & "*.csv")
Application.ScreenUpdating = False
Do While myFile <> ""
mySheetName = Left(myFile, Len(myFile) - 4)
n = FreeFile
Open myPath & myFile For Input As #n 'ouverture en lecture séquentielle
x = Input(LOF(n), #n) 'concaténation
s = Split(x, vbCrLf) 'vecteur ligne
Close #n
With wbDest
.Activate
On Error Resume Next
.Sheets(mySheetName).Activate
On Error GoTo 0
If ActiveSheet.Name <> mySheetName Then
.Sheets.Add After:=.Sheets(.Sheets.Count)
ActiveSheet.Name = mySheetName
End If
ActiveSheet.Cells.Delete
With ActiveSheet.Cells(1).Resize(UBound(s))
.Value = Application.Transpose(s)
.TextToColumns .Cells, xlDelimited, Semicolon:=True 'commande Convertir
End With
End With
myFile = Dir
Loop
wbDest.Sheets(1).Activate
MsgBox "Durée " & Format(Timer - t, "0.00 \sec")
End Sub