Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
''Early Bindind
'Mettre en référence "Microsoft ActiveX Data Objects x.x Library" et " Microsoft ADO ext x.x for DLL and Security"
'(Outil, Références)
' Dim Cn As ADODB.Connection
' Dim oCat As ADOX.Catalog, Feuille As ADOX.Table
' Set Cn = New ADODB.Connection
' Set oCat = New ADOX.Catalog
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'Late Binding
Dim Cn As Object
Dim oCat As Object, Feuille As Object
Set Cn = CreateObject("ADODB.Connection")
Set oCat = CreateObject("ADOX.Catalog")
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Dim NomFeuilles$, MonFich
If Target.Address <> [NomFich].Address Then Exit Sub
ChDir ThisWorkbook.Path
MonFich = Application.GetOpenFilename("Fichiers Excel (*.xls;*.xlsx;*.xlsm), *.xls;*.xlsx;*.xlsm", , "Fichier Excel SOURCE")
If MonFich = False Then
'Effacer le nom de fichier et la liste des feuilles (validation de données de la cellule "NomFeuille")
[NomFich].ClearContents
[NomFeuille].ClearContents
[NomFich].Offset(0, -1).Select
With Sh_Accueil.[NomFeuille].Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=","
End With
Exit Sub
End If
'Ecrire le nom du fichier
[NomFich].Value = MonFich
[NomFeuille].Select
'Collecter le nom des feuilles
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& MonFich & ";Extended Properties=""Excel 12.0;HDR=YES;ReadOnly=True;"""
.Open
End With
Set oCat.ActiveConnection = Cn
'Liste des feuilles
For Each Feuille In oCat.Tables
TxtFeuil = Replace(Feuille.Name, Chr(39), "")
If Right(TxtFeuil, 1) = "$" Then NomFeuilles = NomFeuilles & Replace(TxtFeuil, "$", "") & ","
Next
'Validation de la cellule "NomFeuille" = liste des feuilles trouvées
With [NomFeuille].Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=NomFeuilles
End With
[NomFeuille].ClearContents
'Nb de lignes et de colonnes en fonction du type de fichier
If Right(MonFich, 4) = ".xls" Then
LgnMax = 65536
ColMax = 256
Else
LgnMax = 1048576
ColMax = 16384
Set Feuille = Nothing
End If
'Validation des cellules NbLignes et NbColonnes
With [NbLignes].Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1", Formula2:=LgnMax
End With
With [NBColonnes].Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1", Formula2:=ColMax
End With
Set Feuille = Nothing
Set oCat = Nothing
Cn.Close
Set Cn = Nothing
End Sub