UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Add items to listbox from VBA    
 
   
malcolmd83
post Oct 19 2004, 02:18 PM
Post#1



Posts: 1,208
Joined: 19-July 04
From: Cochrane, Ontario, Canada


I am using the following code to search for files within a directory, and then display the results in a number of textboxes.
!--c1-->
CODE
Dim strMask As String
Dim strFile As String
    
    strMask = "*.txt"
    
    strFile = Dir("g:\library files\" & strMask) 'Get first file
    
    Do While LenB(strFile) > 0 'If > 0 then we found file to match mask
        MsgBox ("Found " & strFile) 'display text box
        strFile = Dir() 'Get next file
    Loop

Is it possible to have the results added to a List Box on the form? I could then allow users to choose a file to open from the results (I hope!).
Thanks in advance for any help!
Go to the top of the page
 
Jack Cowley
post Oct 19 2004, 04:27 PM
Post#2


Retired Moderator
Posts: 37,716
Joined: 11-February 00
From: The San Francisco Bay Area


You can create a string and set that as the Row Source for the List box as long as the List boxes Row Source Type is set to Value List. Your string might look like: strFile = "File1;File2;File3:. Then use code similar to this:
e.ListBoxName.RowSource = strFile
hth,
Jack
Go to the top of the page
 
DoubleD
post Oct 19 2004, 04:34 PM
Post#3



Posts: 762
Joined: 8-May 00
From: WI


Here's a function to create a list of values.
CODE
Function GetFiles() As String
  With Application.FileSearch
    .LookIn = "C:\PROD\"
    .Filename = "*.txt"
    If .Execute() > 0 Then
      For I = 1 To .FoundFiles.Count
        GetFiles = GetFiles & .FoundFiles(I) & ";"
      Next I
    Else
      GetFiles = "No Files Found;"
    End If
  End With
  GetFiles = Left(GetFiles, Len(GetFiles) - 1)
End Function

How put this line somewhere in your code when you want to populate the list box:
Me.lstBoxName.RowSource = GetFiles()
Go to the top of the page
 
malcolmd83
post Oct 20 2004, 06:14 AM
Post#4



Posts: 1,208
Joined: 19-July 04
From: Cochrane, Ontario, Canada


That works great! Thank you so much for your help; I appreciate it.
Go to the top of the page
 
malcolmd83
post Oct 20 2004, 06:59 AM
Post#5



Posts: 1,208
Joined: 19-July 04
From: Cochrane, Ontario, Canada


I've now expanded my search to a larger directory, and am now getting a Run-Time error '2176', "The setting for this property is too long". I've done a search on the 'net, and discovered this has something to do with the number of "search results", and a maximum size of the ValueList property. Is there another/better way to do what I am trying to do here?
Go to the top of the page
 
DoubleD
post Oct 20 2004, 08:14 AM
Post#6



Posts: 762
Joined: 8-May 00
From: WI


You could populate a table with the values you return, then set your Listbox to source against that table.
Go to the top of the page
 
malcolmd83
post Oct 20 2004, 08:44 AM
Post#7



Posts: 1,208
Joined: 19-July 04
From: Cochrane, Ontario, Canada


If I do that, I understand there is virtually no size limit, as the ListBox will be using a Table as a source, rather then a Value List. Sounds good...could you point me in the right direction as to how I can change the code you provided to write to a table rather then the ListBox directly? I did a search on the forums, but wasn't able to find the information I'm looking for.
Thanks again for your time!
Go to the top of the page
 
DoubleD
post Oct 20 2004, 09:20 AM
Post#8



Posts: 762
Joined: 8-May 00
From: WI


Here you go.
No just make your rowsource = tblFileList
tblFileList should have one text field called FILE.
!--c1-->
CODE
Sub AddFiles()
Dim db As Connection
Dim rs As New ADODB.Recordset
Dim I As Integer
Set db = CurrentProject.Connection
db.Execute "DELETE * FROM tblFileList"
rs.Open "tblFileList", db, , adLockOptimistic
  With Application.FileSearch
    .LookIn = "C:\PROD\"
    .Filename = "*.xls"
    If .Execute() > 0 Then
      For I = 1 To .FoundFiles.Count
        rs.AddNew "FILE", Dir(.FoundFiles(I))
      Next I
    Else
      rs.AddNew "FILE", "No Files Found"
    End If
  End With
Set rs = Nothing
Set db = Nothing
Me.lstFileList.Requery
End Sub
Go to the top of the page
 
malcolmd83
post Oct 20 2004, 09:47 AM
Post#9



Posts: 1,208
Joined: 19-July 04
From: Cochrane, Ontario, Canada


That does it! Thanks for your time in giving the information. The only question I have now is, for some reason the results which are returned show only the file name and not the full path. Is there a way to have the path included? I will be enabling "Search Sub Folders", so I can't just "add" a default path after. Thanks again!
Go to the top of the page
 
malcolmd83
post Oct 20 2004, 09:52 AM
Post#10



Posts: 1,208
Joined: 19-July 04
From: Cochrane, Ontario, Canada


Never mind. I think I figured it out. I just changed:
CODE
          rs.AddNew "FILE", Dir(.FoundFiles(I))

to
CODE
        rs.AddNew "FILE", (.FoundFiles(I))

Thanks...
Go to the top of the page
 
DoubleD
post Oct 20 2004, 10:02 AM
Post#11



Posts: 762
Joined: 8-May 00
From: WI


Yep, I thought you wanted only the FileName since you were using the Dir function earlier.
You got it! Have fun, if you need any more help just let me know.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th July 2019 - 12:17 PM