Join File Name & Path variables

[Keeley]

Hello

I am trying to concatenate a chosen folder with a set
directory. The command line specifying the full path
works fine (see below). I added the input box to ask for
the folder name, this also works ok, the WhereName and
WFullName variable are filled with the correct data. It
just doesn't like using my WFullName or WhereName in the
Const SPth command line.

Thank you for your time.

Keeley.


Private Sub UserForm_Activate()
Dim i As Integer
Dim WhereName As String
Dim WFullName As String

WhereName = InputBox("Please enter name of
folder", "Choose Sub Folder")
WFullName = "C:\" & WhereName

Const sPth = WFullName (doesn't work)
'Const sPth = "C:\" & WhereName (doesn't work)
'Const sPth = "C:\test\" (works-but need more flexibility)

With Application.FileSearch
.NewSearch
.LookIn = sPth
.FileName = "*.doc"
.Execute msoSortByFileName
For i = 1 To .FoundFiles.Count
UserForm3.ListBox1.AddItem .FoundFiles(i)
Next
End With
End Sub

 

[Helmut Weber]

Hi Keeley,
hmm...
flexibility excludes constancy,
constancy excludes fexibility.
You can't assign a variable to a constant.
You will have to build the path by concatenating
the constant and a variable.

 

[Keeley]

Hi

Of course!! I am having a Monday-sorry & thanks!!

The other question I have is how do I set my list box to
only display the file name - not the path - see new code
below.

S2_SecChoice.SummList.Clear
SummDocChoice = "H:\Work\Dev\Tender Tests\Summary\" &
TeamSelect & "\"
sPth = SummDocChoice

S2_SecChoice.SummList.Visible = True
With Application.FileSearch
.NewSearch
.LookIn = sPth
.FileName = "*.doc"
.Execute msoSortByFileName
For i = 1 To .FoundFiles.Count
S2_SecChoice.SummList.AddItem .FoundFiles(i)
Next
End With
End Sub

Thanks very much.

 

[Helmut Weber]

Hi Keeley,
like this:
---
Dim sFull As String
Dim sName As String
sFull = "c:\test\dat\Myfiles\unbelievable\test.doc"
sName = Right(sFull, Len(sFull) - InStrRev(sFull, "\"))
MsgBox sName

 

[Keeley]

Works wonderfully - thanks v much.

the full code looks like this - for anyone else looking.

Private Sub PSTeam_Click()
'PSTeam is a radio button on a user form
' build list of filenames
Dim i As Integer
S2_SecChoice.PSList.Clear
'this will clear the last search in this box

PSDocChoice = "H:\Work\Tests\project strategy\" &
TeamSelect & "\"
'set the file path for documents
sPth = PSDocChoice

S2_SecChoice.PSList.Visible = True
'show the files found in the project strategy folder

With Application.FileSearch
.NewSearch
.LookIn = sPth
.FileName = "*.doc"
.Execute msoSortByFileName
For i = 1 To .FoundFiles.Count
ShrtName = Right(.FoundFiles(i), Len(.FoundFiles(i)) -
InStrRev(.FoundFiles(i), "\"))
'display the files found as filename only not full path

S2_SecChoice.PSList.AddItem ShrtName
Next
End With
End Sub