Browse & Select a File, Folder using VBA

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
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?
 

Attachments

  • BrowseTo.zip
    997.1 KB · Views: 4,598
Last edited:

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

Super Moderator
Staff member
Joined
Mar 16, 2011
Messages
112
Reaction score
0
Points
0
Location
Oregon, United States
Excel Version(s)
365, Online, iOS, iOS for iPad
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..
Code:
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:
Code:
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

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
Thanks Zack, I'll incorporate your feedback. Yeah, a single Userform is probably the only way to go. I gen something up.
 
Top