View Full Version : How to display the file name only and not the full path (urgent!)

06-04-2006, 08:46 AM
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?

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
' 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", _
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
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
.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
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.
ActiveDocument.ActiveWindow.View = wdPrintView
' Set tabs.
With Selection.ParagraphFormat.TabStops
.Add _
Position:=InchesToPoints(3), _
Alignment:=wdAlignTabLeft, _
.Add _
Position:=InchesToPoints(4), _
Alignment:=wdAlignTabLeft, _
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

.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
.StartOf Unit:=wdParagraph, Extend:=wdMove
.InsertAfter ":"
.Move Unit:=wdParagraph, Count:=1
End If
End With
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
'End If
If MsgBox("Do you want to list another folder?", vbYesNo) = vbYes Then
GoTo Folder
End If
End Sub

this is the result when i run the script:


06-04-2006, 10:41 AM
1. Please use the VBA tags supplied. It makes it much easier to read.

2. When posting, if at all possible, start with the relevant code, and indicate the parts that are a problem. Extraneous code is a drag. If we need to see more, we certainly will ask for it.

3. The question relates to the name of file...yes? So the relevant part of the code is where you get the name of the file....yes? It is:MyName = .FoundFiles(i)
Documents.Open FileName:=.FoundFiles(i)Think this through. To open the file you need the full path and name. FoundFiles(i) has that, and you can open the file. You are giving that full path and name to the variable MyName. Don't do that.

Open the file with FoundFile....THEN get the name of the document. If you looked in Help you would find that the name of the document is ActiveDocument.Name - no path, just the name. So:Documents.Open FileName:=.FoundFiles(i)
MyName = ActiveDocument.Name

4. Your code could definitely be tidied up better.

06-04-2006, 07:04 PM
Documents.Open FileName:=.FoundFiles(i)
MyName = ActiveDocument.Name

when i use that the result page displays nothing...

06-05-2006, 04:28 AM
Do a test of MyName immediately after you have MyName = ActiveDocument.Name. Use either Debug.Print, or if you want a MsgBox.

What do you get?

06-05-2006, 11:17 PM
Documents.Open FileName:=.FoundFiles(i)
MyName = ActiveDocument.Name


06-05-2006, 11:57 PM
Debug.Print look in VBA Help.

Then use a messagebox. Just tell me what you are getting.

06-06-2006, 12:01 AM
Sorry, that was a bit brusque.

Debug.Print will send what ever you are debugging (a variable usually) to the Immediate window. This way you can see the results of things as they happen. It is a debugging tool.

You have to have (obviously) the Immediate window open to see the debug display.