Populate Listbox with File Name Only

J

Justin

Hi,
I have various ways to populate a list box with .xls files, yet when I
do this I get the FileName to appear as well as the entire Directory
attached to that file. How should I go about Populating the list Box
with Only the File Name?
Right now in listbox1 I have

"C:\Program Files\File1.xls"
"C:\Program Files\File2.xls"
"C:\Program Files\File3.xls"

what I really want is

"File1.xls"
"File2.xls"
"File3.xls"

Thanks in advance!

Justin
 
B

Bob Phillips

Where does the data come from?

--

HTH

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

Chip Pearson

Justin,

Try something like the following:

Dim FullName As String
Dim FName As String
Dim Pos As Integer
FullName = "C:\Temp\Book.xls"
Pos = InStrRev(FullName, "\")
FName = Mid(FullName, Pos + 1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
3

38N90W

Justin said:
Hi,
I have various ways to populate a list box with .xls files, yet when I
do this I get the FileName to appear as well as the entire Directory
attached to that file. How should I go about Populating the list Box
with Only the File Name?
Right now in listbox1 I have

"C:\Program Files\File1.xls"
"C:\Program Files\File2.xls"
"C:\Program Files\File3.xls"

what I really want is

"File1.xls"
"File2.xls"
"File3.xls"

Thanks in advance!

Justin

Not tested.

Sub TestMe()

Dim wb as Workbook

For Each wb in Workbooks
MyForm.ListBox1.AddItem wb.Name
Next wb

End Sub

-gk-
 
J

Justin Hebert

Thanks for the replies. The Subs Worked great, yet I couldn't feed them
into my code. Thanks for the help



Sub SummarySheetSearchListBox()

' Dimension variables.
Dim myarray()
Dim FS As Object
Dim i As Integer
Dim NextFile As String

' Declare filesearch object.
Set FS = Application.FileSearch

' Set folder to search.
FS.LookIn = "C:\Summary Sheets\"

' Set file name to search for.
FS.Filename = ("*.xls")


' Execute the file search, and check to see if the file(s) are present.
If FS.Execute > 0 Then

' Redimension the array to the number of files found.
ReDim myarray(FS.FoundFiles.Count)

' Loop through all found file names and fill the array.
For i = 1 To FS.FoundFiles.Count
myarray(i) = FS.FoundFiles(i)
Next i
Else
' Display message if no files were found.
MsgBox "No files were found"
End If

' Loop through the array and fill the list box on the UserForm.
For i = 1 To FS.FoundFiles.Count
SummarySheet.ListBox1.AddItem myarray(i)
Next i

' Display the UserForm.
SummarySheet.Show

End Sub
 
J

Justin Hebert

I forgot to mention, this is the code that outputs
in Listbox1

c:\summary sheet\file1.xls
c:\summary sheet\file2.xls
c:\summary sheet\file3.xls

Thanks in advance
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top