Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
list box- list all files ina directory
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change directory to list default | Excel Discussion (Misc queries) | |||
Combo box with list of xls files in a directory | Excel Discussion (Misc queries) | |||
List of Files in A Directory | Excel Discussion (Misc queries) | |||
list directory in EXCEL | Excel Discussion (Misc queries) | |||
List of folders in a certain directory | Excel Discussion (Misc queries) |