View original (French)

Access, list folder contents + clickable

Solved
Brandysve Posted messages 59 Status Member -  
Brandysve Posted messages 59 Status Member -
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 Posted messages 1255 Status Member 155
     
    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
  2. Brandysve Posted messages 59 Status Member 5
     
    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 Posted messages 1255 Status Member 155
       
      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
  3. Brandysve Posted messages 59 Status Member 5
     
    Thank you very much eljojo_e, it's perfect o/

    See you soon
    0