Browse & Select a File, Folder using VBA

[GaryA]

Attached is a macro enabled workbork that will allow you to Browse and Select a File or Folder Path into the worksheet automatically, instead of manually typing the full path. The Macro will also copy the same path to multiple locations in the spreadsheet based on common keywords. IT uses the file dialog to browse to a UDL (must enter the full http path).

This code, as it is, works as designed. However, instead of being presented with multiple dialogs (i.e A file dialog box or a Folder dialog box), I would like to merge the 2 into one. I would also like to be able to incorprate Browse to a URL more efficiently.

Any ideas?

[Ken Puls]

Hi Gary,

I'll try and take a look at this over the weekend. I don't anticipate the file/folder part being too difficult. The other I'm not sure about, but I'll give it a go.

[Zack Barresse]

Hello,

A couple of quick comments regarding the code. I'm not sure you need to do the selecting you do with the code. Also, I wouldn't bank on the ActiveCell, like you do with this line..

ActiveCell.Value = ConvertDrive2ServerName(Fname)

But rather set to a variable, then set the cell value by that variable. You go on further to look at the ActiveCell Value, which you should be checking the variable you would've just set. Much less problemmatic.

This line confuses me:

ActiveSheet.Range("Procedure_Artifact_Group").AutoFilter Field:=6, Criteria1:=ArtifactValue

Your "Procedure_Artifact_Group" is in a single column, from rows 2 to 109, but you set your Field as 6, when it should be a 1 (think columns).

As per your original request, I would recommend perhaps incorporating this into a UserForm, where both controls would be visible to the user. Unfortunately it is going to be one or the other (file or folder), so a control with both available might be the way to go.

HTH

[GaryA]

Thanks Zack, I'll incorporate your feedback. Yeah, a single Userform is probably the only way to go. I gen something up.