list box to list box to link files from local drive

[axism]

Hi, I want to have a list box listing all files in a folder in my local computer. I have few pdf files and word documents in the folder. I want to be able to click on file in list box to open up that file. I am having problem generating the list box now. Can someone help please? Here's what I have on form_load:

Private Sub Form_Load()
Dim strFiles() As String
Dim intFileCount As Integer
Dim varName As Variant
Dim strName As String

strName = Dir("c:\reports\")

Do Until strName = ""
ReDim Preserve strFiles(intFileCount)
strFiles(intFileCount) = strName
strName = Dir
intFileCount = intFileCount + 1
Loop

For Each varName In strFiles
Me.lstForms.Additem varName
Next

End Sub

lstForms is the name of my list box and that's where the problem is right now. please help! thanks

 

[axism]

is add item only for office 2003? I am using office 2000.

 

[nice95gle]

This will be a quicker way to get your results:

Make sure the Row Source Type of your listbox is set to Value list.

    Dim ScrObj
    Dim SearchFld
    Dim mFileName

    Set ScrObj = CreateObject("Scripting.FileSystemObject")
    Set SearchFld = ScrObj.GetFolder("C:\reports")
    
   For Each mFileName In SearchFld.Files
         Me.lstForms.AddItem mFileName.Name
   Next

 

[axism]

thanks for help nice95gle, but i m still having problem when running it. .ADDItem is highlighted if i run the form, with compile error: Method or data member not found. Can this be a version issue? I am running access 2000.

 

[RoyVidar]

The .additem/.removeitem methods of combos/lists became available in the 2002 (xp) version. For prior version, it's concatenation...

"stealing" nice95gle's code, with some "air code alterations";-)

[tt] dim strRowSource as string
For Each mFileName In SearchFld.Files
strRowSource=strRowSource & mFileName.Name & ";"
Next
Me!lstForms.rowsource = strRowSource[/tt]

Roy-Vidar

 

[RoyVidar]

...oh - and if there's any chance you're getting a rowsource breaking the limit (2048 characters, including semicolons and everything), then there's the callback function, which can circumvent the limitation, have a look here Nailing Your Files: List Files in a Folder

Roy-Vidar

 

[nice95gle]

I totally overlooked your 2nd post where you stated you were using Acc2000.

 

[axism]

sweet works out fine. I was trying to use FilesystemObject and some how it's out of hand hehe. Thnx so much. Now, all I to do is figure out how to open those files upon double clicking. If anything, I'll ask for your help again. Thanks

 

[axism]

hmm Just notice a weird thing. here's code:

Private Sub Form_Load()
    Dim ScrObj
    Dim SearchFld
    Dim mFileName
    Dim strRowSource As String

    Set ScrObj = CreateObject("Scripting.FileSystemObject")
    Set SearchFld = ScrObj.GetFolder("c:\reports\")
    

   For Each mFileName In SearchFld.Files
        strRowSource = strRowSource & mFileName.Name & ";"
    Next
    
      
    Me!List0.RowSource = strRowSource
    Me.List0.ListCount
End Sub

I have 8 pdf files and 1 documents in my dir. somehow it's only showing every other file when list box opens up. any idea what's going on? the rowsource looks right, just with out the "" around file name. can someone help please?

 

[RoyVidar]

Your column count property is set to 1, and there's only one colum width specified for column width?

If that's not enough, you could add quotes:

[tt]strRowSource=strRowSource & chr(34) & mFileName.Name & chr(34) & ";" [/tt]

But I don't know if the latter would change anything...

Roy-Vidar

 

[PHV]

how to open those files upon double clicking
You may consider the FollowHyperlink method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

Just curious, what does Me.List0.ListCount do in this context?

Ken S.

 

[axism]

I am adding sub directories within my reports directory, is there any way to pick up those sub directories and the files within them?

 

[axism]

nvm found out using .SubFolders