Browse Button Code to obtain single file name

Hi All,

i've looked on the threads and there are some exaples of this but not specfic enough.

i need code that allows me to browse my PC for Excel Files then select one file and put it's name into the activecell?

any help is greatly appreciated.

cheers for any help!!

Good morning Macdave_19

Originally Posted by Macdave_19

i need code that allows me to browse my PC for Excel Files then select one file and put it's name into the activecell?

How about something like this? It gives the full pathname - is that acceptable?

ActiveCell.Value = Application.GetOpenFilename("Microsoft Office Excel Files (*.xls), *.xls")

HTH

DominicB

Try adding this function and this macro into a standard module, then run the macro:

Sub GetFileName()
Dim FileName As String

    FileName = Application.GetOpenFilename("Microsoft Office Excel Files (*.xls),*.xls")
    ActiveCell.value = FileOrFolderName(FileName, 1)

End Sub


Function FileOrFolderName(InputString As String, _
    ReturnFileName As Boolean) As String
' returns the foldername without the last pathseparator or the filename
Dim i As Integer, FolderName As String, FileName As String
    i = 0
    While InStr(i + 1, InputString, Application.PathSeparator) > 0
        i = InStr(i + 1, InputString, Application.PathSeparator)
    Wend
    If i = 0 Then
        FolderName = CurDir
    Else
        FolderName = Left(InputString, i - 1)
    End If
    FileName = Right(InputString, Len(InputString) - i)
    If ReturnFileName Then
        FileOrFolderName = FileName
    Else
        FileOrFolderName = FolderName
    End If
End Function

(Function source)

Thanks to both of you guys, i knew it would be something fairly simple, i should really be able to do this by now!!

cheers

Hi JBeaucaire

Think your code is a little larger than it needs to be. Something as simple as this would work to extract the filename only :

filename = Application.GetOpenFilename("Microsoft Office Excel Files (*.xls), *.xls")
ActiveCell.Value = Mid(filename, InStrRev(filename, "\") + 1)

DominicB

Heh... that's a actually a new function for me, never used that before. InStrRev...sweet...text string search from the END of the string.

Thanks for that!