View original (French)

Access, list folder contents + clickable

Solved
Brandysve -  
Hello CCM,

There you go. I have an Access form where I’d like to insert a control (rectangle, drop-down list, it doesn’t matter) that would display in real time all the Excel files located in a folder. Additionally, if someone double-clicks on the name of one of these files, it should open.

Do you have any idea?

Brandysve

3 answers

  1. eljojo_e
    Hello,

    Look at this code (to adapt):

    Private Sub Form_Current()
    Dim strDossier As String
    Dim strFichier As String

    strDossier = "D:\Modèle\2 et 3\"

    ' Verify that the folder exists
    If Dir(strDossier, vbDirectory) = "" Then
    MsgBox "Dossier introuvable !", vbExclamation
    Exit Sub
    End If

    ' List all files in the folder
    strFichier = Dir(strDossier, vbNormal)
    While strFichier <> ""

    ' Display the file name
    Me.ModeleG.AddItem strFichier 'me.ModeleG is a combo box

    ' Read the next file
    strFichier = Dir
    Wend
    End Sub

    You can already list all files in a folder (not only Excel)

    If it helps,

    --
    Le geek ne descend pas du métro, il libère la rame.
    0
    1. Brandysve Author
      Thanks eljojo_e, I almost solved my issue. Here’s what I found via the helpdesk 
      Private Sub ListBox2_Click()
MsgBox ListBox2.Value
End Sub
Private Sub Form_Open(Cancel As Integer)
Me.ListBox2.RowSourceType = "Liste valeurs"
Me.ListBox2.RowSource = ShowFolderList("C:\Users\***\Desktop\En attente")
End Sub

Function ShowFolderList(folderspec) As String
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 In fc
s = s & f1.Name
s = s & ";"
Next
ShowFolderList = s
End Function
      I just want now that when I double-click on a file name, it opens.
      0
      1. eljojo_e
        With my code that feeds a dropdown list, you insert code on the after_update event, which opens the file with the name you selected: 


        Private Sub xxxx_afterupdate()
        Dim xls As Excel.Application
        On Error GoTo errHnd
        Set xls = CreateObject("Excel.Application")
        xls.Workbooks.Open "D:\tartanpion\" & Me.Listedéroulante.Value ' and if there is no extension, add ".xls"
        xls.Visible = True
        Exit Sub
        errHnd:
        MsgBox "Erreur N° " & Err.Number & vbLf & Err.Description, , Err.Source
        End Sub
        0
    2. Brandysve Author
      Thank you very much eljojo_e, it's perfect o/

      See you soon
      0