Macro to choose file and add name to a cell

[tonywatsonhelp]

Hi Everyone,

I would like a macro that when run brings up the folder window like the save as window and asks me to select which AMF file i want to use?
I can then select a file from my computer and the name i selected get input into cell G10

Please help if you can

Thanks

Tony

 

[Jon von der Heyden]

Hi Tony

I don't know what an AMF file is but I am assuming it has amf extension (see filter in code below). The cod will display selected file name in message box but you can write it to a cell instead.

Sub GetFile()
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        Call .Filters.Clear
        Call .Filters.Add("AMF Files", "*.amf")
        If .Show <> 0 Then
            MsgBox .SelectedItems(1)
        End If
    End With
End Sub

 

[tonywatsonhelp]

Hi Jon von der Heydon,
thank you for your help, sorry AMF is the type of template I used so the files are xlsx
but I got your code working ok and thanks for your help.
the problem is it gave me the whole filepath "C:\Users\tony\OneDrive\Desktop\Test1\AMF Justice Report - Jun 18 to May 19.xlsx" I need the cell to just contain the name "

AMF Justice Report - Jun 18 to May 19"
how can I get it to do this?
​

 

[Jon von der Heyden]

Good job Tony. To get filename only use:

Dir$(.SelectedItems(1))

 

[tonywatsonhelp]

Brillliant thank you