Results 1 to 3 of 3

Thread: VBA Allow User to PickFolder

  1. #1
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Excel Version
    Office 365

    VBA Allow User to PickFolder

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Google searching says that the PickFolder function can't display a user defined message. I'm asking the user to select three folders. The inbox, the project archive and a duplicate (or bin) folder. As per the code below I ask using a message box to display the requested folder, then the PickFolder function. I don't want to do a user defined form and add a treeview control because the project folder is HUGE and I don't want it to add the whole structure.

    Any simple ideas. I thought of Inputbox and getting the user to select the folder in the normal navigation pane but there is no Type variable in Outlook's InputBox function

    The recursive function RDuplicateMails then moves each item from the Inbox to either the project folder or if it's there already to the duplicates folder.

    'inspired by
    Public Sub DeleteDuplicateMails()
    Dim oInFolder As Folder, oProjFolder As Folder, oDupfolder As Folder    'the folders
    Dim oProjMailItems                                        'the sorted collections
        'ask the user for the three folders
        If vbNo = MsgBox("Please select the folder with NEW e-mails", vbInformation + vbYesNo, "Select Inbox") Then Exit Sub 'allow the user to cancel
        Set oInFolder = Outlook.GetNamespace("MAPI").PickFolder         'where is the new information
        If vbNo = MsgBox("Please select the destination PROJECT folder", _
            vbInformation + vbYesNo, "Select Project Archive") Then Exit Sub 'allow the user to cancel
        Set oProjFolder = Outlook.GetNamespace("MAPI").PickFolder       'where is the Project archive
        If vbNo = MsgBox("Please select the backup DUPLICATE folder", _
            vbInformation + vbYesNo, "Select Duplicate Archive") Then Exit Sub 'allow the user to cancel
        Set oDupfolder = Outlook.GetNamespace("MAPI").PickFolder        'where do the duplications go to
        If vbNo = MsgBox("Inbox: " & oInFolder.Name & vbCrLf & _
                        "Project : " & oProjFolder.Name & vbCrLf & _
                        "Duplicates : " & oDupfolder.Name, vbInformation + vbYesNo, "Final Confirmation") Then Exit Sub 'allow the user to cancel
        'set the prj items
        Set oProjMailItems = oProjFolder.Items
        oProjMailItems.Sort "[ReceivedTime]", olAscending
        Call RDuplicateMails(oInFolder, oProjFolder, oDupfolder, "", oProjMailItems)  'Call recursive
        'clean up any references
        Set oProjMailItems = Nothing
        Set oInFolder = Nothing
        Set oProjFolder = Nothing
        Set oDupfolder = Nothing
        Call MsgBox("done")
    End Sub

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    I'm not totally following the rationale on not going with a custom form. Why not create a simple form like this:
    Click image for larger version. 

Name:	10-3-2014 8-26-09 AM.png 
Views:	92 
Size:	7.3 KB 
ID:	2706

    You could let the user type in the name of the folder, or select it using the Browse button. I'm thinking that you could leverage the PickFolder method to retrieve the path, and you probably wouldn't even need to go to a Treeview. (To be fair, I haven't worked with PickFolder... I assume it returns a folder path?)

    Last, I'd just add a little validation in the "Do it!" button to make sure that all the paths are valid before you run the rest of the routine.

    I'd think that would still be pretty lightweight overall...

    If you want it, I've also uploaded the components that you could import the form as pictured here. No code yet, but would give you a design to start.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Excel Version
    Office 365
    Hi Ken,
    Yes PickFolder brings a standard dialogue box up and allows the user to browse thru the list to select a folder (or return the list of child folders with .Folders). It seems to read the standard Outlook navigation bar so if the public folder is already open it reads significantly quicker then doing so via code.

    Unfortunately I have no control of the public folder structure, the path is /Public Folders - username/All Public Folders/Country/State/Projects and Clients/Client/Project folder. Unfortunately it's a large global company and at a minimum they should be splitting the folder into country. Asking a user to type a 7 level deep project folder is not going to work. Returning all the folders just to select one is hitting the problem with a very large hammer. Works but not elegant or fast.

    If I go the treeview path I would only display one level down and if the user selected a subfolder then add that branch. Alternatively if there was a way to escape the program and allow the user to select the folder (with currentfolder) such as input box does in Excel.

    Thanks anyway, will put this one on hover for now

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts