PDA

View Full Version : Sleeper: Load Folder Content in Single Column List Box



sheeeng
06-24-2005, 01:08 AM
Hi all :hi: ,

How can I load a folder contents into a list box?

The list box display the full name of the file.

After that, when I double click the item in list box, it will open the file.

eg. C:\MyFile has files such as name.txt, add.txt, money.txt
(most of my file is *.txt)

ListBox <------ load the folder content into list box
---------
name.txt <----- double click here will open name.txt with notepad.
add.txt
money.txt

Thanks.:friends:

Bob Phillips
06-24-2005, 03:23 AM
How can I load a folder contents into a list box?

The list box display the full name of the file.

After that, when I double click the item in list box, it will open the file.



Sheeng,

Why not just create a new worksheet with a hyperlinked list of files. I showed an example yesterday at http://tinyurl.com/7l4lv

sheeeng
06-24-2005, 07:16 AM
Sheeng,

Why not just create a new worksheet with a hyperlomked list of files. I showed an example yesterday at http://tinyurl.com/7l4lv

Thanks for the information. :clap: But it looks messy when 100+ files involved. :(
Can we still proceed on list box? :friends:

Btw, what is tinyurl for? Why it leads to google groups?

Norie
06-24-2005, 07:32 AM
Where is the listbox located?

Is it on a userform?

Please see the attached

Bob Phillips
06-24-2005, 07:35 AM
Thanks for the information. :clap: But it looks messy when 100+ files involved. :(
Can we still proceed on list box? :friends:

Btw, what is tinyurl for? Why it leads to google groups?

Easybit first. Tinyurl is a web service that will take a long URL and apply an encoding algorithm that comes up with a link to TinyURL. When you click on the TinyURL URL, it is decoded and links you to the real URL. We use it a lot in the newsgroups where wrap-around cuts URLs up.

You can add TinyURL as a link in your browser, see http://tinyurl.com/#toolbar

There are many similar services, such as Make A Shorter link, Snurl, etc. I like TinyURL the best.

In what way do you find it messy, is it the grouping, the extar file information? If so, these could be removed.

sheeeng
06-24-2005, 07:47 AM
Easybit first. Tinyurl is a web service that will take a long URL and apply an encoding algorithm that comes up with a link to TinyURL. When you click on the TinyURL URL, it is decoded and links you to the real URL. We use it a lot in the newsgroups where wrap-around cuts URLs up.

You can add TinyURL as a link in your browser, see http://tinyurl.com/#toolbar

There are many similar services, such as Make A Shorter link, Snurl, etc. I like TinyURL the best.

In what way do you find it messy, is it the grouping, the extar file information? If so, these could be removed.

Well, the tabs on most left is very distracting for me. Sorry to mention it.
Plus, It list the name of folder repeatedly. (I don't know why.) Maybe too much file and folder contain in parent folder....:help

sheeeng
06-24-2005, 08:26 AM
Where is the listbox located?

Is it on a userform?

Please see the attached

The attachment excel macro seem to stop for long time with no results...:(
How?:friends:

gsouza
06-24-2005, 08:44 AM
so how do you double click on the list to open a specific file listed? I want to know also : )

gsouza
06-24-2005, 08:53 AM
I think i did it



Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Link = ListBox1.Text
On Error GoTo NoCanDo
ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
Unload Me
Exit Sub
NoCanDo:
MsgBox "Cannot open " & Link
End Sub

Bob Phillips
06-24-2005, 09:17 AM
Well, the tabs on most left is very distracting for me. Sorry to mention it.
Plus, It list the name of folder repeatedly. (I don't know why.) Maybe too much file and folder contain in parent folder....:help

Try this



Option Explicit
Private FSO As Object
Private cnt As Long
Private arfiles

Private Const kFolder As String = "c:\myTest"
Sub Folders()
Dim i As Long
Dim sFolder As String
Dim iStart As Long
Dim iEnd As Long
Dim fOutline As Boolean
Set FSO = CreateObject("Scripting.FileSystemObject")
arfiles = Array()
cnt = -1
ReDim arfiles(2, 0)
If sFolder <> "" Then
SelectFiles kFolder
Application.DisplayAlerts = False
On Error Resume Next
Worksheets("Files").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add.Name = "Files"
With ActiveSheet
For i = LBound(arfiles, 2) To UBound(arfiles, 2)
If arfiles(0, i) = "" Then
With .Cells(i + 1, 1)
.Value = arfiles(2, i)
.Font.Bold = True
End With
iStart = i + 1
iEnd = iStart
fOutline = False
Else
.Hyperlinks.Add Anchor:=.Cells(i + 1, 2), _
Address:=arfiles(0, i), _
TextToDisplay:=arfiles(2, i)
iEnd = iEnd + 1
End If
Next
.Columns("A:Z").Columns.AutoFit
End With
End If
End Sub


Sub SelectFiles(Optional sPath As String)
Dim oSubFolder As Object
Dim oFolder As Object
Dim oFile As Object
Dim oFiles As Object
Dim arPath
arPath = Split(sPath, "\")
cnt = cnt + 1
ReDim Preserve arfiles(2, cnt)
arfiles(0, cnt) = ""
arfiles(2, cnt) = sPath
Set oFolder = FSO.GetFolder(sPath)
Set oFiles = oFolder.Files
For Each oFile In oFiles
cnt = cnt + 1
ReDim Preserve arfiles(2, cnt)
arfiles(0, cnt) = oFolder.Path & "\" & oFile.Name
arfiles(1, cnt) = Right(oFile.Name, Len(oFile.Name) - _
InStrRev(oFile.Name, "."))
arfiles(2, cnt) = oFile.Name
Next oFile
For Each oSubFolder In oFolder.Subfolders
SelectFiles oSubFolder.Path
Next
End Sub

Norie
06-24-2005, 09:31 AM
Change this line


ListBox1.AddItem .FoundFiles(I)
to this


ListBox1.AddItem Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "\") + 1)
To get rid of the path.

sheeeng
06-25-2005, 03:24 AM
Change this line


ListBox1.AddItem .FoundFiles(I)
to this


ListBox1.AddItem Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "\") + 1)
To get rid of the path.

Sorry. :help The program cannot work, Execution time seem halted. How? :friends:

Bob Phillips
06-25-2005, 03:52 AM
The program cannot work, Execution time seem halted. How?

How many files have you got? I could only take one tenth of a second even on the windows directory files.

ANyway, this code is 20-30% quicker than the other guy's in my tests. You need to set a reference to the Microsoft Scripting Runtime library



Dim oFSO As Scripting.FileSystemObject
Dim oFolder As Folder, oFile As File
Set oFSO = CreateObject("Scripting.FilesystemObject")
Set oFolder = oFSO.getfolder("c:\windows")
For Each oFile In oFolder.Files
ListBox1.AddItem oFile.Name
Next oFile

sheeeng
06-25-2005, 06:26 AM
How many files have you got? I could only take one tenth of a second even on the windows directory files.

ANyway, this code is 20-30% quicker than the other guy's in my tests. You need to set a reference to the Microsoft Scripting Runtime library



Dim oFSO As Scripting.FileSystemObject
Dim oFolder As Folder, oFile As File
Set oFSO = CreateObject("Scripting.FilesystemObject")
Set oFolder = oFSO.getfolder("c:\windows")
For Each oFile In oFolder.Files
ListBox1.AddItem oFile.Name
Next oFile



Can yo attach your file? :friends: How can I set a reference to the Microsoft Scripting Runtime library? Through Menu in VBE or by Code? :help

Ivan F Moala
06-25-2005, 01:01 PM
If speed is an issue then use the Dir command instead of FSO then you don't need to set a reference to it. Look up DIR in you VBA help.

sheeeng
06-25-2005, 10:30 PM
If speed is an issue then use the Dir command instead of FSO then you don't need to set a reference to it. Look up DIR in you VBA help.

Can you show me a sample? Thanks.:friends:

Ivan F Moala
06-25-2005, 11:31 PM
XLD showed you a link that has an example

see http://tinyurl.com/#toolbar

Justinlabenne
06-25-2005, 11:58 PM
See if this gets you started:

sheeeng
06-26-2005, 02:11 AM
See if this gets you started:

That's great!! :thumb Almost like what I need... :think:
Can you modify the code so as to remove the full path?
Just show the file name in the list box, but show the path at title of form...

Why cannot link to the file through the list box?

Thanks!! :friends:

Justinlabenne
06-26-2005, 02:18 AM
Since this idea is something I could actually find useful myself, give me some time and I will finish it up.

sheeeng
06-26-2005, 02:47 AM
Since this idea is something I could actually find useful myself, give me some time and I will finish it up.

Thanks, Justinlabenne.
I can wait for 2/3 days....if possible.....:friends:

Btw, I appreciate your hard work and effort. :beerchug:

Justinlabenne
06-26-2005, 03:03 AM
Anybody test this and see if any flaws can be found?

I was falling asleep doing it, so there may be oversights, but it seems ok minus some actual error handling:

sheeeng
06-26-2005, 03:15 AM
Initial testing is GOOD! :thumb

I try testing more aggresively tomolo. :cool:
Thank you for your time and effort..... :friends:

Thanks. :hi:

mdmackillop
06-26-2005, 03:39 AM
Hi Justin and Sheeng
One small suggestion, add the following to the userform code to open the browse window automatically.


Private Sub UserForm_Initialize()
cmdLoadFiles_Click
End Sub

Norie
06-26-2005, 06:19 AM
Justin

When I choose C: from the folder browser I get a Run time error 52 - Bad file name or number.

mdmackillop
06-26-2005, 08:05 AM
Another minor change to avoid errors on empty disk drives.


Private Sub cmdLoadFiles_Click()
Const sBrowseMsg As String = "Select a folder"
Dim sPath As String
Dim sFiles As String
Dim tmp
lstFiles.Clear
' Browse for our folder:
sPath = BrowseFolders(ApphWnd, sBrowseMsg, BrowseForFoldersOnly, CSIDL_DRIVES)
' if nothing is selected, or cancel pressed
If sPath = "" Then Exit Sub
On Error Resume Next
Dir sPath
If Err <> 0 Then
sPath = "No disc in " & sPath
GoTo ErrH
End If
' What files do we have in our folder?
' Look at all types
sFiles = Dir(sPath & "\*.*", vbNormal)
Do Until sFiles = ""
' Using a loop, load the file names into the listbox
With lstFiles
.AddItem sFiles
End With
sFiles = Dir()
Loop
ErrH:
' Show selected folder path on the form
Me.Caption = sPath
' Return focus to the form
lstFiles.SetFocus
End Sub

Ivan F Moala
06-27-2005, 02:28 AM
Just one other point Clear your listbox After deciding to carry on further



If sPath = "" Then Exit Sub

lstFiles.Clear


Also, as you are actually opening any file type it would also pay to include
an "Open With" dialog for those files not associated with a file type by the use of the SHell API. You can do this via RunDll

Justinlabenne
06-27-2005, 08:21 AM
Thanks for the feedback, added the changes posted so far. I personally would prefer to filter the list of files to certain types (I personally dont need a list of .dll's to open if you browse to C:\Windows, :dunno)

Just my preference.

I was wondering what the interest level of this is to everybody, would it be useful as an add-in at all? Since this generated some feedback, I planned adding the files into an array to sort by extension or something, and to filter the list so we don't have to go the rundll route, with an "Open With" dialog. Just my opinion, and most certainly would add it in if eveyone would agree that it is necessary, I don't think I do, but I am thinking from a developer standpoint, let me know what everyone's thinking and I welcome your ideas as to other items to include if the interest is there.

Later, and here is a copy updated, I moved all code into the Userform module for further encapsulation plus the above additions

mdmackillop
06-27-2005, 09:18 AM
Hi Justin,
I agree with the dll files, and you should also consider exe files which maybe should not be run from this type of utility.
Regarding the setup, I think the single click to open files is too "sensitive". I prefer a double click/button with a keyboard alternative, which allows me to scroll down a list highlighting each, then pressing Enter to open the desired file.

sheeeng
06-27-2005, 07:40 PM
Hi Justin,
I agree with the dll files, and you should also consider exe files which maybe should not be run from this type of utility.
Regarding the setup, I think the single click to open files is too "sensitive". I prefer a double click/button with a keyboard alternative, which allows me to scroll down a list highlighting each, then pressing Enter to open the desired file.

I also do prefer double click. :friends: Can someone help out to proceed with Load Folder Content in Single Column List Box and double click an item in the list box to open?

Thanks : pray2:

Ivan F Moala
06-27-2005, 08:49 PM
Thanks for the feedback, added the changes posted so far. I personally would prefer to filter the list of files to certain types (I personally dont need a list of .dll's to open if you browse to C:\Windows, :dunno)


Filter by all means, but an Open With is a user interface option that most users are use to with this type of thing... it's not to open a Dll, more another option that I as a user like to have as I have more then ONE application that I use to open files with. eg App open with Hex Editor, VB.NET, or another application that supports the file type. As a developer you need to be thinking along the lines of what your customers want and to point out to them other options that they may not have thought of.. other wise nice job :)



Just my preference.


I was wondering what the interest level of this is to everybody, would it be useful as an add-in at all? Since this generated some feedback, I planned adding the files into an array to sort by extension or something, and to filter the list so we don't have to go the rundll route, with an "Open With" dialog. Just my opinion, and most certainly would add it in if eveyone would agree that it is necessary, I don't think I do, but I am thinking from a developer standpoint, let me know what everyone's thinking and I welcome your ideas as to other items to include if the interest is there.

Later, and here is a copy updated, I moved all code into the Userform module for further encapsulation plus the above additions

Justinlabenne
06-28-2005, 01:29 AM
How true Ivan :bow:

I have yet to develop anything for anybody that is a user quite near your level, and most people I work for usually freak if they get an Open With dialog, (If it doesn't just open by double clicking on it, the world is over)

Here is where it is at so far, needs some work as far as sorting a bit I think, but I have the extensions filtered a bit, feel free to add to the list If you would like to see more inclusions. I will see what I can whip for an Open With dialog, got school tommorrow so it may be a bit for I can get back to it, whoever wants to add to the file, by all means do so, Thanks for the feedback so far..

Sheeeng, it now includes the Double click event.

Later,
Oh, and I renamed it, got tired of Listbox Loader

sheeeng
06-28-2005, 02:20 AM
Sheeeng, it now includes the Double click event.

Later,
Oh, and I renamed it, got tired of Listbox Loader

Thanks, a lot. :friends:
Great work!! :thumb

mdmackillop
06-28-2005, 05:35 AM
Hi Justin,
Don't know if it's needed, but you can also use FollowHyperlink as a method for opening files.

Justinlabenne
06-29-2005, 02:12 AM
Updated: seems messy to me, but think I got most options covered.

See what you think.

sheeeng
06-29-2005, 07:04 PM
Updated: seems messy to me, but think I got most options covered.

See what you think.

Why is there so many changes in ur last file?
Can you explain the functionality of it?

Thx. :friends:

sheeeng
07-03-2005, 08:27 AM
Since this idea is something I could actually find useful myself, give me some time and I will finish it up.

Hi Justin and all,
Have you manege to come up with the codes on this...?
Thx. :friends:

Justinlabenne
07-03-2005, 12:00 PM
This is the add-in version, looks a lot different than the ones from the previous posts.

File Finder (http://jlxl.net/Excel/downloads.html)

mdmackillop
07-03-2005, 04:28 PM
Hi Justin,
A couple of minor points.
If I click off the "Stay Open" option, I still cannot close the form.
Would it be possible for the option button/File type selection to apply the filter to the list of files, if the folder has already been selected?

Justinlabenne
07-03-2005, 04:56 PM
Thanks for the feedback, I have been working on a reliable way to filter the list after the folder has already been selected, but just havent had the time to devote to getting it perfected.

I have some other things I want to add into the add-in also, like the ability to kill a specific file from the list (maybe, but with lots of warning prompts) and some reworking of selecting the folder (it does get awful annoying to keep having to select one) so, I will be updating it hopefully soon,

In regards to closing the form, are you talking about clicking on the red [x]? I used a template form and left a query_close procedure in as normally I prompt users to close specifically by using the close button. Probably not needed here, my bad...:doh:

Again, thanks for the feedback, hope to make it better soon..

sheeeng
07-03-2005, 06:39 PM
This is the add-in version, looks a lot different than the ones from the previous posts.

File Finder (http://jlxl.net/Excel/downloads.html)

Sorry...May I see the code as to learn from it?
Thx....:friends: