How to prompt users to select file path

[krprime90]

Right now I have a vba macro in excel that directly links cells in a workbook to bookmarks in a word document. The document containing the bookmarks is hard coded into the macro. Ultimately, multiple users will have access to this macro enabled workbook and download it off a shared drive which creates a problem with everyone having different folder structures. I'm looking for a way to open a dialog box to prompt users to select the file path of the word document containing the bookmarks rather than have it hard coded.

Sub BDOPS()
Dim objWord As Object
Dim ws As Worksheet


Set ws = Workbooks("Pricing Macro Enabled").Sheets("Details")
Set objWord = CreateObject("Word.Application")


objWord.Visible = True
objWord.Documents.Open "C:\Users\Desktop\BDOPS Handoff.docx"




With objWord.ActiveDocument
.Bookmarks("E2").Range.Text = ws.Range("E2").Text
.Bookmarks("E5").Range.Text = ws.Range("E5").Text
End Sub

As you can see the code points to a specific workbook and specific sheet as well as a specific document then begins filling the specified bookmarks. I'm hoping to find a way for the users to not have to alter the code manually but rather be able to select and use a file path via a dialog box the opens when you run the macro.

Thanks in advance for the help!

 

[wideboydixon]

Sub BDOPS()

Dim objWord As Object
Dim ws As Worksheet
Dim handoff As String

Set ws = Workbooks("Pricing Macro Enabled").Sheets("Details")
Set objWord = CreateObject("Word.Application")

objWord.Visible = True
handoff = vbNullString
With objWord.Dialogs(80)
    If .Display Then
        If .Name <> "" Then handoff = .Name
    End If
End With

If handoff <> vbNullString Then
    objWord.Documents.Open handoff
    With objWord.ActiveDocument
        .Bookmarks("E2").Range.Text = ws.Range("E2").Text
        .Bookmarks("E5").Range.Text = ws.Range("E5").Text
    End With
End If

End Sub

WBD

 

[Norie]

You could use Application.GetOpenFilename to allow the user to navigate to the relevant document.

 

[Zack Barresse]

If you're looking to get the folder path, assuming the file name is known, then you have a few options.

Option #1
Select a folder. Also allows you to make a folder.

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Originally written by Ken Puls at: http://www.vbaexpress.com/kb/getarticle.php?kb_id=284
'Function purpose:  To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE:  If invalid, it will open at the Desktop level
    Dim ShellApp As Object
    'Create a file browser window at the default folder
    Set ShellApp = CreateObject("Shell.Application"). _
                   BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
    'Set the folder to that selected.  (On error in case cancelled)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0
    'Destroy the Shell Application
    Set ShellApp = Nothing
    'Check for invalid or non-entries and send to the Invalid error
    'handler if found
    'Valid selections can begin L: (where L is a letter) or
    '\\ (as in \\servername\sharename.  All others are invalid
    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = "\"
        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
    Case Else
        GoTo Invalid
    End Select
    Exit Function
Invalid:
    'If it was determined that the selection was invalid, set to False
    BrowseForFolder = False
End Function

Option #2
Select a folder. Does not have the make a new folder button, but you can use it as a traditional explorer window utilizing the FileDialog [native] object.

Function GetFolder(Optional ByVal InitialPath As String) As String

    Dim ChosenFolder As FileDialog
    Dim ItemName As String
    Dim CurrentFolder As String

    If InitialPath <> "" Then
        InitialPath = Replace(InitialPath, "/", "\")
        If Right(InitialPath, 1) <> "\" Then InitialPath = InitialPath & "\"
    End If

    CurrentFolder = CurDir

    Set ChosenFolder = Application.FileDialog(msoFileDialogFolderPicker)
    With ChosenFolder
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = InitialPath

        If .Show <> -1 Then
            ItemName = InitialPath
        Else
            ItemName = .SelectedItems(1)
        End If
    End With

    ChDrive CurrentFolder
    ChDir CurrentFolder

    If Right$(ItemName, 1) <> "\" Then ItemName = ItemName & "\"
    GetFolder = ItemName
    Set ChosenFolder = Nothing

End Function

Option #3
Select a folder. Like option #1, traditional Windows folder picker (not an explorer window), but requires an external reference (Shell32), so Windows only.

Private Const BIF_RETURNONLYFSDIRS As Long = &H1

Sub BrowseAndOpenFolder()

    Dim SH As Object
    Dim F As Object
    Dim Caption As String
    Dim FolderPath As String
    Dim InitialFolder As String
    
    InitialFolder = ""
    Caption = "Please select a folder:"
    Set SH = CreateObject("Shell.Application")
    Set F = SH.BrowseForFolder(0&, Caption, BIF_RETURNONLYFSDIRS, InitialFolder)
    If Not F Is Nothing Then
        FolderPath = F.Items.Item.Path
        MsgBox (FolderPath)
        F.Items.Item.InvokeVerb
    End If
    Set F = Nothing
    
End Sub

It should be noted that most people seem to be akin to option #2 above, as it looks most familiar. As a developer, I tend to go with option #1, because it's portable, limits user ability, and is flexible enough to create a folder and start at a specific location. IMHO.

 

[krprime90]

Worked perfectly. Thanks so much!