Show Filename Only in ComboBox Pulldown

D

D.Parker

This is an continuation post from 6/26. JMB showed me the code to add to
utilize FileSearch inconjunction with UserForms. The code works great,
unfortunately the entire "path" (C:\.....\<filename>.txt) shows up in my
combobox pull down. Is there a way to show only the filename and not the
actual path to the file in the combo box?

Kind regards,

D.Parker
 
J

Jim Cone

D,

Use the "Dir" function...
'-----------------------------
Sub FileNameTest()
Dim strPath As String
Dim strName As String
strPath = "C:\Program Files\Intel\Intel Application Accelerator\iATAenu.dll"
strName = Dir(strPath)
MsgBox strName
End Sub
'-----------------------------

Jim Cone
San Francisco, USA



This is an continuation post from 6/26. JMB showed me the code to add to
utilize FileSearch inconjunction with UserForms. The code works great,
unfortunately the entire "path" (C:\.....\<filename>.txt) shows up in my
combobox pull down. Is there a way to show only the filename and not the
actual path to the file in the combo box?

Kind regards,

D.Parker
 
D

D.Parker

Hmmm. Let me add the code that I have and maybe that may be helpful. I'm
not clear or how to incorporate that code you forwarded to show only my
filename and not the entire filepath in the combobox pulldown?

============
Private Sub UserForm_Activate()
Me.ComboBox1.Clear
With Application.FileSearch
.NewSearch
.LookIn = "C:\Temp"
.Filename = "SN1008" & "*.txt"
.SearchSubFolders = False
.Execute
For i = 1 To .FoundFiles.Count Step 1
Me.ComboBox1.AddItem .FoundFiles(i)
Next i
End With
End Sub
===================

Kind regards,

D.Parker
 
J

Jim Cone

D,

Just ran this without a problem.
Note the lines with the asterisks...
'----------------------------------------
Private Sub MoreTests()
Dim i As Long '****
With Application.FileSearch
.NewSearch
.LookIn = "C:\Old Telephone Dialer"
.Filename = "*.hlp"
.SearchSubFolders = False
.Execute
For i = 1 To .FoundFiles.Count Step 1
MsgBox Dir(.FoundFiles(i)) '****
Next i
End With
End Sub
'-----------------------------------------------------


Hmmm. Let me add the code that I have and maybe that may be helpful. I'm
not clear or how to incorporate that code you forwarded to show only my
filename and not the entire filepath in the combobox pulldown?

============
Private Sub UserForm_Activate()
Me.ComboBox1.Clear
With Application.FileSearch
.NewSearch
.LookIn = "C:\Temp"
.Filename = "SN1008" & "*.txt"
.SearchSubFolders = False
.Execute
For i = 1 To .FoundFiles.Count Step 1
Me.ComboBox1.AddItem .FoundFiles(i)
Next i
End With
End Sub
===================
Kind regards,
D.Parker
 
D

Dave Peterson

If you're using xl2k+:

Change:
Me.ComboBox1.AddItem .FoundFiles(i)
to
Me.ComboBox1.AddItem mid(.FoundFiles(i),instrrev(.foundfiles(i),"\")+1)


I think Jim wanted you to try:

Me.ComboBox1.AddItem dir(.FoundFiles(i))
 
D

D.Parker

Gentlemen, I have tried both methods and the combobox has the text file, but
I continually get error 53 (file not found). Prior to this change it was
finding the file and getting the data from the file and dumping into the
appropriate cell. Is there something else I need to change to use either of
these methods? Thank you.

Kind regards,

D.Parker
 
D

D.Parker

I forgot to add my code for clicking the OK button upon the file selection.

Private Sub OKButton_Click()

TextPath = Me.ComboBox1.Value

On Error GoTo ErrorMsg
Open TextPath For Input As #1 'open text file for SN1033

Do While Not EOF(1) 'go while not end of text file
Line Input #1, NCData

If EOF(1) Then
Application.Range("AP9") = NCData
Range("AP9").Select

'On Error GoTo ErrorMsg1004
'recorded macro for text to columns
Selection.TextToColumns _
Destination:=Range("AP9"), _
DataType:=xlDelimited, _
Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1)), _
TrailingMinusNumbers:=True

