list box- list all files ina directory

S

suee

I wanted to know if anyone knew the code to create a list box, tha
retrieves all the .xls files stored in a folder. Clicking on th
retrieved file would then open the work book
Can anyone help
 
B

Bob Phillips

This is a good answer, but post back if you must have a listbox on the
sheet.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

suee

Hi Bob,
the files inside the directory do have to be displayed inside a lis
box.....
 
B

Bob Phillips

Okay, give this a try

Sub CreateFormsListBox()
Dim FSO As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim sPath As String
Dim oList As ListBox

Application.CommandBars("Forms").Visible = True
ActiveSheet.ListBoxes.Add(18, 12.75, 150, 200).Select
Selection.OnAction = "myPrintMacro"
Set oList = Selection

Set FSO = CreateObject("Scripting.FileSystemObject")

sPath = "C:\MyTest"
Set Folder = FSO.GetFolder(sPath)

Set Files = Folder.Files
For Each file In Files
oList.AddItem file.Name
Next file

Range("A1").Select

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

suee

Thats great!!
The only change i need is when u click on the desired file from th
list....it will open that filein excel.....rather than call amacro a
it does now
Any help here would be grea
 
B

Bob Phillips

Then add that code into the macro that is called. Forms listboxes can only
fir a macro AFAIK.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

I modified Bob's code slightly:

Option Explicit

Sub CreateFormsListBox()
Dim FSO As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim sPath As String
Dim oList As ListBox

On Error Resume Next
ActiveSheet.ListBoxes("MYLISTBOX").Delete
On Error GoTo 0

Set oList = ActiveSheet.ListBoxes.Add(18, 12.75, 150, 200)
oList.OnAction = ThisWorkbook.Name & "!myPrintMacro"
oList.Name = "MYLISTBOX"

Set FSO = CreateObject("Scripting.FileSystemObject")

sPath = "C:\my documents\excel\test\"
Set Folder = FSO.GetFolder(sPath)

Set Files = Folder.Files
For Each file In Files
oList.AddItem file.Name
Next file

End Sub

Sub myPrintMacro()
Dim sPath As String
Dim myLB As ListBox

Set myLB = ActiveSheet.ListBoxes(Application.Caller)

sPath = "C:\my documents\excel\test\"

If myLB.ListIndex < 1 Then
'do nothing
Else
Workbooks.Open Filename:=sPath & myLB.List(myLB.ListIndex)
End If

End Sub
 
B

Bob Phillips

Sue (and Dave),

The line

oList.AddItem file.Name

would be better served as

oList.AddItem file.Path

IMO as it is not a good idea to have the folder coded into both bits of
code. This will then display the whole path in the list box as well as the
file name (this might mean widening the box).

And it would probably be better not to call it myPrintMacro for a macro that
opens files <vbg> - old code I am afraid.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

Or maybe a public constant:

right after "Option Explict"
Public Const sPath As String = "C:\my documents\excel\test\"

Then dump the Dim's from each procedure (as well as the assignments).

(I agree with you. I didn't like it when I typed it, but I was a weasel and too
lazy to fix it.)

And now you made me fix it! Doh!

Bob said:
Sue (and Dave),

The line

oList.AddItem file.Name

would be better served as

oList.AddItem file.Path

IMO as it is not a good idea to have the folder coded into both bits of
code. This will then display the whole path in the list box as well as the
file name (this might mean widening the box).

And it would probably be better not to call it myPrintMacro for a macro that
opens files <vbg> - old code I am afraid.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Top