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

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)

"Justin" wrote in message
om...
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

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



"Justin" wrote in message
om...
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

"Justin" wrote in message
om...
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-

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!