populate combo with Dir

Access 2000...

With the help of someone on this forum, I was able to populate a combo box with the filenames in a given folder.

There appears to be a limitation however.

Here is the code......

Private Sub Form_Open()
'Code to populate project report combo box with directory contents
Dim str_filename As String
Dim str_filenames As String

' Change folder path below as desired:
str_filenames = Dir$("c:\windows\desktop\*.*")

Do
str_filename = Dir
str_filenames = str_filenames & ";" & str_filename
Loop Until str_filename = ""

cmb_filenames.RowSource = str_filenames
cmb_filenames.Value = Mid$(str_filenames, 1, InStr(str_filenames, ";") - 1)

End Sub


As this code is written, it will run fine, but change the directory to say, C:\windows\system\ it dies because the directory is chock full of files.

The code works until the directory exceeds X number of files. What that exact limitation is, I don't know. Maybe it's a limit on the number of characters allowed in a string. Again, I don't know.

We already have more files in the folder on the server than the code can handle, and we add a couple more every week.

Is there a way around this? What I am trying to do is allow the user to open the Word document associated with a particular record.

The other approach I tried was creating a table of the directory filenames which we will have to manually update. This extra step is acceptable, however, using SHELL fails because of spaces in the directory path and spaces in the filenames.

Thanks.

Using shell shouldn't fail due to spaces between folderNames or filenames. This is because you could enclosed foldernames and/or filenames in quotes (the "rabbit ears")

Here is a code sample. Once I get this working, I'll use an SQL statement to grab the filename from the table. Right now, I just want this to work....

Private Sub btn_open_in_word_Enter()

Dim str_wordpath As String
Dim str_filepath As String
Dim str_filename As String

str_wordpath = "c:\program files\microsoft office\office\winword "
str_filepath = "c:\windows\desktop\"
str_filename = "best market com services.doc"

Shell str_wordpath & str_filepath & str_filename

End Sub


It dies when opening Word. Word says it can't find C:\windows\desktop\best

What gives? Looks to me like it doesn't like spaces.

ok, a batch file is not really required. Try these codes I have tested.

If you have questions, read the documentation.

If it does not work, make another post.

Private Sub btn_open_in_word_Enter()
Dim str_wordpath As String
Dim str_filepath As String
Dim str_filename As String
Dim tmp As String, strQuote As String

strQuote = Chr$(34)

str_wordpath = "c:\program files\microsoft office\office\winword "
str_filepath = "c:\windows\desktop\"
str_filename = "best market com services.doc"
tmp = str_wordpath + strQuote + str_filepath + str_filename + strQuote
rem MsgBox tmp
Shell tmp
End Sub

Yup, that did it. Thank you so much.

While this works and is acceptable, I would still prefer to do it the Dir() way. If the files are in the directory, Dir() is foolproof. No one has to enter data into a table. So, i'm still open to suggestions.

Thanks again jerryfchui.

Is every file in the directory a word doc? If not the limit the dir command with Dir$("pathtofiles\*.doc").

There are a couple of powerpoint presentations in there, but that doesn't matter. Trying to cut down on the number of files with an exclusive extension won't work because the number of docs grows each week.

Rather than use Dir, use FileSystemObject and the Folders collection:
<pre>
Sub Test(FolderName As String)
Dim fs As Object
Dim f, f1, fc, s

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(FolderName)
Set fc = f.subfolders

If fc.Count &gt; 0 Then

For Each f1 In fc
Debug.Print FolderName & "\" & f1.Name
Test (f1)
Next

End If

End Sub
</pre>
.
This should do what you need.



______
Cheers!

Thanks! I can see this working, but am at a loss on how to populate the combo box rowsource with "f".

Anything you do that is manually populating the RowSource will end up giving you the same problem. I think you were right about exceding some maximum string length. On the other hand, if you populate a table and there are that many choices, how can your users ever hope to find the right file. What about having an additional textbox for the user to enter some part of the filename being search for. Then you can use the FileSystemObject or Dir$ to find just the files that match.

I don't disagree. It's a trade-off between having access to all the files without fail because they're already in the folder, or adding the extra step of manually entering the document name in a field on some form and pulling it from a table. While the latter option works, it still depends on someone going back and entering the correct filename &lt;--- two things that could go wrong....

Thanks for all your help folks.

This method could be used to "manually" populate your table from where you pull your data. Instead of the Debug.Print, write yourself a little routine that repopulates the table before the user sees the options.

______
Cheers!

Yes I thought of that this afternoon and will work in that direction over the weekend.

Thanks.