UK089669


I am trying to use a custom form for the first time and would like to select an excel file from the normal "File _ Save As..." window and once selected, save the path and filename into cell A1 of a spreadsheet called "My Toobar.xls".

This will then allow the user to open this "favourite" file very quickly.

Can anyone help me get this Windows "File _ Save As..." browser window embedded into a UserForm.

Many thanks,

Paul




Re: Excel user form to select a favourite file

Derek Smyth


Hi,

You can use the build in SaveAs dialog in Excel. The example below show you how to show the dialog filtered, it only shows files of type *.fil and the file the user selected is stored in variable vSelectedFile.

    'display dialog and open a file
    vSelectedFile = Application.GetOpenFilename("My Type Of File (*.FILE), *.fil")

If you want to allow all file type do this

    vSelectedFile = Application.GetOpenFilename("All Files (*.*), *.*")

You can call this code from behind a button on a user form.

 

You can check if the user presses cancel like this.

If vSelectedFile <> False Then   'user didn't press cancel







Re: Excel user form to select a favourite file

Andy Pope

I'm sure Derek meant the GetSaveAsFilename ;)

application.GetSaveAsFilename

Both these methods just collect a filename and do not actually save or open anything. You would still need to use the .SaveAs method of the workbook.