Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14

    Unanswered:

    In the following example, the Form has:
    1. A TextBox control named Text_Folder that contains the path to the folder to explore.
    2. A ListBox control named List_Files, with the following properties:
    - Name: List_Files
    - ColumnCount: 2
    - RowSourceType: "Value List"
    3. A Command button named Command_GetFileList with its OnClick property linked the the event handler Command_GetFileList_Click() in the Class Module of the Form.

    The code in the Class Module of the Form is:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Command_GetFileList_Click()
    
        Me.List_Files.RowSource = GetFileList(Me.Text_Folder.Value)
        
    End Sub
    
    Function GetFileList(ByVal Folder As String) As String
    
        Dim lngCount As Long
        Dim strFileName As String
        
        If Right(Folder, 1) <> "\" Then Folder = Folder & "\"
        strFileName = Dir(Folder & "*.*")
        Do While Len(strFileName)
            lngCount = lngCount + 1
            If Len(GetFileList) > 0 Then GetFileList = GetFileList & ";"
            GetFileList = GetFileList & lngCount & ";" & strFileName
            strFileName = Dir
        Loop
        
    End Function
    When you click on the Command button Command_GetFileList, the ListBox List_Files is populated with the list of the files stored in the folder the path of which is contained in the TextBox Text_Folder.
    Attached Thumbnails Attached Thumbnails GetFileList.jpg  
    Have a nice day!

  2. #2
    Join Date
    Mar 2013
    Posts
    29

    Impossible? Past list of files from windows to access

    It is about VBA.
    First of all I describe my problem:
    e.g. I writing order number 1:
    I'm creating a new folder in my disk e.g. C\Myfiles\ClientX\27-03-2013
    then I past files into that folder e.g. x.pdf, z.doc, y.tiff.
    Then I copy path from Window "C\Myfiles\Clientx\27-03-2013"
    and past to filed "path" in my form (multiple items).
    ------------------------------------------------------
    That's the point:
    I want to have list of files from this folder in my form, so It would be great when I will do this automaticly using button.
    Like: I click button "show files" and next filed "files" appear the list of file from my folder.
    ------------------------------------------------------
    Next problem:
    It would be really great if I will click on the name of file in my form e.g. "x.pdf" and open it. but I'm not sure that, is it possible to do? If not, I can have only a list of files.

    Memo with rich text:
    1. x.pdf
    2. z.doc
    3 y.tiff

    Or if above isn't possible:
    x.pdf; x.doc; y.tiff

    Is it possible to do in VBA? I am looking for help in other forums but nobody know how to help me :/
    Last edited by andrzejfox; 03-28-13 at 09:01.

  3. #3
    Join Date
    Mar 2013
    Posts
    29
    Thanks for insight!
    Almost. But I'm really shocked that is possible.
    My problem is that I have a form "Multiple Items"

    Please see my screenshot.
    In first record is everything OK
    But in second record, I have a other path and when I click button, one list will appear in all of the records.

    I think it's one way to fix it.
    instead of List should be Memo with "rich text" properties.
    If we have "rich text" we can on "numbering" which do some text like "1. x.pdf, 2. y.cdr"
    But List would be better because, In list I can do event on click to open file, using FollowHyperlink Method right?

    Sinndho What Do You thing about that?
    Attached Thumbnails Attached Thumbnails 1.jpg  
    Last edited by andrzejfox; 03-28-13 at 11:55.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by andrzejfox View Post
    I think it's one way to fix it.
    instead of List should be Memo with "rich text" properties.
    If we have "rich text" we can on "numbering" which do some text like "1. x.pdf, 2. y.cdr"
    But List would be better because, In list I can do event on click to open file, using FollowHyperlink Method right?

    Sinndho What Do You thing about that?
    In such a case, you need to write a formatter function to convert the data from the Dir() function to RTF. Seems to be a lot of work to get little results.

    I don't understand what the problem is for what you named "the second record". The GetFileList() function will work with any path it receives as argument. If there are more than one path to be explored (which you first post did not mentioned), it's possible to adapt it for exploring several folders and return the results to a single ListBox control.
    Have a nice day!

  5. #5
    Join Date
    Mar 2013
    Posts
    29
    Thanks for your reply Sinndho.

    I thought all night about my process in my firm (what I should do in form when files have changed). You was right that I forgot mention about more than one path, but I think this code could be my final. I don't need change this. (I will create some folder after update in form but now it doesn't matter - I can do it myself.)

    So If we have path e.g. "C:\MyFiles" and we have name of files in list e.g. "1. x.pdf"
    we can do link to open it (event on dbl click) using FollowHyperlink Method, right? I know that link should looks like:
    me.path & "\" & me.List_Files, but I don't know how to write in vba that link should be without e.g. "1."
    Last edited by andrzejfox; 03-29-13 at 06:08.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The ListBox List_Files has 2 columns. The first holds a file number and the second holds a file name. You can:
    1. Change the BoundColumn property and set it to 2. Then, when you need to retrieve the file name for the selected row in the list, you can use:
    Code:
    Dim strFileName as String
    strFileName = Me.List_Files.Value
    2. You can also use the Column property of the ListBox to have access to any column within it, whatever the value of the BoundColumn property can be:
    Code:
    Dim strFileName as String
    strFileName = Me.List_Files.Column(1) ' Leftmost column is Column(0).
    Have a nice day!

  7. #7
    Join Date
    Mar 2013
    Posts
    29
    Code:
    Private Sub List_Files_DblClick(Cancel As Integer)
    Dim strFileName As String
    Dim path As String
    strFileName = Me.List_Files.Value
    path = Me.Text_Folder & "\" & strFileName
    Application.FollowHyperlink path
    End Sub
    So that's all

    I am very grateful!
    Thanks Sinndho!!!!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome.

    p.s. Since you chose to use the Value property to retrieve the file name, don't forget to change the BoundColumn property of the ListBox.
    Have a nice day!

  9. #9
    Join Date
    Mar 2013
    Posts
    29
    Sinndho, What Should I do, to save date from Me.List_Files in my table (where i should write record source)?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In such a case, I would insert the file names into a table and use that table (or part of it through a query) as the RowSource of the list.
    Have a nice day!

  11. #11
    Join Date
    Mar 2013
    Posts
    29
    I have the list in bound form which name is "Orders"
    So in tblOders I created 2 columns: "numbers" and "List_Files" - to save data from form

    In RowSource of my list I write:
    sELECT [tblOrders].[numbers], [tblOrders].[List_Files] FROM tblOrders ORDER BY [numbers];

    It's not working:/

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Whether the list is in a bound form or not does not matter here: a ListBox control has its own data source defined by its RowSource property.
    Have a nice day!

  13. #13
    Join Date
    Mar 2013
    Posts
    29
    Ok, so what I should write in RowSource property?

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Create a Table where the names of the files will be stored (only once):
    Code:
    Sub CreateTable()
    
        Const c_SQL As String = "CREATE TABLE Tbl_FileList ( FileNumber LONG CONSTRAINT PK_Tbl_FileList PRIMARY KEY, FileName TEXT(255) );"
        
        CurrentDb.Execute c_SQL, dbFailOnError
        
    End Sub
    2. Change the function GetFileList, like this:
    Code:
    Function GetFileList(ByVal Folder As String) As String
    
        Const c_SQL As String = "INSERT INTO Tbl_FileList ( FileNumber, FileName ) VALUES ( @C, '@F' );"
        Dim lngCount As Long
        Dim strFileName As String
        
        CurrentDb.Execute "DELETE FROM Tbl_FileList;", dbFailOnError
        If Right(Folder, 1) <> "\" Then Folder = Folder & "\"
        strFileName = Dir(Folder & "*.*")
        Do While Len(strFileName)
            lngCount = lngCount + 1
            CurrentDb.Execute Replace(Replace(c_SQL, "@C", lngCount), "@N", strFileName), dbFailOnError
    '
    ' Note: If you want to store the path (folder name) in the table, use:
    '
    '        CurrentDb.Execute Replace(Replace(c_SQL, "@C", lngCount), "@N", Folder & strFileName), dbFailOnError
    '
            strFileName = Dir
        Loop
        GetFileList = "Tbl_FileList"
        
    End Function
    3. Keep the Command_Click event handler as it is:
    Code:
    Private Sub Command_GetFileList_Click()
    
        Me.List_Files.RowSource = GetFileList(Me.Text_Folder.Value)
        
    End Sub
    Have a nice day!

  15. #15
    Join Date
    Mar 2013
    Posts
    29
    I don't know why, but this upgraded code doesn't work. I recive some value "@" in tab, but in list there are no value.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •