select path of file

[fraser5002]

Hi im using VBA with excel i was wanting to know how to get a dialogue box to pop up allowing ther user to locate a file to open. VBA then saving the file path. how would i do this? i cant see any controls that do it?

[Bob Phillips]

Take a look at FileDialog in VBA help, there is a good example there.

[CreganTur]

The code below will open a file dialog window that allows the User to choose a file. The Filepath is saved tot he strFilepath variable.

You will have to have a reference set (In the VBE click Tools -> References) to Microsoft Office xx.0 Object Library. YOu'll either have 11.0 or 12.0.

Dim strFilepath As String
Dim dlgOpen As Office.FileDialog
Dim vrtSelectedItem As Variant

'select workbooks using file dialog
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen '<<<Opens file dialog window
.AllowMultiSelect = False '<<<User can select multiple files | False for single file
.Title = "Please select file to load" '<<<Title text for window
.Filters.Clear '<<<Removes any old File Dialog filters
.Filters.Add "Excel Files", "*.XLS" '<<<sets filter to Excel files

If .Show = 0 Then '<<< if User presses Cancel then Sub ends
Exit Sub
Else
'Will loop through all files selected by the Dialog window
For Each vrtSelectedItem In .SelectedItems
strFilepath = vrtSelectedItem '<<<Set filepath to Variable
Next
End If
End With

HTH:thumb

[sassora]

How would I go about doing something similar but for folders?

The idea is that we can double-click on cell B5 say and this will bring up an "Open" style dialog box. From here you select a folder as opposed to a file and then click OK and the file path chosen gets put in cell C5.

Is this possible and how?

[Bob Phillips]

Sane approach, FileDialog has an option to specify the dialogtype. Look it up in VBA help.

[RonMcK]

You might look at Solved: Folder Path where a similar question was discussed. (I found this using the Search feature in the tool bar.)

Cheers,

[sassora]

Thanks for the info, I can see it working when I make a few changes.