How to display the file name only and not the full path (urgent!)
here is my code, what it does not is that it displays everything correctly but what i wan is just to display the file name and not the full path to it...
how do i make the file name only display the file name?
[VBA]
Sub CommandButton1_Click()
' Example Macro to list the files contained in a folder.
'
Dim x As String, MyName As String
Dim i As Integer
Dim Response As Integer, TotalFiles As Integer
Dim counter As Integer
Dim test As String
On Error Resume Next
Folder:
' Prompt the user for the folder to list.
x = InputBox(Prompt:="What folder do you want to list?" & vbCr & vbCr _
& "For example: C:\My Documents", _
Default:=Options.DefaultFilePath(wdDocumentsPath))
If x = "" Or x = " " Then
If MsgBox("Either you did not type a folder name correctly" _
& vbCr & "or you clicked Cancel. Do you want to quit?" _
& vbCr & vbCr & _
"If you want to type a folder name, click No." & vbCr & _
"If you want to quit, click Yes.", vbYesNo) = vbYes Then
Exit Sub
Else
GoTo Folder
End If
End If
' Test if folder exists.
If Dir(x, vbDirectory) = "" Then
MsgBox "The folder does not exist. Please try again."
GoTo Folder
End If
' Search the specified folder for files
' and type the listing in the document.
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeOfficeFiles
' Change the .FileType to the type of files you are looking for;
' for example, the following line finds all files:
' .FileType = msoFileTypeAllFiles
.LookIn = x
.Execute
TotalFiles = .FoundFiles.Count
If TotalFiles = 0 Then
MsgBox ("There are no files in the folder!" & _
"Please type another folder to list.")
GoTo Folder
End If
' Create a new document for the file listing.
Application.Documents.Add
ActiveDocument.ActiveWindow.View = wdPrintView
' Set tabs.
With Selection.ParagraphFormat.TabStops
.Add _
Position:=InchesToPoints(3), _
Alignment:=wdAlignTabLeft, _
Leader:=wdTabLeaderSpaces
.Add _
Position:=InchesToPoints(4), _
Alignment:=wdAlignTabLeft, _
Leader:=wdTabLeaderSpaces
End With
' Type the file list headings.
Selection.TypeText "File Listing of the "
With Selection.Font
.AllCaps = True
.Bold = True
End With
Selection.TypeText x
With Selection.Font
.AllCaps = False
.Bold = False
End With
Selection.TypeText " folder!" & vbLf
Selection.Font.Underline = wdUnderlineSingle
Selection.TypeText vbLf & "File Name" & vbTab & "File Size" _
& vbTab & "File Date/Time" & vbTab & "Total Flow" & vbLf & vbLf
Selection.Font.Underline = wdUnderlineNone
' Type results into table
'stop the screen flickering
Application.ScreenUpdating = False
For i = 1 To TotalFiles
MyName = .FoundFiles(i)
Documents.Open FileName:=.FoundFiles(i)
With Selection.Find
.ClearFormatting
.Text = "Test Objective:"
.Format = True
.MatchCase = True
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Forward = True
Do While .Execute(Forward:=True, Format:=True) = True
With .Parent
counter = counter + 1
If .End = ActiveDocument.Content.End Then
.StartOf Unit:=wdParagraph, Extend:=wdMove
.InsertAfter ":"
Exit Do
Else
.StartOf Unit:=wdParagraph, Extend:=wdMove
.InsertAfter ":"
.Move Unit:=wdParagraph, Count:=1
End If
End With
Loop
End With
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
Selection.TypeText MyName & vbTab & FileLen(MyName) _
& vbTab & FileDateTime(MyName) & vbTab & counter & vbLf
counter = 0
Next i
' Type the total number of files found.
Selection.TypeText vbLf & "Total files in folder = " & TotalFiles & _
" files."
'turn updating back on
Application.ScreenUpdating = True
End With
'If MsgBox("Do you want to print this folder list?", vbYesNo) = vbYes Then
'Application.ActiveDocument.PrintOut
'End If
If MsgBox("Do you want to list another folder?", vbYesNo) = vbYes Then
GoTo Folder
End If
End Sub
[/VBA]
this is the result when i run the script:
http://i6.photobucket.com/albums/y22...23/results.gif
