VBA - User prompt to determine path to files

**michaelisk** · 03-08-2013, 04:35 PM

Sub ProcessListUpdate()

Const strFolderPath As String = "C:\exceltest\"

How to create VBA code to create popup window/dialog box to determine path to location of desired .xls files to be edited? Instead of users updating VBA code

Good resource (book,website) beside excel help forum to learn basic VBA programming? Feeling guilty for taking the easy way out on this..

**jason_lee_91** · 03-08-2013, 04:43 PM

Try this

Sub GetFilePath()
Dim fn As String
fn = Application.GetOpenFilename
MsgBox (fn)
End Sub

**tigeravatar** · 03-08-2013, 04:45 PM

michaelisk,

Here is some commented code for how to accomplish this. Note that this is not the only way to accomplish this.

Sub tgr()
    
    Dim oShell As Object
    Dim strFolderPath As String
    
    Set oShell = CreateObject("Shell.Application")
    
    On Error Resume Next    'Prevent error if user presses cancel
    'Prompt user to select a folder
    strFolderPath = oShell.BrowseForFolder(0, "Select Folder", 0).Self.Path & Application.PathSeparator
    Set oShell = Nothing    'Release the memory used for the object variable
    On Error GoTo 0         'Remove the "On Error Resume Next" condition
    
    If Len(strFolderPath) = 0 Then Exit Sub 'User pressed cancel, exit macro
    
    MsgBox strFolderPath
    
End Sub

As for book/website for learning, this thread has several useful links: http://www.excelforum.com/excel-prog...materials.html

**alansidman** · 03-08-2013, 04:47 PM

I think that each of us is reading the issue differently.

Dim strFolderPath As String

Set strFolderPath = Application.InputBox(prompt:="Enter Path", Type:=2)

**tigeravatar** · 03-08-2013, 04:49 PM

Originally Posted by alansidman

I think that each of us is reading the issue differently.

Quoted For Truth

**jason_lee_91** · 03-08-2013, 04:49 PM

I dont mean to hijack someone else's thread but thank you tigeravatar.
I have been looking for something like that for a long time.

**michaelisk** · 03-08-2013, 04:55 PM

Tiger that works great. Just would like to be able to keep the memory of last folder selected for ease of browsing.

Set oShell = Nothing to ??

**tigeravatar** · 03-08-2013, 05:03 PM

michaelisk, I'm not sure what you mean. The chosen folder is stored in the string variable strFolderPath, there is no reason to keep memory reserved for the oShell object variable.

@jason_lee_91, you're very welcome