End If
Loop
Close #1

Unload TextFileSelectSN1033

Exit Sub
ErrorMsg: 'Error handling routines
Close #1
Range("AP9").Select
Selection.ClearContents
MsgBox "Error " & Err & ": " & Error(Err)

End Sub

Private Sub UserForm_Activate()

Dim i As Long

Me.ComboBox1.Clear
With Application.FileSearch
.NewSearch
.LookIn = "C:\Temp"
.Filename = "SN1033" & "*.txt"
.SearchSubFolders = False
.Execute

For i = 1 To .FoundFiles.Count Step 1
'Me.ComboBox1.AddItem Mid(.FoundFiles(i),
InStrRev(.FoundFiles(i), "\") + 1)
Me.ComboBox1.AddItem Dir(.FoundFiles(i))
Next i

End With

End Sub
 
D

Dave Peterson

If you only keep track of the filename, then excel won't know to look in c:\temp
(or any of its subfolders).

In fact, if there are two files (in different subfolders) that share the same
name, how would the user decide which is which?

I'd make that combobox bigger and show the complete path.
 
D

D.Parker

Hmmm. All the files will be in one location and will have varying serial
numbers (SN1008, SN1009, etc.), but one serial number can have multiple files
with different GMT timestamps contained within the filename. Actuallly the
path is much, much longer relative to my application, that's why I would like
to display only the filenames in the combobox versus the full path, it would
be a super long combobox. Is this possible? Do I need to use something else
inconjuction with the Dir function to get rid of error 53, file not found in
OKButton_Click() sub? Thank you.

Kind regards,

D.Parker
 
J

Jim Cone

Another approach would be to use two columns in the combo box.
The first column to display the file name and a second hidden column
to contain the full file path.

Jim Cone
San Francisco, USA


message If you only keep track of the filename, then excel won't know to look in c:\temp
(or any of its subfolders).

In fact, if there are two files (in different subfolders) that share the same
name, how would the user decide which is which?
I'd make that combobox bigger and show the complete path.
 
D

D.Parker

So does this mean it is not possible with the Dir function you sent me
earlier? Thank you.

D.Parker
 
J

Jim Cone

D,

Almost anything is possible, but some of it is not worth the effort.
What you might do as an interim fix is to go back to the code
you had before Dave and I got involved, then right-align the text
in the combo box so the file name will show.

Reviewing the help file for the combobox object could prove
beneficial.

Regards,
Jim Cone
San Francisco, USA



So does this mean it is not possible with the Dir function you sent me
earlier? Thank you.

D.Parker
 
J

Jim Cone

D,

OK, one more idea...

Change the second line of the Private Sub OKButton_Click()
sub so it reads:
TextPath = "C:\Temp\" & Me.ComboBox1.Value

Jim Cone



D,
Almost anything is possible, but some of it is not worth the effort.
What you might do as an interim fix is to go back to the code
you had before Dave and I got involved, then right-align the text
in the combo box so the file name will show.
Reviewing the help file for the combobox object could prove
beneficial.
Regards,
Jim Cone
San Francisco, USA



So does this mean it is not possible with the Dir function you sent me
earlier? Thank you.
D.Parker
 
D

Dave Peterson

Oops. One version of the application.filesearch had .SearchSubFolders = true.

But the one the OP is using doesn't (I think).

I think you have it...

But I'd look out for an empty combobox.

if me.combobox1.value = "" then
msgbox "Please choose a file
exit sub
else
textpath = "C:\temp\" & me.combobox1.value
end if
 
D

D.Parker

Unfortunately, no success. Thank you for all of you support and
assistance!!!!!

Kind regards,

D.Parker
 
Top