ACCESS 2013 - VBA - Besoin d'afficher une fenêtre de dialogue 'OpenFileDialog' sous VBA

Salut, une solution parmi d'autres ( ici pour des fichiers du style : Liste*.xls* ), à adapter à ton contexte.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
Option Explicit
 
Declare Function GetOpenFileName Lib "comdlg32.dll" _
        Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
 
Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type
 
Private Function SelectionFichier(Path As String, Optional Filtre As String = "*.*") As String
Dim OpenFile As OPENFILENAME, lReturn As Long, sFilter As String
    OpenFile.lStructSize = Len(OpenFile)
    sFilter = "Fichiers Excel (" & Filtre & ")" & Chr(0) & Filtre & Chr(0)
    With OpenFile
        .lpstrFilter = sFilter
        .nFilterIndex = 1
        .lpstrFile = String(257, 0)
        .nMaxFile = Len(OpenFile.lpstrFile) - 1
        .lpstrFileTitle = OpenFile.lpstrFile
        .nMaxFileTitle = OpenFile.nMaxFile
        .lpstrInitialDir = Path
        .lpstrTitle = "Fichiers à ouvrir"
        .flags = 0
    End With
    lReturn = GetOpenFileName(OpenFile)
    If lReturn = 0 Then
    Else
        SelectionFichier = Trim$(Left$(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, Chr$(0)) - 1))
    End If
End Function
 
Sub Tst()
Dim Fichier As Variant
Dim sChemin As String, Filtre As String
    sChemin = ThisWorkbook.Path & "\"
    Filtre = "Liste*.xls*"
    Fichier = SelectionFichier(sChemin, Filtre)
    DoEvents
    If Len(Fichier) > 0 Then Workbooks.Open Fichier
End Sub

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Option Explicit
 
Sub SelFichier()
Dim Fichier As Variant, i As Long
 
    ChDir ThisWorkbook.Path & "\"
    Fichier = Application.GetOpenFilename("Texte,*.txt", 1, _
                                          "Sélectionner un fichier", , MultiSelect:=False)
    If TypeName(Fichier) = "Boolean" Then Exit Sub
 
    ' ....
 
End Sub
 
Sub SelFichier_02()
Dim sChemin As String
    sChemin = ThisWorkbook.Path
    With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = sChemin & "\"
        .Title = "Sélectionner un Fichier"
        .AllowMultiSelect = False
        .ButtonName = "Sélection Fichier"
        .Filters.Clear
        .Filters.Add "Excel", "*.xls", 1
        .Show
        If .SelectedItems.Count > 0 Then
            ' ......
        End If
    End With
End Sub

Autrement voir dans les FAQs et Tutoriels ainsi que dans l'aide en ligne.

PS : ce n'est pas le bon forum ..... plutôt ici.