Using UserForms to access directories

[barbicanboy]

Hello,
I am pretty new to writing macros but have built something that seems pretty robust (My fingers are crossed it continues to work). As I hope it will work over time I would like to give the user the functionality to pick a file from the directory using a UserForm.
I have no idea how to do this and am unable to find anything in Bills VBA and Macros book or on the net.
Please help!
Peter

 

[Andrew Poulsom]

Welcome to the Board.

Have a look at the GetOpenFileName method in VBA Help.

 

[DominicB]

Good afternoon barbicanboy

Use the GetSaveAsFilename or GetOpenFilename (whether you are saving or opening a file). The example below outlines how to use it - without saving anything, the path and filenames are just dumped into the variables A and B.

Sub test()
a = Application.GetSaveAsFilename(fileFilter:="Microsoft Excel Workbooks (*.xls), *.xls")
b = Application.GetOpenFilename(fileFilter:="Microsoft Excel Workbooks (*.xls), *.xls")
MsgBox "Save As : " & a & Chr(13) & "File Open : " & b
End Sub

HTH

DominicB