Consulting

Results 1 to 14 of 14

Thread: Search files from textbox in UserForm, display result in Listbox

  1. #1
    VBAX Regular
    Joined
    Dec 2021
    Posts
    17
    Location

    Search files from textbox in UserForm, display result in Listbox

    Hi there,

    I've been searching high and low, but haven't found what i'm looking for yet.

    Its actually pretty simpel (i guess).

    First step:

    Textbox1, Conmandbutton1, listbox1 and listbox2 is shown in userform1.

    (example-)value 22444 is typed in TextBox1
    CmdBotton1 is then pressed
    Result in ListBox1 (only PDF files, location H:\pdf)
    Result in Listbox2 (only DXF files, location H:\dxf)

    but,
    • the found files would/could look like this: 22444_1.pdf, 22444_2.pdf, 22444_A.pdf, 22444_B etc.
    • subfolders might occure and the result from those should be present in the listboxes too


    If the searchstring = 22444 the file will ALWAYS be stored in H:\pdf\20
    So, to make the searching faster i've written the search path like this:
    mypathPDF = "H:\pdf\" & Left(textbox1.value, 1) & "0\" (it should result in a path = H:\pdf\20\)
    mypathDXF = "H:\dxf\" & Left(textbox1.value, 1) & "0\" (it should result in a path = H:\dxf\20\)
    Shoud the searching be seperated into 2 sessions? like by performing the pdf-file search first and then the DXF afterwards?

    Can anybody help me in moving forward? (and type me some code)


    Second step; is to be able to select one item in each listbox and and press a copy-button (comandbutton2). afterwards you switch to an email and press Ctrl+V to insert the copied objects into the mail.
    (don't know if that's possible at all?)

    Thanks
    /c
    Last edited by c_skytte; 12-08-2021 at 05:54 AM.

  2. #2
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    680
    Location
    Hi there,

    Just given your request a little thought and noted the below part, the piece below will be difficult as VBA has limited access to the clipboard in my experience, maybe you could have the files saved to a specific location to be copied instead?

    Quote Originally Posted by c_skytte View Post
    Second step; is to be able to select one item in each listbox and and press a copy-button (comandbutton2). afterwards you switch to an email and press Ctrl+V to insert the copied objects into the mail.
    (don't know if that's possible at all?)
    The next bit i noted was that you have given an example of the files location:
    mypathPDF = "H:\pdf\" & Left(textbox1.value, 1) & "0\" (it should result in a path = H:\pdf\20\)
    mypathDXF = "H:\dxf\" & Left(textbox1.value, 1) & "0\" (it should result in a path = H:\dxf\20\)
    But you have also specified that there may be sub folders, would these subfolders be inside the paths you have mentioned above or elsewhere?

    I have not started to code anything but I gave it some thought as if I was going to and came up with the questions/ issues above.

    Hope you get it all sorted soon
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  3. #3
    VBAX Regular
    Joined
    Dec 2021
    Posts
    17
    Location
    Hi again,

    I'm a bit concerned about the copyfunction too... but i hope to figure it out later ..
    I think I have seen it once, but can't remember where.

    Yes, the subfolders are placed inside the path that is generated... as
    H:\pdf\20\old\
    H:\pdf\20\outdated\
    ...or similar

  4. #4
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    680
    Location
    Quote Originally Posted by c_skytte View Post
    I'm a bit concerned about the copyfunction too... but i hope to figure it out later ..
    I think I have seen it once, but can't remember where.
    Another option may be to have the code open up a new email (Outlook) and have the code attach the specified files?

    This may not be suitable if you wish to paste the files into a reply email.
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  5. #5
    VBAX Regular
    Joined
    Dec 2021
    Posts
    17
    Location
    Yes of course! ...but to be honest it's way above my level to get there!

  6. #6
    VBAX Regular
    Joined
    Dec 2021
    Posts
    17
    Location
    Hi georgiboy

    i'm not gonna put you under pressure but silently just asking if you are trying to put something together for me?

    thanks in advance!

  7. #7
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    680
    Location
    I am looking at it in between what I am doing, I may be beaten to it if others on the forum are feeling energetic.
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  8. #8
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    778
    Location
    Not that energetic but I had something similar which may work. Adjust the userform and listbox names to suit. HTH. Dave
    Userform code...
    Private Sub CommandButton1_Click()
    Call listallfiles("H:\pdf")
    Call listallfiles("H:\dxf")
    End Sub
    
    
    Function listallfiles(strfolder As String)
    Dim objfso As Object, objfolder As Object
    Set objfso = CreateObject("scripting.filesystemobject")
    Set objfolder = objfso.GetFolder(strfolder)
     Call getfiledetails(objfolder)
    Set objfolder = Nothing
    Set objfso = Nothing
    End Function
     
     
    Function getfiledetails(objfolder As Object)
    Dim objfile As Object, objsubfolder As Object
    For Each objfile In objfolder.Files
    If objfile.Name Like "*" & ".pdf" Then
    UserForm1.ListBox1.AddItem objfile.Name
    End If
    If objfile.Name Like "*" & ".dxf" Then
    UserForm1.ListBox2.AddItem objfile.Name
    End If
    Next objfile
    For Each objsubfolder In objfolder.SubFolders
    Call getfiledetails(objsubfolder)
    Next objsubfolder
    Set objsubfolder = Nothing
    Set objfile = Nothing
    End Function
    Note: This does nothing for the copy file part

  9. #9
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    680
    Location
    I have created the attached to get you on the right track to get all parts done. There is a lot more you can do with this for error handling etc...

    Hope it helps
    Attached Files Attached Files
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  10. #10
    VBAX Regular
    Joined
    Dec 2021
    Posts
    17
    Location
    First of all; Thank you all ..or both!


    I tried to go on with Dave's solution an managed to make some adjustments in order to make the listing "correct" (inserted Textbox1.value in the search etc).
    but in ordre to move on to the "attach to email" i got totally lost in space and a wave of frustration builded up in front of me!


    10 minutes ago - after been spending more than 2 days in "code-hell" - i realized that georgiboy actually have managed to reach the goal for me!!
    Georgiboy, I can't thank you enough! ...even though some coding still have to be done, your efford is highly appreciated!


    ( I'll keep this one open, if some questions still might occure! )


    /c_skytte

  11. #11
    VBAX Regular
    Joined
    Dec 2021
    Posts
    17
    Location
    Hi again,

    My next problem is that if a listbox returns nothing (doesn't find any matching files in specific folder), is it then possible to insert a non selectable default value in the listbox like: (Empty) or (Nothing found)?

    this is the code to do the listing:

    Sub LoopAllSubFoldersDXF(FSOFolderDXF As Object)
        Dim FSOSubFolderDXF As Object
        Dim FSOFileDXF As Object, FSODXF As Object
        Dim tmpValDXF As String, extDXF As String
        
        Set FSODXF = CreateObject("Scripting.FileSystemObject")
        
    
    
    
    
        For Each FSOFileDXF In FSOFolderDXF.Files
            extDXF = LCase(FSODXF.GetExtensionName(FSOFileDXF.Path))
            tmpValDXF = FSOFileDXF.Name
            tmpValDXF = Left(tmpValDXF, InStrRev(tmpValDXF, ".") - 1)
            tmpValDXF = Split(tmpValDXF, "_")(0)
            tmpValDXF = Split(tmpValDXF, ".")(0)
            If tmpValDXF = SearchStringDXF Then
                If extDXF = "dxf" Then
                    y = y + 1
                    ReDim Preserve DXFvarPath(y): DXFvarPath(y) = FSOFileDXF.Path
                    ReDim Preserve DXFvarName(y): DXFvarName(y) = FSOFileDXF.Name
                End If
            End If
            
            
        Next
        y = 0
    
    
    End Sub
    shoud it be inserted as an Else-function?

    thanks!

  12. #12
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    680
    Location
    If you look inside the 'Search' button code on the userform you will find a line of code:
    Me.ListBox1.List = PDFvarName
    You can swap that for the below:
    If Len(Join(PDFvarName)) > 0 Then        
         Me.ListBox1.List = PDFvarName
         ListBox1.Enabled = True
         ListBox1.ForeColor = vbBlack
    Else
         Me.ListBox1.List = Array("Not Found")
         ListBox1.Enabled = False
         ListBox1.ForeColor = RGB(128, 128, 128)
    End If
    You will also find line:
    Me.ListBox2.List = DXFvarName
    You can swap that for the below:
    If Len(Join(DXFvarName)) > 0 Then    
        Me.ListBox2.List = DXFvarName
        ListBox2.Enabled = True
        ListBox2.ForeColor = vbBlack
    Else
        Me.ListBox2.List = Array("Not Found")
        ListBox2.Enabled = False
        ListBox2.ForeColor = RGB(128, 128, 128)
    End If
    Hope this helps
    Last edited by georgiboy; 01-10-2022 at 07:47 AM.
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  13. #13
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,417
    Condensed to:
    with ListBox2
      .List = DXFvarName
      .Enabled = ubound(.list)>-1
      .ForeColor = RGB(128, 128, 128)* (Ubound(.list)=-1)
      if ubound(.List)= -1 then .List =Array("Not Found")
    end with

  14. #14
    VBAX Regular
    Joined
    Dec 2021
    Posts
    17
    Location
    Thank you so much!


    I used snb's solution ... but haven't tested what Georgiboy came up with.
    I really appreciate your boths effort ...REALLY!


    So, now I know where to look for answers if some challenges in Excel lays way above my basic novice skills!
    You guys are amazing! Thanks!

Posting Permissions

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