Search files from textbox in UserForm, display result in Listbox

[c_skytte]

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

[georgiboy]

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?

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

[c_skytte]

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

[georgiboy]

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.

[c_skytte]

Yes of course! ...but to be honest it's way above my level to get there!

[c_skytte]

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!

[georgiboy]

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.

[Dave]

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

[georgiboy]

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

[c_skytte]

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

[c_skytte]

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!

[georgiboy]

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

[snb]

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

[c_skytte]

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!