Browse up to file

[kaiser]

I am writing an excel macro that requires a user to input the path of a
file. I have code to ensure that the file actually exits but would
prefer to have a buttont that pops up a windows that allows the user to
BROWSE up to the file and when he follows the path and eventually
double clickson the file it stores that path instead of him having to
type it into a cell/input box somewhere.

Can anyone help with this?

 

[Bill James]

What version of Office are you using? For Office 2003 (from help file!):

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False ' only allow one file selection (True-
allow multiple file selections)
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

For prior versions I have resorted to using:

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Do a search for OPENFILENAME to get the structure of the object. I can
provide you a sample via email if you can't find it.