list box- list all files ina directory
 

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

--
Message posted from http://www.ExcelForum.com

list box- list all files ina directory
 

Have a look at GetOpenFilenam

--
Message posted from http://www.ExcelForum.com

list box- list all files ina directory
 

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)

"mudraker " wrote in message
...
Have a look at GetOpenFilename


---
Message posted from http://www.ExcelForum.com/

list box- list all files ina directory
 

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

--
Message posted from http://www.ExcelForum.com

list box- list all files ina directory
 

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)

"suee " wrote in message
...
Hi Bob,
the files inside the directory do have to be displayed inside a list
box......


---
Message posted from http://www.ExcelForum.com/

list box- list all files ina directory
 

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

--
Message posted from http://www.ExcelForum.com

list box- list all files ina directory
 

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)

"suee " wrote in message
...
Thats great!!
The only change i need is when u click on the desired file from the
list....it will open that filein excel.....rather than call amacro as
it does now
Any help here would be great


---
Message posted from http://www.ExcelForum.com/

list box- list all files ina directory
 

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

"suee <" wrote:

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

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

list box- list all files ina directory
 

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)

"Dave Peterson" wrote in message
...
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

"suee <" wrote:

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

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

list box- list all files ina directory
 

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 Phillips wrote:

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)

"Dave Peterson" wrote in message
...
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

"suee <" wrote:

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

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson


--

Dave Peterson